Variable error in select statement

[ysac]

Hi,

 

I'm 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 it's 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') doesn't give the expected result.

 

Thanks in advance.

[bwgc]

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 wouldn't do that).

[Robby]

I would change the email part as well...

"SELECT Name, Company, Address, Email " & _
"FROM CRM_contacts " & _
"WHERE Email Is not null AND Postcode = '" & txtPost & "' " & _ 
"ORDER BY Company"

[ysac]

Thanks, This works !