Member Avatar for JohnandDixie

Hi, I only joined yesterday. I have a problem with the following code where I get a missing operator error. Could someone please tell me what I am doing wrong.
Thanks
John

'---------------------------------------------------------------------------
' Function: strbuildSQLstr
' Purpose: Build SQL String to retrieve ADO Recordset
' Paramters: strChoice- string(Good or Bad),strStart- string(start date)
' strEnd - string( End Date)
' Returns: String - SQL Statement build with parameters passed
'---------------------------------------------------------------------------
Function strbuildSQLstr(strChoice As String) As String
Dim strtemp As String

strtemp = "Select * from SizeRange Where Style =" & "'" & txtStyle & "'"
If strChoice > "" Then strtemp = strtemp & "'" & " and WHERE type =" & "'" & strChoice & "'"

' return the SQL String
strbuildSQLstr = strtemp & " " & "ORDER by Date"

  • 3 Contributors
  • 3 Replies
  • 129 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by JohnandDixie

Dani AI

Generated

: the "missing operator" message almost always means the SQL string your code builds is syntactically wrong — extra/misplaced keywords, stray or unmatched quotes, or a reserved name used as a column. is right that you should not repeat WHERE; use AND for additional conditions. is also correct that using a column named Date can cause confusion (it is a built‑in type); either rename the column or escape it.

Best practice is to avoid manual concatenation of values and use parameterized commands. Example using ADO Command (names changed from the original thread):

Dim cmd As New ADODB.Command
Set cmd.ActiveConnection = cn
cmd.CommandText = "SELECT ColA, ColB FROM MyTable WHERE StyleCol = ?"
cmd.Parameters.Append cmd.CreateParameter("pStyle", adVarChar, adParamInput, 50, styleVal)
If Len(Trim(choiceVal)) > 0 Then
  cmd.CommandText = cmd.CommandText & " AND TypeCol = ?"
  cmd.Parameters.Append cmd.CreateParameter("pType", adVarChar, adParamInput, 20, choiceVal)
End If
cmd.CommandText = cmd.CommandText & " ORDER BY [DateCol]"
Set rs = cmd.Execute

If you must build SQL strings, always:

  • Keep spaces when concatenating so tokens do not run together.
  • Escape single quotes inside values: Replace(val, "'", "''").
  • Escape reserved names with square brackets, e.g. [Date], or better, rename the column. See Microsoft guidance on date/time types and reserved words for SQL Server: and Reserved keywords.

Debugging checklist: print the final SQL (Debug.Print or log it) and run it directly in your SQL tool; look for unmatched quotes, duplicate WHERE, missing spaces before AND, or wrong column names. Fixing those will clear the "missing operator" error.

Recommended Answers

All 3 Replies

Member Avatar for debasisdas

please change the order by field name. Date is not a recomended field name in database as it is a predefined data type.

Member Avatar for QVeen72

Hi,

Once already Where is given in the first statement, after that use only "And"

Change the second line to :

If Trim(strChoice) <> "" Then 
    strtemp = strtemp & " and type ='" & strChoice & "' "
End If

Regards
Veena

Member Avatar for JohnandDixie

Thanks for your help. Is it the fact I am using 'Date' as a field name or actually ordering by date that is a problem?

Think it is working now anayway.
John

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.