If then statement in reports

[greenner]

I have a simple database tracking a vacation schedule. All I have is 3 tables and so far 1 form other then the switchboard. In the main table I have leaving date and returning date fields. Basicly I want a report that only shows who is currently on vacation.


Thank You
Josh

[Killer42]

I have a simple database tracking a vacation schedule. All I have is 3 tables and so far 1 form other then the switchboard. In the main table I have leaving date and returning date fields. Basicly I want a report that only shows who is currently on vacation.

That should be pretty simple. Just design your report, and for the data source give it something like the following query

SELECT * FROM Table1
  WHERE ([Leaving] <= Date() AND [Returning] >= Date())

You might even get away with something like

SELECT * FROM Table1 WHERE (Date Between [Leaving] AND [Returning])

I can never remember whether you need the parentheses on the Date function, sorry.

[NeoPa]

You might even get away with something like :

SELECT * FROM Table1 WHERE (Date Between [Leaving] AND [Returning])

You should certainly be looking to use this format.

I can never remember whether you need the parentheses on the Date function, sorry.

Generally not. I always advise to use them, however, as it gives a visual clue to a code reader that you are referring to a function call rather than a variable or a field in your dataset.

[Killer42]

I can never remember whether you need the parentheses on the Date function, sorry.

Generally not. I always advise to use them, however, as it gives a visual clue to a code reader that you are referring to a function call rather than a variable or a field in your dataset.

Good advice - thanks.

[greenner]

Thank you guys for the advice, I will give it a try!!