Access Query Writing

[Guest]

I am trying to write a query that will prompt the user for a date then match
the entered date to one of three dates in a table and then return the date
and other info from a table. I initialy wrote a Switch query as follows-
Date:Switch([Enter Date]=[Date1],[Date1],[Enter Date]=[Date2],[Date2],[Enter
Date]=[Date3],[Date3]) which I thought was fine until I realised that if
there are same dates in date 1 to 3 then you will only ever return date 1. I
know you could break it down into different fields and columns but is there a
way to return Dates 1 to 3 in the same column but different rows? I want to
keep them all in the same column so I can then base my report on it and I can
then just pull the one field.
I am using Access 2000.
Hope that makes some sense: any advice would be much appreciated.

 

[Jeff Boyce]

"Date" is a reserved word in Access -- change the name of your query field.

Are you saying that the date you seek ([Enter date]) could be in any of
three different fields? I believe you'd need to check in all three fields
(you could use the same parameter for all three).

If you have the same date in field1 and field3 of a row, does it matter? It
sounded like you wanted to pick up other info from a row that holds an
[Enter date] in one (?or more) of the three fields...

Regards

Jeff Boyce
<Office/Access MVP>

 

[Guest]

Hi Jeff, thanks for taking the time to reply,
I think I need to split the expression into three columns one for each date
the only thing is when I base my report on it if any of the fields are null I
will have gaps in the report (but maybe I can format this to be otherwise).
Again thanks very much for your help.

Regards

Mr Vlek

"Date" is a reserved word in Access -- change the name of your query field.

Are you saying that the date you seek ([Enter date]) could be in any of
three different fields? I believe you'd need to check in all three fields
(you could use the same parameter for all three).

If you have the same date in field1 and field3 of a row, does it matter? It
sounded like you wanted to pick up other info from a row that holds an
[Enter date] in one (?or more) of the three fields...

Regards

Jeff Boyce
<Office/Access MVP>

I am trying to write a query that will prompt the user for a date then
match
the entered date to one of three dates in a table and then return the date
and other info from a table. I initialy wrote a Switch query as follows-
Date:Switch([Enter Date]=[Date1],[Date1],[Enter
Date]=[Date2],[Date2],[Enter
Date]=[Date3],[Date3]) which I thought was fine until I realised that if
there are same dates in date 1 to 3 then you will only ever return date 1.
I
know you could break it down into different fields and columns but is
there a
way to return Dates 1 to 3 in the same column but different rows? I want
to
keep them all in the same column so I can then base my report on it and I
can
then just pull the one field.
I am using Access 2000.
Hope that makes some sense: any advice would be much appreciated.