VBA and SQL

[pscott]

I am trying to get data from fields in a form input by the user and then compare those field values to data already in the table. It involves checking the values of 3 fields together and I have written it as follows - obviously incorrect syntax:

strSQL = "SELECT ((t_main_venue_details.Venue_Name),(t_main_venue_details.proprietor_name),(t_main_venue_details.town)) FROM [t_Main_Venue_Details] WHERE ((t_Main_Venue_Details.venue_name = '" & Me.Venue_Name & "'" ) and (t_Main_Venue_Details.proprietor_Name = '" & me.proprietor_name & "'") and (t_Main_Venue_Details.town = ' " & me.town & "'" ))

It works with just one field from the recordset and then using where to the related field in the form, so the recordset bit is OK, I think.

Any advice appreciated. Thx

 

[Scooterbug]

What I do to check to see if the sytax of an sql statement is valid is this:

1. Use debug.print strSql. What this will do is print the value of the string into the Immediate Window. Copy the Sql.
2. Create a new query, but dont add any tables. Instead, go the the Sql view (First Icon on the ribbon up top) and paste in the sql. Try switching the view to design. If there is a syntax error, an message box will appear and when you close, the portion of the sql that is wrong will be highlighted. From there you can make the necessary changes until the design view displays. Make the changes to your sql in code and you should be good to go.

I also like to build the sql in stages...makes troubleshooting a bit easier.

strSQL = "SELECT ((t_main_venue_details.Venue_Name),(t_main_venue_d etails.proprietor_name),(t_main_venue_details.town )) "
strSql = strSql & "FROM [t_Main_Venue_Details] "
strSql = strSql & "WHERE ((t_Main_Venue_Details.venue_name = '" & Me.Venue_Name & "'" ) "
strSql = strSql & "AND (t_Main_Venue_Details.proprietor_Name = '" & me.proprietor_name & "'") "
strSql = strSql & "AND (t_Main_Venue_Details.town = ' " & me.town & "'" [COLOR=red][COLOR=black]))[/COLOR];[/COLOR][COLOR=black]"[/COLOR]

Dunno if it's the problem...but you are missing a semicolon at the end of your sql statement.

 

[wazz]

you don't need any of the parentheses. but i think the problem is at the ends of your statements:
& "'" ) "
should be
& " ' ) "

 

[tranchemontaigne]

In answer to yoru post, I see an error near the end of your SQL statement

& me.town & "'" ));"

should read

& me.town & "' ));"

or

& me.town & "'" & " ));"

================================
General thoughts regarding SQL in VBA
================================
Always be on the look out for double quote characters within an SQL statement as VBA uses them to mark the beginning and end of a string. This is what you should think of first if you receive errors telling you that you have unexpected characters found after the end of a statement. I always replace them with ASCII character codes when writing SQL statements within a module

Other character codes I have needed to use in the past include the following:
double quote = chr(34)
asterisk = chr(42)
space character = chr(32)
semi colin = chr(59)

As you are probably aware, WHERE clauses requrie a set number of nested parentheses in MS Access, but not in ANSI standard SQL
________
William clay ford, jr. history

 

[Scooterbug]

I always have trouble remember the rules for using quotes inside of quotes...so I make a quick function to help me out:

Public Function QuoteWrapper(stString as String) as String
 
QuoteWrapper = chr(34) & stString & chr(34)

I can then wrap anything that requires to be in quotes withing quotes not not worry about it.