Criteria from form to query with wild card

[SteveW]

Hi I am having trouble getting a query to work and help would be most appreciated.
I have a main form with several tabs that contain various forms.
One of the forms has customer details on it.
Unfortunately the address has been put into only one field called CustDelivery.
a typical entry in the field would be something like this "57 Midas Road Malaga WA 6275"
My problem is that the customer wants to filter the field [CustDelivery] in the table tblCust to find only records that have Malaga or any other suburb within the field [CustDeliovery].

I have an Unbound text box called [txtFilterSuburb] on a form called frmCustomer that is part of an unbound form called frmMain so that he can enter a suburb of his choice and a button that will query the Customer table.

The query looks like this but it does not work. I cant get the query to get the criteria from the unbound text box on the form.

SELECT tblCust.CustID AS tblCust_CustID, tblCust.CustName, tblCust.CustDelivery, tblCust.CustPhone, tblCust.CustMobile, tblCust.CustFax, tblCust.CustEmail
FROM tblCust
WHERE (((tblCust.CustDelivery) Like "*[Forms]![frmMain]![frmCustomer].[Form]![txtFilterSuburb]*"));

[kennejd]

I think you want this:
SELECT tblCust.CustID AS tblCust_CustID, tblCust.CustName, tblCust.CustDelivery, tblCust.CustPhone, tblCust.CustMobile, tblCust.CustFax, tblCust.CustEmail
FROM tblCust
WHERE (((tblCust.CustDelivery) Like "*" & [Forms]![frmMain]![frmCustomer].[Form]![txtFilterSuburb] & "*"));

[SteveW]

Thanks but it still does not work.
It seems that there is something wrong with the way Im getting the criteria from the form as this works.

SELECT tblCust.CustID AS tblCust_CustID, tblCust.CustName, tblCust.CustDelivery, tblCust.CustPhone, tblCust.CustMobile, tblCust.CustFax, tblCust.CustEmail
FROM tblCust
WHERE (((tblCust.CustDelivery) Like "*" & "Malaga" & "*"));

But as soon as I replace the Malaga Criteria with a text box location that has Malaga in it then it fails.

[SteveW]

Ok I have fixed it.

when I place the criteria in the query builder
as Like "*" & Forms![frmMain]![frmCustomer].[Form]![txtFilterSuburb] & "*"
the query builder places a square bracket arround [Forms]! which is not correct like this
Like "*" & [Forms]![frmMain]![frmCustomer].[Form]![txtFilterSuburb] & "*"


So when I go to SQL view and edit to Like "*" & Forms![frmMain]![frmCustomer].[Form]![txtFilterSuburb] & "*"));

Removing the square brackets around Forms! then it works.

So it seems that the query builder adds square brackets sometimes where they are not wanted which is most annoying.

Note: now the SQL view shows no Square brackets but the query builder does show them.

If you let the query builder build the code it always puts the square brackets around [Form]! and in SQL view they will be there also so I had to edit them out then it all works.