Turning criteria off when no value is put forward

[NJK]

I have a query that retrieves contact details and uses values in another form as criteria to filter the results for a report. However I only want it to use criteria if a value is given for that detail.

For example I want a report to output all contacts from company "xyz" so I have set "xyz" as the company filter on the form. If I have not selected a company in the form I want the query to return all contacts - effectively the filter is switched off.

How do I do this?

[CJ_London]

the basic construct is

WHERE (Company='" & forms!myform!companyfilter & "' OR " & forms!myform!companyfilter & " is Null)

[NJK]

the basic construct is

WHERE (Company='" & forms!myform!companyfilter & "' OR " & forms!myform!companyfilter & " is Null)

sorry still getting anything with a value in or not. To make sure I have interpreted it correctly this is the filter code I entered into the query and why:-

WHERE (tblContacts.Company='" & Forms![Contact report]!CompanyCombo.value & "' OR " & Forms![Contact report]!CompanyCombo.value & " is Null)



tbl.Contacts.Company - is the table and field

Forms![Contact report]!CompanyCombo.value - is the form and value location

Many thanks

[CJ_London]

your interpretation for field/table/control names is correct - but construction is not. My mistake, I mixed two styles. If you are building this in your query then your query will look something like

SELECT *
FROM tblContacts
WHERE (Company= Forms![Contact report]!CompanyCombo OR Forms![Contact report]!CompanyCombo is Null)

No need to use the .value - that is the default property used


Also if you are using lookups in your contacts table for the company field, this can cause confusion. Depends on how it is constructed but you may be seeing text but looking up a number

[NJK]

your interpretation for field/table/control names is correct - but construction is not. My mistake, I mixed two styles. If you are building this in your query then your query will look something like

No need to use the .value - that is the default property used


Also if you are using lookups in your contacts table for the company field, this can cause confusion. Depends on how it is constructed but you may be seeing text but looking up a number

Spot on thanks