Null Values in Query Parameter (1 Viewer)

[Becca]

Hi

I am using a forms to enter query parameters for a report selection.

The criteria I have in my query is:
Between ([forms]![financialstatementcriteria]![beginningyear]) And ([forms]![financialstatementcriteria]![endingyear]) Or Is Null

What I was hoping it would do is limit the selection to those years falling between the selection, but if no beginning year or ending year is selected, to bring up all records in the report.

When the combo boxes are completed, I am able to bring up the correct records, however, I currently am getting no records if the combo boxes are left null.

Can anyone help me here?

Thanks

 

[Ally]

Instead of "Or IsNull" try

Between ... And IIf(IsNull([forms]![financialstatementcriteria]![endingyear]),Date(),[forms]![financialstatementcriteria]![endingyear])

This will enter Date() (today's date) in EndingYear if left blank. If you wanted to leave the first one blank as well, you'd do the same IIf Statement, but instead of Date() enter something like "*", but not entirely sure. Alternatively, you could check what the first date ever entered was and enter #01/01/88# instead of Date().

 

[Becca]

Ally

Thanks. It seems to be working now, thanks to your tip. Will see if it holds up in UAC.

Muchos grovelling thanks.

Bec

 

[Becca]

Blugghhh.. the formula worked perfectly for several fields... but for some reason the last parameter expression is just not working.. it keeps telling me my expression is too complicated
This is what I have, it is identical to all the other expressions in the query which are exactly the same on the same form

Between (IIf(IsNull([forms]![financialstatementcriteria]![FromTRansactionNo]),"1",[forms]![financialstatementcriteria]![FromTransactionNo])) And (IIf(IsNull([forms]![financialstatementcriteria]![ToTransactionNo]),"9999999999",[forms]![financialstatementcriteria]![ToTransactionNo]))

It should work, as the basic formula is used elsewhere in the query for the same form. Could it be that the fact that the Transaction Number feild with the criteria above is an autonumber field.. could this be screwing it up? any assistance would be much appreciated.

 

[Jon K]

You have a data type mismatch problem here.

The Transaction Number is numeric, but the "1" and "9999999999" with the quotes are characters.

Delete the quotes to make them numeric.

 

[Becca]

So simple.. now I feel dumb

Thanks for your help