Solved

SQL and VBA syntax error

Posted on 2009-05-04
13
884 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
  • 6
  • 5
  • 2
13 Comments
 
LVL 84
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
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
Comment Utility
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 84
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:omodarah
Comment Utility
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
Comment Utility
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
Comment Utility
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 125 total points
Comment Utility
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
Comment Utility
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
Comment Utility
No probs. Remember though you got some help improving your code from LSMConsulting
0
 

Author Comment

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

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

744 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now