How to filter on calculated dates

  • Thread starter Thread starter SimonMurphy
  • Start date Start date
S

SimonMurphy

What I am trying to acheive:
I have a date (InspDate) in a table. I want to write a query that selects
out records where the InspDate+3 years fall into a user defined start/end
date period.

What I have attempted so far:
1. I have written a query with a calculated field NextInsp:
DateAdd("yyyy",3,[InspDate]).
2. I have written a second query that uses the first query as its source and
placed NextInsp on the 2nd query. Then specified the selection criteria as
"Between [Enter Start Date:] And [Enter End date:]"
3. When I run the 2nd query and enter the start/end dates I get no records
selected although I have 1 record setup to fall between the start/end dates.
4. As a test I have removed the BETWEEN selection and the query returns all
the records from which I can see the InspDate and NextInsp fields with the
correct data.

Am I missing something here? I thought I was being pretty logical in my
thought processes.
 
Why second query?
Just use Between CVDate([Enter Start Date:]) And CVDate([Enter End
date:]) on the calculated date.
 
Hi Karl
I used the 2nd query because I originally had the "Between" in the first
query and got no records so I thought that maybe Access had not calculated
the field before it did the selection. Hence I tried a different approach.
Your solution has worked fine thank you.
For my own knowledge why could I not just use [Enter Start Date:]? Why does
it have to be enclosed in CVDate?
Many thanks again for your help.

KARL DEWEY said:
Why second query?
Just use Between CVDate([Enter Start Date:]) And CVDate([Enter End
date:]) on the calculated date.

SimonMurphy said:
What I am trying to acheive:
I have a date (InspDate) in a table. I want to write a query that selects
out records where the InspDate+3 years fall into a user defined start/end
date period.

What I have attempted so far:
1. I have written a query with a calculated field NextInsp:
DateAdd("yyyy",3,[InspDate]).
2. I have written a second query that uses the first query as its source and
placed NextInsp on the 2nd query. Then specified the selection criteria as
"Between [Enter Start Date:] And [Enter End date:]"
3. When I run the 2nd query and enter the start/end dates I get no records
selected although I have 1 record setup to fall between the start/end dates.
4. As a test I have removed the BETWEEN selection and the query returns all
the records from which I can see the InspDate and NextInsp fields with the
correct data.

Am I missing something here? I thought I was being pretty logical in my
thought processes.
Click to expand...
Click to expand...
 
I converts the text string to a date without Access guessing that it is.
You could also declare your perameters as a date.

SimonMurphy said:
Hi Karl
I used the 2nd query because I originally had the "Between" in the first
query and got no records so I thought that maybe Access had not calculated
the field before it did the selection. Hence I tried a different approach.
Your solution has worked fine thank you.
For my own knowledge why could I not just use [Enter Start Date:]? Why does
it have to be enclosed in CVDate?
Many thanks again for your help.

KARL DEWEY said:
Why second query?
Just use Between CVDate([Enter Start Date:]) And CVDate([Enter End
date:]) on the calculated date.

SimonMurphy said:
What I am trying to acheive:
I have a date (InspDate) in a table. I want to write a query that selects
out records where the InspDate+3 years fall into a user defined start/end
date period.

What I have attempted so far:
1. I have written a query with a calculated field NextInsp:
DateAdd("yyyy",3,[InspDate]).
2. I have written a second query that uses the first query as its source and
placed NextInsp on the 2nd query. Then specified the selection criteria as
"Between [Enter Start Date:] And [Enter End date:]"
3. When I run the 2nd query and enter the start/end dates I get no records
selected although I have 1 record setup to fall between the start/end dates.
4. As a test I have removed the BETWEEN selection and the query returns all
the records from which I can see the InspDate and NextInsp fields with the
correct data.

Am I missing something here? I thought I was being pretty logical in my
thought processes.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top