Incorrect years being returned on action query


T

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.
 
Ad

Advertisements

D

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.
 
T

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?


Duane Hookom said:
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


Tim Castro said:
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.
 
Ad

Advertisements

D

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


Tim Castro said:
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?


Duane Hookom said:
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


Tim Castro said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top