Like '*' not returning black date fields...

[Hammondo]

FormA displays contains SubformA and a text box 'Text1'

A date is entered into Text1.

SubformA is based upon QueryA.

QueryA displays records from TableA, displaying them in SubformA.

QueryA's query criteria are selected from numerous text boxes and combo boxes on FormA.

TableA contains a date field (DateA) that is not populated for every record in the table.

QueryA's criteria for this field looks something like:

Like [Forms]![FormA]![Text1]

Text1 is defaulted to '*'

This I expected to return all records in Table1 as this (I though) effective means Like '*' ie, return everything...

However this only returns records from Table1 who's DateA value has been set. Those records who's date is blank do not get returned.

I can't do a OR IsNull, because when I do enter a date in Text1, I get all matched records + all the ones with blank dates too!!!

Help!!! How can I get around this?

Best,

Perplexed of UK origin.

 

[hsitz]

One way would be to add an expression field to Query A, whose only purpose will be to select records with the dates you want.

You could use the function: iif(isnull([datefield]), #1/1/1900#, [datefield]) as the expression for the new column. Then the Like criteria, if it's working the way you say it is, should select the correct records. Don't include this field as a column in the recordset returned by the query (i.e., don't have its box checked), just use it as a selection criteria. The #1/1/1900# date value is an aribtrary one, and I use it assuming that you aren't interested in any dates back that far. It's used only to return a date value in the expression column for records that have null date fields, so that even nulls will be in the query recordset when you use the like '*' criterion.

I'd be interested in hearing how you resolve your problem, whether it's through this or some other method.

-- Herb

 

[Hammondo]

Herb, your the best... that works perfectly.

I used the following:

Expr1: IIf(([Validation Date]) Is Null,#01/01/1900#,[Validation Date])

Then query the expression column with:

(Like [Forms]![IssueQueryForm]![ValidatedOnStart] Or >=[Forms]![IssueQueryForm]![ValidatedOnStart]) And (Like [Forms]![IssueQueryForm]![ValidatedOnEnd] Or <=[Forms]![IssueQueryForm]![ValidatedOnEnd])

Like I say, you're a star!