Query sql to VBA error help with syntax using the AS clause

[Guest]

The following query works fine using the query grid but when I copy and paste
it into the code editor, it gives a syntax error.

The idea is that if the field CulvSize is null, then the first comma and the
phrase ‘mm dia’ do not show.

The table is called tblRecords, has fields Item and CulvSize
Both are text fields.
The query window in sql view shows
SELECT [Item] & (", "+[CulvSize]+" mm dia") AS Descr
FROM tblRecords;

I paste it into the code editor as shown below
strSQL = "SELECT [Item] & (", "+[CulvSize]+" mm dia") AS Descr " _
& "FROM tblRecords;"
But it just gives syntax error. I have tried several variations, but nothing
works. I can't see why not.

 

[Duane Hookom]

Try this
strSQL = "SELECT [Item] & (', ' & [CulvSize] & ' mm dia') AS Descr " _
& "FROM tblRecords;"

 

[Guest]

Hi Nugimac,

Try this:

Sub test()
Dim strSQL As String

strSQL = "SELECT [Item] & ("", ""+[CulvSize]+"" mm dia"") AS Descr " _
& "FROM tblRecords;"

Debug.Print strSQL

End Sub


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

thanks for such quick responses. I will go with Tom's answer as it works the
way I want. Thanks again.