Parameter query with date field

[Bethany via AccessMonster.com]

I am trying to create a parameter query where the user will enter a begin
date and an end date. When I run the query with the dates hard coded
(between #05/01/2005# and #05/31/2005#), I get the correct results. When I
run the query as a parameter query, I get result records in the correct
month, day range, but I'm getting records from prior year even though the
year is used as part of the date. The field used as criteria
(qry_monthly_comm_detail.Pol_effdt) is a CDATE so from everything I
understand date is the only consideration when referring to the criteria
entered for the query. Can someone look at this and tell me what I am doing
wrong? Thank you so much for your help.

SELECT qry_monthly_comm_detail.Premium, qry_monthly_comm_detail.[Pol Num],
qry_monthly_comm_detail.Nrcode, qry_monthly_comm_detail.Pol_effdt
FROM compare_gl_commission_missing_fromGL INNER JOIN
qry_monthly_comm_detail ON
compare_gl_commission_missing_fromGL.Policy_number =
qry_monthly_comm_detail.[Pol Num]
WHERE ((qry_monthly_comm_detail.Pol_effdt) Between #5/1/2005# And
#5/31/2005#)


SELECT qry_monthly_comm_detail.Premium, qry_monthly_comm_detail.[Pol Num],
qry_monthly_comm_detail.Nrcode, qry_monthly_comm_detail.Pol_effdt
FROM compare_gl_commission_missing_fromGL INNER JOIN
qry_monthly_comm_detail ON
compare_gl_commission_missing_fromGL.Policy_number =
qry_monthly_comm_detail.[Pol Num]
WHERE ((qry_monthly_comm_detail.Pol_effdt) Between [type month begin date]
And [type month end date])

 

[Duane Hookom]

What do you mean by "is a CDATE"? When you view the query in datasheet view,
is your Pol_effdt aligned to the left or right? If left, then it is being
treated like text.

 

[Bethany via AccessMonster.com]

I'm taking a text field and querying it as a CDATE so that access formats
it as a date. The data in the result set aligns to the right, so it appears
to be in date format.

 

[Duane Hookom]

Try add [type month begin date] And [type month end date] to your
Query->Parameters and set their data types to Date/Time.

 

[Bethany via AccessMonster.com]

I'm not sure what you mean, realistically it's the query with CDATE that I
need to fix and then all queries after that will have the data in the
correct format. Here is my syntax for that query:

SELECT Monthly_comm_detail.[Pol Num], Monthly_comm_detail.Poleffdt,
Monthly_comm_detail.Txneffdt, Monthly_comm_detail.Nrcode,
Monthly_comm_detail.Premium, Monthly_comm_detail.Commission,
CDate([poleffdt]) AS Pol_effdt, CDate([txneffdt]) AS Txn_effdt
FROM Monthly_comm_detail;

If I hardcode the pol_effdt criteria (between 5/1/05 and 5/31/05), I get
the expected results; however if I insert parameters
Between [Type the Beginning Date:] And [Type the End Date:]
I get results for transactions out of the date range (5/1/2004), but
noteably all of them had months greater than 5.

It seems that access is treating the dates as numbers when I have it as a
parameter query and when the dates are hardcoded the dates are treated as
dates.

 

[Bethany via AccessMonster.com]

Pardon my note about the months greater than 5, that was when I was trying
it using greater than.

 

[Duane Hookom]

Select Query->Parameters and enter your parameters and their data types.

 

[Bethany via AccessMonster.com]

Ok, I found that. Thanks, I didn't realize that was there. Can I get a
little more detailed help on how to use it in this specific instance? What
do I input into the 'parameter' list after selecting it from the menu. I
tried inputting the field name (pol_effdt) where I need to set the
criteria, but this did not return correct data. I also tried the above step
plus using the same prompts that I had previously. I guess I don't
understand how that 'parameter' list functions. I haven't been able to find
anything about it in access help.

 

[Bethany via AccessMonster.com]

I finally figured it out! Why is there no information in the help section
about this? It begins to tell you about creating parameter queries, but
says nothing about the parameter list from the query menu.

Thank you for your help!!