?
Solved

SQL and VBA syntax error

Posted on 2009-05-04
13
Medium Priority
?
895 Views
Last Modified: 2013-11-28
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

0
Comment
Question by:omodarah
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 2
13 Comments
 
LVL 85
ID: 24293719
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.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24294018
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
0
 

Author Comment

by:omodarah
ID: 24295545
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
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 65

Expert Comment

by:rockiroads
ID: 24297738
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)

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24297761
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 *
0
 
LVL 85
ID: 24298398
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?
0
 

Author Comment

by:omodarah
ID: 24307365
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
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24308625
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.
0
 

Author Comment

by:omodarah
ID: 24309095
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...  :-)
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 24309201
But what do you want to do with the select statement?

Lol, a confused RPG programmer.


I cant see what is wrong with the sql statement bar possibly the number

Can u wrap all occurrences of 18_SvrTns after where clause with []

eg


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


eg

    StrSQL = StrSQL & Operator & "[18_SvrTns].[L_SRNum] = " & Chr$(34) & Nz(Me.CSRNum.Value) & Chr$(34)


Maybe its getting confused since it reads

where 18 ...

0
 

Author Comment

by:omodarah
ID: 24309365
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.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24309397
No probs. Remember though you got some help improving your code from LSMConsulting
0
 

Author Comment

by:omodarah
ID: 24309697
Thanks for reminding me.  Thanks LSMConsulting and sorry for forgetting you. Lunch is on me if you can find me :-)
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question