Variable error in select statement

Y

ysac

Member
Joined
May 25, 2003
Messages
7
Hi,

Im having a problem with using a variable in my Select statement to filter in a MS Access SQL statement. Following:see code bellow

A. When I use B-1000 in the select statement, the query does its job like it should and shows me all selected details.(see code A)

B. However, if I use a variable (txtPost as String) with the same value, the query returns no records at all.

What do I do wrong ?

Code A: Works fine
Me.OleDbSelectCommand1.CommandText = "SELECT Name, Company, Address, Email" & _
" FROM CRM_contacts WHERE (Email <> ) AND (Postcode = B-1000) ORDER BY Company"

Me.OleDbSelectCommand1.Connection = Me.cnContactList

daContacts.Fill(DsContacts)
Me.DsContacts_PositionChanged()


Code B: Does not work fine
Dim txtPost As String

txtPost = "B-1000"

Me.OleDbSelectCommand1.CommandText = "SELECT Name, Company, Address, Email" & _
" FROM CRM_contacts WHERE (Email <> ) AND (Postcode = txtPost) ORDER BY Company"

Me.OleDbSelectCommand1.Connection = Me.cnContactList

daContacts.Fill(DsContacts)
Me.DsContacts_PositionChanged()

So, here (Postcode = txtPost) doesnt give the expected result.

Thanks in advance.
 
I believe you need to change as follows:

...AND (Postcode = "+txtPost+" ) ORDER ...

I put a space between the single and double quote for readability (you wouldnt do that).
 
I would change the email part as well...
Code: 
"SELECT Name, Company, Address, Email " & _
"FROM CRM_contacts " & _
"WHERE Email Is not null AND Postcode = " & txtPost & " " & _ 
"ORDER BY Company"
 
Back
Top