HOw DO I - DO A SQL SELECT statement on a string contain ing ","s ??? 1

[DominicG]

I am writing a sql statement in VBA access using recordets, where the table only has one text column.

the select statement used is as follows;

par = "Select * from core1 order by idnumb"
Set rst = db.OpenRecordset(par)

ads1 = Len(rst.Fields(1).Value)

the text in the table in one column in core1 is a 255 length string, however the string contains a ",".

when the "," appears with teh above statement beleives the "," to be the end of the field and does not recognise and characters after the ",".

Is there something I can do so the statement ignors the ","

 

[TonyJollans]

Hi DominicG,

Whatever else is happening, the comma in your data is not causing other data to be ignored. Can you give a bit more detail about what you are selecting and what the columns are (if only one of them is text and you have selected at least two).

If it helps, the Len function will return the actual length of the data in the first record - not the length you have given as the maximum length in the field definition.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[DominicG]

Tony

It may be that the "," is causing the recorset to think it is another feild.

the following;

"DJ020040201GN EXXXX 712 QUEENS GARDENS 13 OLD PEAK ROAD, MID LEVELS HONG KONG " The following text resides in feild 2 of a table core1

when the select * from core1 is used in the recordset

the statement

ads1 = Len(rst.Fields(1).Value)

returns the value ;

"DJ020040201GN EXXXX 712 QUEENS GARDENS 13 OLD PEAK ROAD"

My aim is for the statement to include all of the text in the value. If I remove the "," then it does.

regards

 

[TonyJollans]

Hi DominicG,

Len is a function which returns the Length of a string; I don't understand how you get a string returned from it. I can't reproduce anything like what you suggest; what version of Access are you using?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[DominicG]

Tony

yes agreed Len is does return the length of the field,

my code ;



par = "Select * from core1 order by idnumb"
Set db = OpenDatabase("x:\??_PEN\EXXXX\Ex201.mdb")
Set rst = db.OpenRecordset(par)



If Left(rst.Fields(1).Value, 2) = "DJ" Then
On Error GoTo goose
ads1 = Len(rst.Fields(1).Value)
ads = rst.Fields(1).Value & Space(254 - ads1)
r41 = Mid(rst.Fields(1).Value, 24, 28)
r42 = Mid(rst.Fields(1).Value, 52, 28)
r43 = Mid(rst.Fields(1).Value, 80, 28)
r44 = Mid(ads, 108, 28)
r45 = Mid(ads, 136, 8)
r48 = ads
Else
End If

Like I say it is as if the "," is being interpreted as an end of field marker.

When I view rst.Fields(1).Value it only displays the text up to the comma and thus the Len command returns an incorrect number.

just woundered if i needed to alter my set db or set rst statement ??

I am using Access 97

thanks

 

[TonyJollans]

Hi DominicG,

Just came across some code I'd used for testing and realised I hadn't replied - sorry. I don't, unfortunately, have anything useful to tell you; I'm afraid I can't reproduce this at all, nor can I explain it - sorry again.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.