Between/And in query parameter for Date that can be blank

[Diana van den Berg]

I would like to be able to edit the following to include no date entered at all in a query parameter for a report.

Between IIf([START INVOICE DATE or leave blank for 1 January of current year] Is Null,DateSerial(Year(Date()),1,1),[START INVOICE DATE or leave blank for 1 January of current year]) And IIf([END INVOICE DATE or leave blank for Current Date] Is Null,Date(),[END INVOICE DATE or leave blank for Current Date])

Thank you for any help.

[HansV]

Do you ALWAYS want to include records for which the date is empty? If so, use

Is Null OR Between IIf([START INVOICE DATE or leave blank for 1 January of current year] Is Null,DateSerial(Year(Date()),1,1),[START INVOICE DATE or leave blank for 1 January of current year]) And IIf([END INVOICE DATE or leave blank for Current Date] Is Null,Date(),[END INVOICE DATE or leave blank for Current Date])

[Diana van den Berg]

Thank you for your reply, Hans. However, I tried that and it wasn't what I am looking for. It brought up only the Invoice Date that was blank. I would like to be able to click Enter on both the START date and the END date and get ALL the dates - those that have been entered (within the 1 January of the current year and the current date) and those that haven't.

SORRY! Disregard what I said above. I tried it on its own and it worked. There must be something else wrong with another of my parameters in the query. I will go back and see if I can find it.

Thank you, Hans.

[HansV]

Does this do what you want?

Between Nz([START INVOICE DATE or leave blank for 1 January of current year],DateSerial(Year(Date()),1,1)) And Nz([END INVOICE DATE or leave blank for Current Date],Date()) Or [START INVOICE DATE or leave blank for 1 January of current year] Is Null And [END INVOICE DATE or leave blank for Current Date] Is Null

[Diana van den Berg]

I think we must have cross-posted, Hans! I tried to edit my previous reply very quickly before you could see it, but I didn't make it!

What you gave me first, was perfect! I just had another query parameter that was wrong. However, I have found what was wrong with the other one and edited it. Thank you very much. In my hurry to reply to you now, I glanced very quickly at what you had put in there and I remember seeing the Nz which I should have thought of myself, but I didn't. However, it is nice to have both! So thank you, yet again, Hans!

[Diana van den Berg]

Having looked at your previous reply, it isn't what I knew, so that is 2 great query parameters you have given me and I will keep for future use of the second one (am using the first one in my current database). Thank you!