Currently i have a query that is based on inputs from a search form. The query will not return any results if one of the fields in the table is empty. Currently, i have to fill them all with "N/A" for the search to work properly.
This is the sql for my query as it stands -
Code:
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].[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].[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].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].IntInvoce3) Like forms!Search!qInternal & "*"));