using wildcard with BETWEEN..AND operator 1

[onusonu]

I'm trying to use wildcard with BETWEEN..AND statement.
I want to have an option to typ in the 1st and 2nd to retreive a range of date or nothing to get all the record or provide only the 1st date that will return all data with date equal to or greater than the 1st date.

Here is my statement, which doesn't work:

BETWEEN [1st date] & "*" AND [2nd Date] & "*"

What am I doing wrong?
PJ

 

[PHV]

Wildcards are for the Like operator, dot.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dhookom]

Try:
BETWEEN Nz([1st date],#1/1/1900#) AND Nz([2nd Date],#12/31/3000#)

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

To cover the table Null date field issue:
(>=[1st date] Or [1st date] Is Null) AND (<=[2nd date] Or [2nd date] Is Null)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rac2]

...
WHERE
      ( IsEmpty( [1st date] ) AND IsEmpty( [2nd date] ) )
      OR
      (IsEmpty( [2nd date] ) AND MyTable.dateOfInterest > [1st date] )
      OR
      ( NOT IsEmpty( [1st date] ) AND NOT IsEmpty( [2nd date] ) AND MyTable.dateOfInterest BETWEEN [1st date] AND [2nd date])

 

[dhookom]

Since we are on a roll, I can create that criteria in 65 characters:

BETWEEN Nz([1st date],[DateField]) AND Nz([2nd Date],[DateField])

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

Hopefully no null date value in the table.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dhookom]

PH,
True, good point. I assumed from earlier postings there would be a date value in each record.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

I agree it seems unlikely.

 

[onusonu]

Thank you all,
Very helpful!