Search multiple fields from one text box

[pbaldy]

It is that field. It's spelled differently in the query than in the table, which is why you get a parameter prompt when you open the query.

[tommy93]

Ok that doesnt seem to fix the problem, i select the fields from the drop down menu in the design view and select them from the table directly in there.

Is there anything else you did? Or could you upload the sql of it working for you? Cause it makes no difference for me at all...

[tommy93]

Still cant get this to work... Tried the spelling mistake to no avail?

[pbaldy]

This is the query that appeared to work:

SELECT [Job Form].[Invoice Number], [Job Form].Customer, [Job Form].[Booking Reference], [Job Form].[B/L Number], [Job Form].[Number of Containers], [Job Form].Line, [Job Form].Vessel, [Job Form].ETA, [Job Form].ETS, [Job Form].[Invoice Value (£)], [Job Form].[Cost (£)], [Job Form].[Payment Recieved (£)], [Job Form].[Payment Made], [Job Form].[Payment Made], [Job Form].[Internal invoice number], [Job Form].IntInvoice2, [Job Form].IntInvoce3
FROM [Job Form]
WHERE ((([Job Form].[Invoice Number]) Like [forms]![Search]![qinvoice] & "*") AND (([Job Form].Customer) Like [forms]![search]![qcustomer] & "*") AND (([Job Form].[Booking Reference]) Like [forms]![search]![qbooking] & "*") AND (([Job Form].[B/L Number]) Like [forms]![search]![qbl] & "*") AND (([Job Form].Line) Like [forms]![search]![qline] & "*") AND (([Job Form].[Payment Made]) Like [forms]![search]![qlinepayment] & "*") AND (([Job Form].[Internal invoice number]) Like [forms]![Search]![qInternal] & "*")) OR ((([Job Form].[Invoice Number]) Like [forms]![Search]![qinvoice] & "*") AND (([Job Form].Customer) Like [forms]![search]![qcustomer] & "*") AND (([Job Form].[Booking Reference]) Like [forms]![search]![qbooking] & "*") AND (([Job Form].[B/L Number]) Like [forms]![search]![qbl] & "*") AND (([Job Form].Line) Like [forms]![search]![qline] & "*") AND (([Job Form].[Payment Made]) Like [forms]![search]![qlinepayment] & "*") AND (([Job Form].IntInvoice2) Like [forms]![Search]![qInternal] & "*")) OR ((([Job Form].[Invoice Number]) Like [forms]![Search]![qinvoice] & "*") AND (([Job Form].Customer) Like [forms]![search]![qcustomer] & "*") AND (([Job Form].[Booking Reference]) Like [forms]![search]![qbooking] & "*") AND (([Job Form].[B/L Number]) Like [forms]![search]![qbl] & "*") AND (([Job Form].Line) Like [forms]![search]![qline] & "*") AND (([Job Form].[Payment Made]) Like [forms]![search]![qlinepayment] & "*") AND (([Job Form].IntInvoce3) Like [forms]![Search]![qInternal] & "*"));

[tommy93]

Wait, in the printscreen the results are shown in the query results table, not the report that its meant to show in?

Did it show the results in the report as well or is it just in the normal results table you can get it to work?

EDIT: Okay, done some playing around with it an your right. Query works fine. Its getting the data to show in the "Search" form, thats where its failing.

[pbaldy]

I didn't really look at the report. The query named "Search" appeared to have the fixes applied to it and the SQL behind the report didn't, so I assumed it was that query we were looking at. If the report is the goal, can't you base it on the working query?

[tommy93]

Yeah i didnt realise your sql suggestion actually fixed the query itself, so thanks for that. Helps a lot.

I was just attempting to use the report every time, then when you said you had it working and mine still wasnt, i noticed the printscreen showed the query itself not the report. When trying that i realised that the query now works, just the report doesnt.

Im going to try, information in the report is pulled in from both the query and the jobs table though, so...