Newbie Question: Queries in SQL view then SQL to VBA

[Nigee]

Hello,
I would like to get some advise form you experts out there

I have an SQL Statement that looks like this:

SELECT [D3 PROD HIER OUTPUT].[Item Class], [D3 PROD HIER OUTPUT].[Base System Desc], [D3 PROD HIER OUTPUT].[Family ID], [D3 PROD HIER OUTPUT].[Family Desc]
FROM [D3 PROD HIER OUTPUT]
GROUP BY [D3 PROD HIER OUTPUT].[Item Class], [D3 PROD HIER OUTPUT].[Base System Desc], [D3 PROD HIER OUTPUT].[Family ID], [D3 PROD HIER OUTPUT].[Family Desc], [D3 PROD HIER OUTPUT].[Product Line Desc], [D3 PROD HIER OUTPUT].[Brand Desc]
HAVING ((([D3 PROD HIER OUTPUT].[Family Desc]) Like "*" & [What you lookin for?] & "*"))
ORDER BY [D3 PROD HIER OUTPUT].[Item Class];

Basically I am just pulling [Family Desc].
It works fine in a query. But when I convert to VBA which looks like:

Private Sub Command2_Click()

Dim strSQl As String
strSQl = "SELECT [D3 PROD HIER OUTPUT].[Item Class]" & vbCrLf
strSQl = strSQl & " , [D3 PROD HIER OUTPUT].[Base System Desc]" & vbCrLf
strSQl = strSQl & " , [D3 PROD HIER OUTPUT].[Family ID]" & vbCrLf
strSQl = strSQl & " , [D3 PROD HIER OUTPUT].[Family Desc]" & vbCrLf
strSQl = strSQl & " FROM [D3 PROD HIER OUTPUT]" & vbCrLf
strSQl = strSQl & " GROUP BY [D3 PROD HIER OUTPUT].[Item Class]" & vbCrLf
strSQl = strSQl & " , [D3 PROD HIER OUTPUT].[Base System Desc]" & vbCrLf
strSQl = strSQl & " , [D3 PROD HIER OUTPUT].[Family ID]" & vbCrLf
strSQl = strSQl & " , [D3 PROD HIER OUTPUT].[Family Desc]" & vbCrLf
strSQl = strSQl & " , [D3 PROD HIER OUTPUT].[Product Line Desc]" & vbCrLf
strSQl = strSQl & " , [D3 PROD HIER OUTPUT].[Brand Desc]" & vbCrLf
strSQl = strSQl & " HAVING ((([D3 PROD HIER OUTPUT].[Family Desc]) Like ""*"" & [What you lookin for?] & ""*""))" & vbCrLf
strSQl = strSQl & " ORDER BY [D3 PROD HIER OUTPUT].[Item Class]"

DoCmd.RunSQL strSQl

End Sub

I set this up in the OnClick event.
Then i get this error:
Run Time error 2342
A RunSQL action requires a argument consisting of an SQL Statement.

No clue what i'm missing here ....
been troubleshooting for hours....

Any help would be appreciated.

Thanks,
nigel

 

[Travis]

Remove these from the ends of your lines & vbCrLf

Also you can debug sql string

Add this line prior to running of the query:

Debug.Print strSQl


After you run the routine goto the imediate window and copy and paste the result in the SQL view of a query. Run the query.

 

[striker]

sql code formatting

If you wish to format the sql in vba so that it reads easily on the screen use the underscore character _ this is a line continuation character in VBA.

 

[FoFa]

ALSO:

strSQl = strSQl & " HAVING ((([D3 PROD HIER OUTPUT].[Family Desc]) Like ""*"" & [What you lookin for?] & ""*""))"

TO

strSQl = strSQl & " HAVING ((([D3 PROD HIER OUTPUT].[Family Desc]) Like '*" & [What you lookin for?] & "*'))"

 

[saman]

Convert SQL string to VBA code

hi
I need create Union query by VBA code.
I have an SQL Statement.

SELECT id,name,school from tblStudent
UNION select id,name,school
FROM tblTeacher;

but this below code can not convert SQL string to VBA
Dim strSQLStart As String
Dim strSQLFrom As String
Dim strSQLWhere As String
Dim strSQLOrder As String
Dim strSQL as string
strSQLStart = "SELECT id,name,school "
strSQLFrom = " From tblStudentUNI"
strSQLFrom = strSQLFrom & " ON select id,name,school"
strSQLFrom = " From tblTeacher;"
strSQL = strSQLStart & strSQLFrom & strSQLWhere & strSQLOrder

what is mistake in below code?
Any suggestions or help is appreciated.
Thank