Between Parameter Date and Infinitive Date

[valterduarte]

Hello!

I want to make a query that searches between Parameter Date A (PDA) and Parameter Date B (PDB).

However, I want to leave the option to leave one of the parameters empty, and the query should run it either to minus infinitive or plus infinitive.

Since I don't know how to run the infinitive, I've inserted a "safe" date:

Between IIF([PDA] is Null;1-1-1000;[PDA]) AND IIF([PDB] is Null;1-1-3000;[PDB])

This works fine in this cases:
PDA: 1-1-1000 PDB: 1-1-3000

PDA: empty PDB: 1-1-3000

However, when I put PDB empty like this:
PDA: 1-1-1000 PDB: empty

...It returns no records!!

What is wrong?

Thank you!

 

[valterduarte]

I have no idea why but changing to:

Between IIF([PDA] is Null;#1-1-1000#;[PDA]) AND IIF([PDB] is Null;#1-1-3000#;[PDB])

...works just fine!

What does the # do?

 

[SkipVought]

Hi,

# CONVERTS a string in a Date Format to a DateSerial Value

Skip,
[sub]
[red]Be Advised![/red] The Vinyards of Texas have produced a wine with diuretic dimishment and urethric relief...
Pinot More![/sub]