Querydef Trouble

[dan-cat]

Hello,

I'm trying to build my own querydef based on criteria on a form but I am having a slight problem with the SQL.

The function reads like this:

Function BuildSQLString(strSQL As String) As Boolean

Dim strSelect As String
Dim strFrom As String
Dim strWhere As String

strSelect = "s.* "
strFrom = "AllProducts s "
If Forms!MODSearchForm!SBUChk = -1 Then
strWhere = " AND s.SBU = " & Forms!MODSearchForm!SBUCombo
End If
If Forms!MODSearchForm!SICChk = -1 Then
strWhere = strWhere & " AND s.[SIC Codes] = " & Forms!MODSearchForm!IndustryCombo
End If

strSQL = "SELECT " & strSelect
strSQL = strSQL & "FROM " & strFrom
If strWhere <> " " Then strSQL = strSQL & "WHERE " & Mid$(strWhere, 6)
BuildSQLString = True

End Function

It's called like this:

Dim strSQL As String

If Not BuildSQLString(strSQL) Then
MsgBox " There was a problem building the SQL string"
Exit Sub
End If
MsgBox strSQL
CurrentDb.QueryDefs("qryExample").SQL = strSQL

However within the SQL criteria the contents of the field are displayed as [ABC] instead of "ABC".
Can anyone see what I am doing wrong?

Thankyou

Dan

 

[dcx693]

You have this line:
strFrom = "AllProducts s "

Is "AllProducts s " the name of a table? Or query?

 

[dan-cat]

Hello DCX

That's the name of the table

 

[dcx693]

I could be wrong here, but don't you need to enclose object names within square brackets if they include spaces?

 

[dan-cat]

Hello DCX,

I'm using strFrom ="AllProducts s" to rename the table 'AllProducts' to 's' within the SQL string.

The table itself isn't actually called 'AllProducts s'.

My problem is as follows:

Say if the field SBUCombo details "ABC".

'qryExample' is defined as follows:

SELECT s.*
FROM AllProducts As s
WHERE (((s.SBU)=[ABC]));

When in fact it should read

SELECT s.*
FROM AllProducts As s
WHERE (((s.SBU)="ABC"));

Dan

 

[dcx693]

To start, change:
strWhere = strWhere & " AND s.[SIC Codes] = " & Forms!MODSearchForm!IndustryCombo

to be:

strWhere = strWhere & " AND s.[SIC Codes] = '" & Forms!MODSearchForm!IndustryCombo & "'"

 

[dan-cat]

To start, change:
strWhere = strWhere & " AND s.[SIC Codes] = " & Forms!MODSearchForm!IndustryCombo

to be:

strWhere = strWhere & " AND s.[SIC Codes] = '" & Forms!MODSearchForm!IndustryCombo & "'"

That's the baby.

Thankyou very much DCX