Date Query

[steve_4477]

Alright all, been working on this one a while, can't fig it out. I have a table with 5,000+ entries, 20% (900 or so) of which have a create date entered. I also have a date field in which a user can enter a "start date" to return all accounts created after this date. Here is the output I need:

1) If no date is entered into the field, all 5,000+ entries show in the listbox

2) If a date is entered, only the entries with a date >= that date will show.

Here is the general idea, just cant get the syntax correct:

IIf([Forms]![FRM_SearchMulti]![CreatedDate] Is Null,>#01/01/1900,>=[Forms]![FRM_SearchMulti]![CreatedDate])

Any ideas??

 

[Galaxiom]

I take it you are trying this in the Where clause. That isn't valid syntax.

Try this:

Where [datefieldname] >= Nz([Forms]![FRM_SearchMulti]![CreatedDate]) OR ([Forms]![FRM_SearchMulti]![CreatedDate] Is Null)

BTW Dates must be formatted #mm/dd/yyyy#
You have no hash at the end of the one you tried to use.

 

[steve_4477]

I am writing this in the criteria of a query. I have tried the Nz and Is Null functions but no dice. The work for if the search field is null but do not return all of the empty fields in the table. I think it will work in the IIF statement if I can get the sytax right.

 

[Galaxiom]

Records with no date entered are Null. They won't be found by testing against the zero date (which incidentally is 12/30/1899 in Access) which is what I think you were trying to do.

Enter the following in the query designer and it should generate the same Where clause in SQL view that I posted above.

In the criteria for the datefield put:
>= Nz([Forms]![FRM_SearchMulti]![CreatedDate])

(The Nz might be required to avoid it spitting the dummy when empty but it might be OK without it. I use VBA a lot and it is very fussy so I make a habit of Nz.)

Add the reference to the control on the form in the Field cell of another column:
[Forms]!FRM_SearchMulti]![CreatedDate]

Then in this column in a different Criteria row from the other criteria:
Is Null

Consequently all records are returned when the control is empty because there is no criteria on the field.

 

[steve_4477]

It works, thanks for the input! I have a new problem now though; I'm out of criteria rows because I had to use double the rows since I am using every other line. Any ideas on how to use more criteria without going through SQL?

 

[steve_4477]

Nevermind- simple fix. Just figured out how to add rows. Thx again for the help!

Steve

 

[Brianwarnock]

If Galaxiom's method is causing the proliferation of criteria rows use this technique instead, it looks odd in SQL view but simplifies the DSG view.

Field: [FieldName]=>[Forms]![FormName]![ControlName] or [Forms]![FormName]![ControlName] Is Null

Show: uncheck

Criteria: True

Brian