Wildcards in queries (1 Viewer)

[widemonk]

I would like a query where the search criteria is populated from a form - pretty simple so far. However, I would like to include ALL records where the data on the Form in blank

For instance, Form fields...
Name: John Smith
Date: [blank]

Result... All records for John Smith, regardless of date.

I can only assume that my current query is taking the Date field to literally be blank and only returns records where the date is blank (which of course, there arent any).

Because I would like to include the date picker icon, the field type has to be 'Date' and so does not support users to enter an * wildcard. Incidentally, this is the same for the Name field, being taken from a combo box which also will not support * wildcards if the query is to run All members on a set date.

If I enter the * wildcard at the query level, can I get the form to override this wildcard *IF* the appropriate form field is not blank ??

Any ideas please ?

 

[Alansidman]

Please post your sql statement for your query so that we can evaluate what it is you are actually doing and compare it to what you want to do.

 

[widemonk]

Because the fields on the form are blank, the query is searching for blank records in the table.

If the form fields are completed, use that data as the query criteria (for instance 'Auditor' or 'Audit Date').
If the form fields are blank, include ALL records (for instance 'PalletID').

SELECT tblSession.Auditor, tblSession.AuditDate, tblPalletAudit.PalletID
FROM tblPalletAudit INNER JOIN tblSession ON tblPalletAudit.SessionID = tblSession.SessionID
WHERE (((tblSession.Auditor)=[Forms]![frmReport2].[txtAuditorID]) AND ((tblSession.AuditDate)=[Forms]![frmReport2].[txtAuditDate]) AND ((tblPalletAudit.PalletID)=[Forms]![frmReport2].[txtPalletID]));

For instance, using the example form (attached) should return all records for today, regardless of who did them.

 

[jdraw]

See the tutorials here
http://www.fontstuff.com/access/acctut19.htm

 

[Alansidman]

Here is a tutorial that I like for building a search form.
http://www.datapigtechnologies.com/flashfiles/searchform.html
Alan