Help - SQL query records truncating from Actinic Catalog.

[skinnybloke]

This may be a bit off topic.

I am writing a SQL query to create a data feed for input to an affilliate database from the Actinic catalag database.

I have a problem in that the full description field truncates to about 255 characters in the query itself.

Is there anything that I can change to make this limitless - obviously without comprimising the Actinic database.

TIA

[Jan]

Are you writing the query in excel? It is not normal for a query to truncate a memo field like this so it has to be something to do with how you are running the query.

If you post the SQL here, we could take a look at it?

Regards,

[skinnybloke]

Hi Jan - the query is in access. I then export it to excel and save it as a .csv file.

Do you still want me to post the code?

[Jan]

If you run the query in Access do you still get the truncation?

Regards,

[skinnybloke]

Yes - the truncation happens in the Access query.

[Jan]

Could you post the query SQL then please.

Regards,

[skinnybloke]

Hi Jan - this is the query.

SELECT DISTINCT Product.[Short description] AS [Product Name], Null AS [Product Category], ([sString1]) AS [Manufacture/brand], Null AS [Promotional Text], Product.[Full description], ("http://www.wildlifeonline.com/acatalog/"+([sPageName])) AS [URL of page to link to], ("http://www.wildlifeonline.com/acatalog/"+sGetLastF([sSectionImageFile])) AS [URL of image], Format([Product].[Price]*0.01175,"#.00") AS Price
FROM ([Catalog section] INNER JOIN Product ON [Catalog section].nSectionID = Product.nParentSectionID) INNER JOIN ProductProperties ON Product.[Product reference] = ProductProperties.sProductRef
WHERE (((Product.Status)="n") AND ((ProductProperties.nValue1)=1) AND (([Catalog section].bHideOnWebSite)=0))
ORDER BY ProductProperties.sString1;

...and this is the functions that go with it.

Public Function sGetLastF(vText As Variant) As String

Dim vBuf As Variant

If IsNull(vText) = False Then
vBuf = Split(vText, "\")
sGetLastF = vBuf(UBound(vBuf, 1))
End If

End Function

[Jan]

The DISTINCT clause causes this to happen - don't know why though, it must be an access thing.

Regards,

[skinnybloke]

thats a bugger !!!

Thanks for taking a look Jan.

[skinnybloke]

Hi - for anyone who may be interested and for the record...

I have delved into this and found out the reason...which is:

"Using DISTINCT or GROUP BY on a memo field causes Access to returns only the first 255 characters of a memo, and you cannot change that behavior. If it did not operate that way, it would potentially have to read tens of thousands of characters from every record in order to determine whether the record was distinct or not."

I have solved my original problem by splitting the query into 2. The first query does the DISTINCT bit and the second query pulls in the description field.

Query 1 which is named AWQsetup1

SELECT DISTINCT Product.[Short description] AS [Product Name], Product.[Product Reference], Null AS [Product Category], ([sString1]) AS [Manufacture/brand], Null AS [Promotional Text], Null AS [Product Description], ("http://www.wildlifeonline.com/acatalog/"+([sPageName])) AS [URL of page to link to], ("http://www.wildlifeonline.com/acatalog/"+sGetLastF([sSectionImageFile])) AS [URL of image], Format([Product].[Price]*0.01175,"#.00") AS Price
FROM ([Catalog section] INNER JOIN Product ON [Catalog section].nSectionID = Product.nParentSectionID) INNER JOIN ProductProperties ON Product.[Product reference] = ProductProperties.sProductRef
WHERE (((Product.Status)="n") AND ((ProductProperties.nValue1)=1) AND (([Catalog section].bHideOnWebSite)=0));

and Query 2:

SELECT AWQsetup1.[Product Name], AWQsetup1.[Product Category], AWQsetup1.[Manufacture/brand], AWQsetup1.[Promotional Text], Product.[Full description] AS [Product Description], AWQsetup1.[URL of page to link to], AWQsetup1.[URL of image], AWQsetup1.price
FROM AWQsetup1 INNER JOIN Product ON AWQsetup1.[Product Reference] = Product.[Product reference]
ORDER BY AWQsetup1.[Manufacture/brand];