Using datevalue as parameter from a form

[accessNator]

In my QBE, I have a field call [startDate]

In my criteria, I am passing a parameter from a form.
Currently, I am using: DateValue([forms]![myForm]![txtStartDate])
From myFORM, the value of the text box is 3/1/2013

When I run the query, it runs as expected and returns all records with the 3/1/2013 dates.

But now I want to make it where if the parameter is null.

How do I change that criteria if that parameter I am passing is null from my form's textbox? I want it to return all records that have a null entry in the [startDate].

Thanks in advance.

 

[Brianwarnock]

If you are entering a date and not date and time then you do not need Datevalue, thus if the parameter is null it will look for null

Brian

Edit on second thoughts i think that is wrong, the null bit that is.

 

[Brianwarnock]

I can't test at the moment but you really need to pass Is Null in the parameter and if the control is formatted for a date I don't think that you can.

Brian

 

[accessNator]

I can't test at the moment but you really need to pass Is Null in the parameter and if the control is formatted for a date I don't think that you can.

Brian

How would you suggest I correct it in my query?
In my form control textbox its just a text date "3/1/2013"

Can you point to an example on how to modify this?
Thanks for your reply.

 

[Brianwarnock]

I assume that the date in the table is a proper date.

I would use a date formatted control in the form as checking is then done to avoid nonsense being entered, then you would not need to use Datevalue.

Personally I would have a second query to check for the date field in the table being null. This should be a one off as the input form should ensure that the date field cannot be null.

Brian

 

[accessNator]

I assume that the date in the table is a proper date.

I would use a date formatted control in the form as checking is then done to avoid nonsense being entered, then you would not need to use Datevalue.

I have revised the control so now I dont have to use the Datevalue in my QBE for the column startDate.

In the criteria field it now states: [forms]![myForm]![txtStartDate]

Personally I would have a second query to check for the date field in the table being null. This should be a one off as the input form should ensure that the date field cannot be null.

In my table, I do have records with null values in the [startDate] or does not equal the passed value of "3/1/2012"

How can I modify the criteria in my existing query called "qryCheckDates" using query def in VBA.

e.g. I will have a check box and if clicked, instead of looking at the current existing criteria using
[forms]![myForm]![txtStartDate],

I would like to change the query "qryCheckDates" to use this as the new criteria in the field [startDate]
<>[forms]![myForm]![txtStartDate] Or Is Null

Do you know of an example code?

Thanks again.