Incorrect years being returned on action query

[Tim Castro]

Access 2000 (Yes, I know. Old.)

I am designing a database to help me manage a research study. Subjects will
be baselined, that date will be entered into Access, and the program will
generate a Follow-Up Interview date for 180 days after Baseline. Our
interviewers will go into the database and use an action query to prompt them
of upcoming Follow-Up interviews.

The Baseline Dates are in a Table and formatted as Short Dates. A Query
takes those dates and generates the Follow-Up Interview Date:

Follow-Up: DateAdd("d",180,[Baseline])

Then an action query takes "Follow-Up" and runs "Between [Begin Date] And
[End Date]" as a Criteria. However, I'm getting incorrect output from the
query. Specifically, if I run the action query for dates between 1/1/2008
and 2/1/2008, the output will contain 1/15/2008 (which is correct), but will
also contain 1/20/2005 (where the year is totally out of range). The month
and day are fine, but Access is totally disregarding the year.

Any ideas on how to fix this? This has been frustrating me for several days.

 

[Duane Hookom]

Are your dates actually dates or are they text? If they are dates, where are
the criteria values coming from? If parameter prompts consider abandoning
them in favor of references to controls on forms.

Then use Query->Parameters to set the data types of all dynamic criteria
values.

 

[Tim Castro]

The dates start in a Table where the Data Type is "Date/Time" and are
formatted as "Short Date". Query #1 takes those Baseline dates and generates
Follow-up dates by adding 180 days to the Baseline dates with the
"DateAdd("d",180[Baseline])". Query #1 generates the Follow-up dates
correctly and those dates are also formatted as "Short Date".

Query #2 queries on the Follow-up dates in Query #1 with "Between [begin]
and [end]". This is the query which is delivering the out of range years in
the output. I believe all my dates remain formatted as dates and not as text.

I'm sorry. I don't understand what "If parameter prompts consider
abandoning them in favor of references to controls on forms" means. Is there
a word missing in that sentence?

Are your dates actually dates or are they text? If they are dates, where are
the criteria values coming from? If parameter prompts consider abandoning
them in favor of references to controls on forms.

Then use Query->Parameters to set the data types of all dynamic criteria
values.
--
Duane Hookom
Microsoft Access MVP

Access 2000 (Yes, I know. Old.)

I am designing a database to help me manage a research study. Subjects will
be baselined, that date will be entered into Access, and the program will
generate a Follow-Up Interview date for 180 days after Baseline. Our
interviewers will go into the database and use an action query to prompt them
of upcoming Follow-Up interviews.

The Baseline Dates are in a Table and formatted as Short Dates. A Query
takes those dates and generates the Follow-Up Interview Date:

Follow-Up: DateAdd("d",180,[Baseline])

Then an action query takes "Follow-Up" and runs "Between [Begin Date] And
[End Date]" as a Criteria. However, I'm getting incorrect output from the
query. Specifically, if I run the action query for dates between 1/1/2008
and 2/1/2008, the output will contain 1/15/2008 (which is correct), but will
also contain 1/20/2005 (where the year is totally out of range). The month
and day are fine, but Access is totally disregarding the year.

Any ideas on how to fix this? This has been frustrating me for several days.

 

[Duane Hookom]

Regarding the parameter prompts, check out
http://www.tek-tips.com/faqs.cfm?fid=6763.

You should not be applying any formatting to your dates. They should always
appear right aligned in datasheet view.

From your query design, select Query->Parameters and enter your specific
parameters and their data type.
--
Duane Hookom
Microsoft Access MVP

The dates start in a Table where the Data Type is "Date/Time" and are
formatted as "Short Date". Query #1 takes those Baseline dates and generates
Follow-up dates by adding 180 days to the Baseline dates with the
"DateAdd("d",180[Baseline])". Query #1 generates the Follow-up dates
correctly and those dates are also formatted as "Short Date".

Query #2 queries on the Follow-up dates in Query #1 with "Between [begin]
and [end]". This is the query which is delivering the out of range years in
the output. I believe all my dates remain formatted as dates and not as text.

I'm sorry. I don't understand what "If parameter prompts consider
abandoning them in favor of references to controls on forms" means. Is there
a word missing in that sentence?

Are your dates actually dates or are they text? If they are dates, where are
the criteria values coming from? If parameter prompts consider abandoning
them in favor of references to controls on forms.

Then use Query->Parameters to set the data types of all dynamic criteria
values.
--
Duane Hookom
Microsoft Access MVP

Access 2000 (Yes, I know. Old.)

I am designing a database to help me manage a research study. Subjects will
be baselined, that date will be entered into Access, and the program will
generate a Follow-Up Interview date for 180 days after Baseline. Our
interviewers will go into the database and use an action query to prompt them
of upcoming Follow-Up interviews.

The Baseline Dates are in a Table and formatted as Short Dates. A Query
takes those dates and generates the Follow-Up Interview Date:

Follow-Up: DateAdd("d",180,[Baseline])

Then an action query takes "Follow-Up" and runs "Between [Begin Date] And
[End Date]" as a Criteria. However, I'm getting incorrect output from the
query. Specifically, if I run the action query for dates between 1/1/2008
and 2/1/2008, the output will contain 1/15/2008 (which is correct), but will
also contain 1/20/2005 (where the year is totally out of range). The month
and day are fine, but Access is totally disregarding the year.

Any ideas on how to fix this? This has been frustrating me for several days.