search boxes

[cybertyk]

ok i have 6 text boxes which for thi example i have named text1 - text6 will this code work if i want to change a record source of a lstbox?

strsql = "SELECT tblCustomer.[Company Name], tblCustomer.[Company City], tblCustomer.[Company County], tblCustomer.[Company Postcode], tblCustomer.[Company Catagory], tblCustomer.[Company Employee Size] FROM tblCustomer WHERE (((tblCustomer.[Company Name])= " & Nz(Me.Text1,*) & ") AND ((tblCustomer.[Company City])=" & Nz(Me.Text2,*) & ") AND ((tblCustomer.[Company County])=" & Nz(me.Text3, *) & ") AND ((tblCustomer.[Company Postcode])=" & Nz(Me.Text4, *) & ") AND ((tblCustomer.[Company Catagory])=" & Nz(Me.Text5, *) & ") AND ((tblCustomer.[Company Employee Size])=" & Nz(Me.Text6,*) & "));"

pleasee help

oh im doing this in VBA which is why im posting here XD

 

[cybertyk]

ok i have got this so far

strsql = "SELECT tblCustomer.[Company Name], tblCustomer.[Company City], tblCustomer.[Company County], tblCustomer.[Company Postcode], tblCustomer.[Company Catagory], tblCustomer.[Company Employee Size] FROM tblCustomer WHERE (tblCustomer.[Company Name])= Like *" & Nz(Me.Text1, "*") & "*;"

i get a syntex error and i cant see why i am can anyone help please

 

[JANR]

WHERE (tblCustomer.[Company Name])[COLOR=red]=[/COLOR] Like *" & Nz(Me.Text1, "*") & "*;"

Remove the equal sign!!

your where-clause reads:

WHERE (tblCustomer.[Company Name])= Like *xxx*;


JR

 

[cybertyk]

ok that worked but now when i use the full code i get another syntex error can anyone see why i need some fresh eyes

strsql = "SELECT tblCustomer.[Company Name], tblCustomer.[Company City], tblCustomer.[Company County], tblCustomer.[Company Postcode], tblCustomer.[Company Catagory], tblCustomer.[Company Employee Size] FROM tblCustomer WHERE (((tblCustomer.[Company Name]) Like '" & Nz(Me.txtCompanyName, "*") & "') AND ((tblCustomer.[Company City]) Like '" & Nz(Me.txtCompanyCity, "*") & "') AND ((tblCustomer.[Company County]) Like '" & Nz(Me.txtCompanyCounty, "*") & "') AND ((tblCustomer.[Company Postcode]) Like '" & Nz(Me.txtCompanyPostcode, "*") & "') AND ((tblCustomer.[Company Catagory]) Like '" & Nz(Me.txtCompanyCatagory, "*") & "') AND ((tblCustomer.[Company Employee]) Like '" & Nz(Me.txtCompanyEmployee, "*") & "');"

 

[JANR]

Its hard to tell, but if you use a Debug.Print after your strsql, you can see the full SQL string in the immediate window and see if you can spot obvious mistakes.

Private Sub Somesub()
Dim strsql As String
strsql = " SELECT ........"
Debug.Print strsql
.....
End Sub

JR