asked on

SQL and VBA syntax error

I am trying to run a simple select statement based on a form that supplied the input. I have tested it hard coded and it works. When I pass parameters I get a useless Syntax error message. Can you please help me understand what I am doing wrong. I have checked and double checked all my spellings... when I debug the error happens on the Set rs = db.OpenRecordset(StrSQL) statement. My complete code is below.

Thanks in advance.

Private Sub Run_Qry_SvrTns_Click()
On Error GoTo Err_Run_Qry_SvrTns_Click
 
  
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rs As DAO.Recordset
    
    Dim P_SRNum As String
    Dim P_SerialNum As String
    Dim P_SerialCtl As String
    Dim P_CpStat As String
    Dim P_HostNam As String
    Dim P_Lctn As String
    Dim P_ProjNam As String
    Dim P_HwMan As String
    Dim P_Mdl As String
    Dim P_HWType As String
    Dim P_HwOs As String
    Dim P_App1 As String
    Dim P_App2 As String
    Dim P_App3 As String
    Dim P_StdEnv1 As String
    Dim P_StdEnv2 As String
    Dim P_StdEnv3 As String
    Dim StrSQL As String
    Dim Quotes As String
    
    
    Set db = CurrentDb
    Set qdf = db.QueryDefs("QRY_SvrGen")
    
    ' Check passed Variables for Blanks
    If IsNull(Me.CSRNum.Value) Then
     P_SRNum = " LIKE '*' "
    Else
     P_SRNum = "='" & Me.CSRNum.Value & "' "
    End If
    '------------ End of Check Service Request
    If IsNull(Me.CSerialNum.Value) Then
     P_SerialNum = " LIKE '*' "
    Else
     P_SerialNum = "='" & Me.CSerialNum.Value & "' "
    End If
    '------------ End of Check Serial Number
    If IsNull(Me.CCtlNum.Value) Then
     P_SerialCtl = " LIKE '*' "
    Else
     P_SerialCtl = "='" & Me.CCtlNum.Value & "' "
    End If
    '------------ End of Check Capital Expenditure Control Code
    If IsNull(Me.CStatus.Value) Then
     P_CpStat = " LIKE '*' "
    Else
     P_CpStat = "='" & Me.CStatus.Value & "' "
    End If
    '------------ End of Check Status
    If IsNull(Me.CHostName.Value) Then
     P_HostNam = " LIKE '*' "
    Else
     P_HostNam = "='" & Me.CHostName.Value & "' "
    End If
    '------------ End of Check Host Name
    If IsNull(Me.CLocation.Value) Then
     P_Lctn = " LIKE '*' "
    Else
     P_Lctn = "='" & Me.CLocation.Value & "' "
    End If
    '------------ End of Check Location
    If IsNull(Me.CProjectName.Value) Then
     P_ProjNam = " LIKE '*' "
    Else
     P_ProjNam = "='" & Me.CProjectName.Value & "' "
    End If
    '------------ End of Check Project Name
    If IsNull(Me.CHWMan.Value) Then
     P_HwMan = " LIKE '*' "
    Else
     P_HwMan = "='" & Me.CHWMan.Value & "' "
    End If
    '------------ End of Check Hardware Manufacturer
    If IsNull(Me.CHWMdl.Value) Then
     P_Mdl = " LIKE '*' "
    Else
     P_Mdl = "='" & Me.CHWMdl.Value & "' "
    End If
    '------------ End of Check Hardware Model
    If IsNull(Me.CHWType.Value) Then
     P_HWType = " LIKE '*' "
    Else
     P_HWType = "='" & Me.CHWType.Value & "' "
    End If
    '------------ End of Check Hardware Type
    If IsNull(Me.CHWOs.Value) Then
     P_HwOs = " LIKE '*' "
    Else
     P_HwOs = "='" & Me.CHWOs.Value & "' "
    End If
    '------------ End of Check Hardware Operating System
    If IsNull(Me.CApp1.Value) Then
     P_App1 = " LIKE '*' "
    Else
     P_App1 = "='" & Me.CApp1.Value & "' "
    End If
    '------------ End of Check Application 1
    If IsNull(Me.CApp2.Value) Then
     P_App2 = " LIKE '*' "
    Else
     P_App2 = "='" & Me.CApp2.Value & "' "
    End If
    '------------ End of Check Application 2
    If IsNull(Me.CApp3.Value) Then
     P_App3 = " LIKE '*' "
    Else
     P_App3 = "='" & Me.CApp3.Value & "' "
    End If
    '------------ End of Check Application 3
    If IsNull(Me.CEnv1.Value) Then
     P_StdEnv1 = " LIKE '*' "
    Else
     P_StdEnv1 = "='" & Me.CEnv1.Value & "' "
    End If
    '------------ End of Check Environment 1
    If IsNull(Me.CEnv2.Value) Then
     P_StdEnv2 = " LIKE '*' "
    Else
     P_StdEnv2 = "='" & Me.CEnv2.Value & "' "
    End If
    '------------ End of Check Environment 2
    If IsNull(Me.CEnv3.Value) Then
     P_StdEnv3 = " LIKE '*'" & ";"
    Else
     P_StdEnv3 = "='" & Me.CEnv3.Value & "';"
    End If
    '------------ End of Check Environment 3
 
    Quotes = ""
    
      StrSQL = Quotes & "SELECT 18_SvrTns.* " & _
     "FROM 18_SvrTns " & _
     "WHERE 18_SvrTns.[L_SRNum]" & P_SRNum & _
     "AND 18_SvrTns.[S_SerialNum]" & P_SerialNum & _
     "AND 18_SvrTns.[S_SerialCTL]" & P_SerialCtl & _
     "AND 18_SvrTns.[S_CpStat]" & P_CpStat & _
     "AND 18_SvrTns.[S_HostNam]" & P_HostNam & _
     "AND 18_SvrTns.[L_Lctn]" & P_Lctn & _
     "AND 18_SvrTns.[S_ProjNam]" & P_ProjNam & _
     "AND 18_SvrTns.[S_HwMan]" & P_HwMan & _
     "AND 18_SvrTns.[L_Mdl]" & P_Mdl & _
     "AND 18_SvrTns.[S_HwType]" & P_HWType & _
     "AND 18_SvrTns.[S_HwOs]" & P_HwOs & _
     "AND 18_SvrTns.[S_App1]" & P_App1 & _
     "AND 18_SvrTns.[S_App2]" & P_App2 & _
     "AND 18_SvrTns.[S_App3]" & P_App3 & _
     "AND 18_SvrTns.[S_StdEnv1]" & P_StdEnv1 & _
     "AND 18_SvrTns.[S_StdEnv2]" & P_StdEnv2 & _
     "AND 18_SvrTns.[S_StdEnv3]" & P_StdEnv3 & Quotes
  
     
    Debug.Print StrSQL
    MsgBox StrSQL
    
    Set rs = db.OpenRecordset(StrSQL)
    DoCmd.SetWarnings False
    qdf.SQL = StrSQL
    DoCmd.OpenQuery ("QRY_SvrGen")
    DoCmd.SetWarnings True
 
    rs.Close
    Set rs = Nothing
    Set db = Nothing
 
 
Exit_Run_Qry_SvrTns_Click:
    Exit Sub
 
Err_Run_Qry_SvrTns_Click:
    MsgBox Err.Description
    Resume Exit_Run_Qry_SvrTns_Click
    
End Sub

Open in new window

Scott McDaniel (EE MVE )

I wouldn't say that the "syntax" error is useless ... it's telling you that your SQL statement is invalid, which is about as specific as it can get.

can you post the actual SQL immediately BEFORE the call to OpenRecordset? Just add this line:

Debug.Print StrSQL

which will print your string to the Immediate window ... post that string back here and we can review it.

rockiroads

Are all the fields you are checking defined as text? Any numerics? Any of those text fields got single quotes in them?

As LSMConsulting says, lookng at the generated sql (copy from immediate window and run it in sql to help find any errors) will help

omodarah

ASKER

Thanks LSM Consulting and Rockiroads for the prompt response. My entire code is posted in the original body of my message. All my fields are text fields with no numerics. What I cant say for sure is if I have single quotes buried somewhere. I do use Single quotes to build my LIKE test criteria, does this not work? I have attached the screen shots from my entry form, Debug statement and Error recieved.
Entry-Form-1-of-3.bmp
debug-Statment-2-of-3.bmp
Syntax-Error-3-of-3.bmp

rockiroads

Well the simple fact that you said single quotes exist means wrapping strings in single quotes will break it
eg

You have the string fred's

if you wrapped this, you would get 'fred's

so you got fred in quotes and a rogue s. Does this make sense?

So what you should do is

instead of this

P_StdEnv3 = "='" & Me.CEnv3.Value & "';"

do this

P_StdEnv3 = "=" & chr$(34) & Me.CEnv3.Value & chr$(34)

See how I got rid of your single quotes and used chr$(34) instead (this represents a double quote)

rockiroads

An example of changing your 5 line if check into one line

eg

If IsNull(Me.CEnv3.Value) Then
P_StdEnv3 = " LIKE '*'" & ";"
Else
P_StdEnv3 = "='" & Me.CEnv3.Value & "';"
End If

to

P_StdEnv3 = " LIKE " & chr$(34) & NZ(Me.CEnv3.Value,"*") & chr$(34)

NZ replaces a null string to whatever you want, in this case I put down *

Scott McDaniel (EE MVE )

Also, is there some reason you're using LIKE with the * operator? To me, that's an odd construct ... it would seem to me that you'd NOT need those WHERE items. For example, what does the LIKE '*' accomplish that simply leaving it out doesn't? If your user has not selected a value, then they obviously don't care or don't want to see that field ... IOW, have you tried this:

'------------ End of Check Application 2
If Not IsNull(Me.CApp3.Value) Then P_App3 = "='" & Me.CApp3.Value & "' "

for all your filters?

omodarah

ASKER

LSMConsulting / Rockiroads,

Thanks for all the information and advice, forgive my ignorance. I am an RPG programmer by birth trying to get my head around this temperamental VB programming :-)

So I have tried to incorporate your advice and droped all references to "*" and LIKE to simplify things. I now build my statement depending on what is passed. Unfortunately I still get a syntax error.

The good news is that when I dont select any options, the basic SELECT * FROM TableName works perfectly, it only complains of an error as soon as I pass any of my parameters. I have tried enclosing my whole Select statement in CHR$(34) or CHR$(39) and none make a difference. When I use the msgbox to view the code to be excecuted it looks fine.

Please see the revised code and screen shots.

Thanks in advance.

On Error GoTo Err_Run_Qry_SvrTns_Click
 
  
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rs As DAO.Recordset
    
    Dim P_SRNum As String
    Dim P_SerialNum As String
    Dim P_SerialCtl As String
    Dim P_CpStat As String
    Dim P_HostNam As String
    Dim P_Lctn As String
    Dim P_ProjNam As String
    Dim P_HwMan As String
    Dim P_Mdl As String
    Dim P_HWType As String
    Dim P_HwOs As String
    Dim P_App1 As String
    Dim P_App2 As String
    Dim P_App3 As String
    Dim P_StdEnv1 As String
    Dim P_StdEnv2 As String
    Dim P_StdEnv3 As String
    Dim StrSQL As String
    Dim Operator As String
    
    
    Set db = CurrentDb
    Set qdf = db.QueryDefs("QRY_SvrGen")
    
    '--- If no options are selected
    StrSQL = "Select 18_SvrTns.* FROM 18_SvrTns"
    Operator = " WHERE "
    
    '--- If any of the options are selected, build SELECT statement incrementaly
    If Not IsNull(Me.CSRNum.Value) Then
    StrSQL = StrSQL & Operator & "18_SvrTns.[L_SRNum] = " & Chr$(34) & Nz(Me.CSRNum.Value) & Chr$(34)
    Operator = " AND "
    End If
    '------------ End of Check Service Request
    If Not IsNull(Me.CSerialNum.Value) Then
    StrSQL = StrSQL & Operator & "18_SvrTns.[S_SerialNum] = " & Chr$(34) & Me.CSerialNum.Value & Chr$(34)
    Operator = " AND "
    End If
    '------------ End of Check Serial Number
    If Not IsNull(Me.CCtlNum.Value) Then
    StrSQL = StrSQL & Operator & "18_SvrTns.[S_SerialCTL] = " & Chr$(34) & Me.CCtlNum.Value & Chr$(34)
    Operator = " AND "
    End If
    '------------ End of Check Capital Expenditure Control Code
    If Not IsNull(Me.CStatus.Value) Then
    StrSQL = StrSQL & Operator & "18_SvrTns.[S_CpStat] = " & Chr$(34) & Me.CStatus.Value & Chr$(34)
    Operator = " AND "
    End If
    '------------ End of Check Status
    If Not IsNull(Me.CHostName.Value) Then
    StrSQL = StrSQL & Operator & "18_SvrTns.[S_HostNam] = " & Chr$(34) & Me.CHostName.Value & Chr$(34)
    Operator = " AND "
    End If
    '------------ End of Check Host Name
    If Not IsNull(Me.CLocation.Value) Then
    StrSQL = StrSQL & Operator & "18_SvrTns.[L_Lctn] = " & Chr$(34) & Me.CLocation.Value & Chr$(34)
    Operator = " AND "
    End If
    '------------ End of Check Location
    If Not IsNull(Me.CProjectName.Value) Then
    StrSQL = StrSQL & Operator & "18_SvrTns.[S_ProjNam] = " & Chr$(34) & Me.CProjectName.Value & Chr$(34)
    Operator = " AND "
    End If
    '------------ End of Check Project Name
    If Not IsNull(Me.CHWMan.Value) Then
    StrSQL = StrSQL & Operator & "18_SvrTns.[S_HwMan] = " & Chr$(34) & Me.CHWMan.Value & Chr$(34)
    Operator = " AND "
    End If
    '------------ End of Check Hardware Manufacturer
    If Not IsNull(Me.CHWMdl.Value) Then
    StrSQL = StrSQL & Operator & "18_SvrTns.[L_Mdl] = " & Chr$(34) & Me.CHWMdl.Value & Chr$(34)
    Operator = " AND "
    End If
    '------------ End of Check Hardware Model
    If Not IsNull(Me.CHWType.Value) Then
    StrSQL = StrSQL & Operator & "18_SvrTns.[S_HwType] = " & Chr$(34) & Me.CHWType.Value & Chr$(34)
    Operator = " AND "
    End If
    '------------ End of Check Hardware Type
    If Not IsNull(Me.CHWOs.Value) Then
    StrSQL = StrSQL & Operator & "18_SvrTns.[S_HwOs] = " & Chr$(34) & Me.CHWOs.Value & Chr$(34)
    Operator = " AND "
    End If
    '------------ End of Check Hardware Operating System
    If Not IsNull(Me.CApp1.Value) Then
    StrSQL = StrSQL & Operator & "18_SvrTns.[S_App1] = " & Chr$(34) & Me.CApp1.Value & Chr$(34)
    Operator = " AND "
    End If
    '------------ End of Check Application 1
    If Not IsNull(Me.CApp2.Value) Then
    StrSQL = StrSQL & Operator & "18_SvrTns.[S_App2] = " & Chr$(34) & Me.CApp2.Value & Chr$(34)
    Operator = " AND "
    End If
    '------------ End of Check Application 2
    If Not IsNull(Me.CApp3.Value) Then
    StrSQL = StrSQL & Operator & "18_SvrTns.[S_App3] = " & Chr$(34) & Me.CApp3.Value & Chr$(34)
    Operator = " AND "
    End If
    '------------ End of Check Application 3
    If Not IsNull(Me.CEnv1.Value) Then
    StrSQL = StrSQL & Operator & "18_SvrTns.[S_StdEnv1] = " & Chr$(34) & Me.CEnv1.Value & Chr$(34)
    Operator = " AND "
    End If
    '------------ End of Check Environment 1
    If Not IsNull(Me.CEnv2.Value) Then
    StrSQL = StrSQL & Operator & "18_SvrTns.[S_StdEnv2] = " & Chr$(34) & Me.CEnv2.Value & Chr$(34)
    Operator = " AND "
    End If
    '------------ End of Check Environment 2
    If Not IsNull(Me.CEnv3.Value) Then
    StrSQL = StrSQL & Operator & "18_SvrTns.[S_StdEnv3] = " & Chr$(34) & Me.CEnv3.Value & Chr$(34)
    End If
    '------------ End of Check Environment 3
    
    'Add final semicolon to close statement.
    StrSQL = StrSQL & ";"
    
     Debug.Print StrSQL
    MsgBox StrSQL
    
    Set rs = db.OpenRecordset(StrSQL)
    DoCmd.SetWarnings False
    qdf.SQL = StrSQL
    DoCmd.OpenQuery ("QRY_SvrGen")
    DoCmd.SetWarnings True
 
    rs.Close
    Set rs = Nothing
    Set db = Nothing
 
 
Exit_Run_Qry_SvrTns_Click:
    Exit Sub
 
Err_Run_Qry_SvrTns_Click:
    MsgBox Err.Description
    Resume Exit_Run_Qry_SvrTns_Click
    
End Sub

Open in new window

debug-Statment-1-of-2.jpg
Syntax-Error-2-of-2.jpg

rockiroads

When u run this, when the msgbox appears, in the immediate window from the vba editor, that same sql is dumped, copy/paste it here and also in a new sql and try to run it, see where access tells you where the problem is.

Also regarding this

Set rs = db.OpenRecordset(StrSQL)
DoCmd.SetWarnings False
qdf.sql = StrSQL
DoCmd.OpenQuery ("QRY_SvrGen")
DoCmd.SetWarnings True

What are your intentions with the query? You are opening a recordset but not doing anything with it. You are also resetting the sql and opening the query.

omodarah

ASKER

Here is the dumped string:

Select 18_SvrTns.* FROM 18_SvrTns WHERE 18_SvrTns.[L_SRNum]='LEG9991124';

Also as per opening the recordset, frankly it does not make any sense to me, but that is what worked. I tried to run a simple SELECT query without any UPDATE, INSERT e.t.c and couldnt get it to work. I then googled and found the only way to run a simple Select was to open a recordset. In my world, you only open a file you want to read records from. I did it and it worked..thats all I can say.

If there is a more efficient or correct way to run a simple SELECT query then please let me know. It will save us all a lot of typing... The time I have spent on this I could have written a full blow RPG ILE Application with a beautiful interface to go with it... :-)

ASKER CERTIFIED SOLUTION

rockiroads

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

omodarah

ASKER

O.K Rockiroads,

You are the Master, I bow to you. It worked beautifully. I must say I am learning a lot, VB programming is a whole different perspective when you are used to a real programming language like RPG :-)... sorry I had to drop that in... I guess I may just have started a fight by my comment, but thats o.k.

Anyway thanks again. Here is what worked:-

Select 18_SvrTns.* FROM 18_SvrTns WHERE ([18_SvrTns].[L_SRNum]) = "LEG9999999" AND ([18_SvrTns].[S_CpStat]) = "Installed";

Best wishes.

rockiroads

No probs. Remember though you got some help improving your code from LSMConsulting

omodarah

ASKER

Thanks for reminding me. Thanks LSMConsulting and sorry for forgetting you. Lunch is on me if you can find me :-)