Query with Between Dates and If Null - All Records

[Kevo]

I have a query that takes End Date and Start Date from a form and returns records between them.....that works fine



I have looked all over and cant find out how to either prompt for user to have to enter dates....or....better yet, return all records if no dates selected.....

Any ideas?

Thank You

[June7]

Try this as parameter:

BETWEEN Nz(Forms!formname!StartDate,#1/1/1900#) AND Nz(Forms!formname!EndDate,#12/31/2900#)

[Kevo]

Try this as parameter:

BETWEEN Nz(Forms!formname!StartDate,#1/1/1900#) AND Nz(Forms!formname!EndDate,#12/31/2900#)

Tried but doesn't seem to work....

Here is the sql view of my query:

SELECT tblLoad.LoadID, tblLoad.LoadDate, tblLoad.AutoclaveID, tblLoad.BI, tblLoad.[Process Challenge Device], tblLoad.[Cycle Complete], tblLoad.[Cycle Aborted]
FROM tblLoad
WHERE (((tblLoad.LoadDate) Between Nz([Forms]![frmSearchMenu]![qLoadDate1],#1/1/1900#) And Nz([Forms]![frmSearchMenu]![qLoadDate2],#12/31/2900#)));


Any other ideas? I wouldn't mind if msgbox popped up if user doesnt enter date and prompt to do so....tried that but not sure how...

[alansidman]

Tried but doesn't seem to work....

What does this mean? Do you get any results?
Are your date fields formatted as dates in your table?

[Kevo]

What does this mean? Do you get any results?
Are your date fields formatted as dates in your table?[/COLOR]

I was answering June7 regarding his suggestion....

[June7]

Same questions. What happens - error message, wrong results, nothing? Is the field date type?

This criteria has worked.

[Kevo]

Same questions. What happens - error message, wrong results, nothing? Is the field date type?

This criteria has worked.

I tried:
WHERE (((tblLoad.LoadDate) Between [Forms]![frmSearchMenu].[qLoadDate1] And [Forms]![frmSearchMenu].[qLoadDate2]));

and I get this error (if I dont enter a start and end date):

This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.

If I enter a start and end date, it works as desired.

I want it to enter Between the dates if entered or ALL records if no date is entered. Is this possible? If not, how do I prompt the user to enter the dates?

And Yes, the fields are dates.

[Kevo]

Well, I gave up on getting it to return all records if Between Dates was not entered......so I put this in the on click event for the "search" button that runs the query:
If Me.qLoadDate1.Value = "" Then
MsgBox "Please enter start date", vbOKOnly
Me.qLoadDate1.SetFocus
Else
If Me.qLoadDate2.Value = "" Then
MsgBox "Please enter End Date", vbOKOnly
Me.qLoadDate2.SetFocus
Else

Dim stDocName As String
stDocName = "rptLoadSearch"

DoCmd.OpenReport stDocName, acViewPreview
End If
End If

[June7]

Too bad. I just tested that criteria again and it worked.

Is null value allowed in that field? That can be an issue with parameterized queries but can be dealt with.