Need Help learning to properly delimit Sql strings.

  • Thread starter Thread starter Jim Evans
  • Start date Start date
J

Jim Evans

Can someone please point me in the right direction to learn the proper
syntax for delimiting SQL strings?

I am working on a SQL string and I know thw SQL syntax is correct . I
believe that the text is not delimited properly.

This is the string giving me fits now:
Wrapping is from the news client. the entire string is entered on one line.

strSql = "select * from tblCustomers where LastName like '" & Me.txtLastName
& "*' And FirstName Like '" & Me.txtFirstName & "*'"""

delimiting SQL can be a bear but it seems that the delimiting in Access is
different and, at least to me, less understandable that, say, vbs and .asp.

Thanks for any help.

Jim Evans
 
Jim Evans said:
Can someone please point me in the right direction to learn the proper
syntax for delimiting SQL strings?

I am working on a SQL string and I know thw SQL syntax is correct . I
believe that the text is not delimited properly.

This is the string giving me fits now:
Wrapping is from the news client. the entire string is entered on one line.

strSql = "select * from tblCustomers where LastName like '" & Me.txtLastName
& "*' And FirstName Like '" & Me.txtFirstName & "*'"""

delimiting SQL can be a bear but it seems that the delimiting in Access is
different and, at least to me, less understandable that, say, vbs and
Click to expand...
..asp.

The easiest way to deal with this is to put...

Debug.print strSQL

....on the line following the one that assigns the string to the variable.
Then you can examine the resulting string in the debug window and even
paste it into a blank query window to see if it runs as expected.

Looks to me like you have too many closing double quotes.
 
strSql = "select * from tblCustomers where LastName like '" & Me.txtLastName
& "*' And FirstName Like '" & Me.txtFirstName & "*'"""
Click to expand...

tmpStart= "'"
tmpEND="*'"

tmpLastName = tmpStart & Me.txtLastName &tmpEnd
tmpFirstName= tmpStart & Me.txtFirstName &tmpEnd

strSql = "SELECT *" _
&" FROM tblCustomers" _
&" WHERE LastName like " & tmpLastName _
& " AND FirstName Like " & tmpFirstName

is this clearer now?

so you want that the beginning of the first AND last -name is from
your Form


If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
MW
 
Thanks, Rick. Good Tip.

Jim Evans
Rick Brandt said:
.asp.

The easiest way to deal with this is to put...

Debug.print strSQL

...on the line following the one that assigns the string to the variable.
Then you can examine the resulting string in the debug window and even
paste it into a blank query window to see if it runs as expected.

Looks to me like you have too many closing double quotes.
Click to expand...
 
Andi,

Excellent demonstration.

Jim Evans

Andi Mayer said:
tmpStart= "'"
tmpEND="*'"

tmpLastName = tmpStart & Me.txtLastName &tmpEnd
tmpFirstName= tmpStart & Me.txtFirstName &tmpEnd

strSql = "SELECT *" _
&" FROM tblCustomers" _
&" WHERE LastName like " & tmpLastName _
& " AND FirstName Like " & tmpFirstName

is this clearer now?

so you want that the beginning of the first AND last -name is from
your Form


If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
MW
Click to expand...
 
Back
Top