Criteria does not return expected results

[Scott B]

Greetings,

I put this critereia - Between [Type a beginning Departure Date] And [Type
an Ending Departure Date] - in a calculated query field. Here is the
ield - DepartDate: ([StayStart]+[StayLength]). StayStart is a date field
and StayLength is a numeric field. The calculation gives me the number of
days a guest stays at our B&B. When I view the results of the query I get
date results that do not conform to the answers to the criteria box. If I
type in 6/1/05 as the beginning date and 6/30/05 as the ending date the
query returns all years of 6/1 - 6/30 (i.e 6/11/02, 6/15/03, etc). But does
stick to 6/1 - 6/30.

Is there a limitation of the criteria that I do not know? Something about
the calculations?

Thaks in advance.

Best regards,
Scott B

 

[Allen Browne]

Hi Scott

Access (JET) is not good at guessing the intended data type of calculated
fields, parameters, and unbound controls. You need to help it out by
explicitly typecasting.

Firstly, change your calculated field to:
DepartDate: CVDate([StayStart]+[StayLength])
Now Access knows the field is a date.

Next, declare the two parameters.
Choose Parameters on the Query menu.
Enter 2 rows into the dialog, and specify they are both dates:
[Type a beginning Departure Date] Date/Time
[Type an Ending Departure Date] Date/Time

That should solve it for you.

More info:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

 

[Scott B]

Allen,

Thank you! Worked great.

As always, I appreciate all the work and thought all of you in these forums
do.

Scott B

Hi Scott

Access (JET) is not good at guessing the intended data type of calculated
fields, parameters, and unbound controls. You need to help it out by
explicitly typecasting.

Firstly, change your calculated field to:
DepartDate: CVDate([StayStart]+[StayLength])
Now Access knows the field is a date.

Next, declare the two parameters.
Choose Parameters on the Query menu.
Enter 2 rows into the dialog, and specify they are both dates:
[Type a beginning Departure Date] Date/Time
[Type an Ending Departure Date] Date/Time

That should solve it for you.

More info:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Greetings,

I put this critereia - Between [Type a beginning Departure Date] And
[Type an Ending Departure Date] - in a calculated query field. Here is
the ield - DepartDate: ([StayStart]+[StayLength]). StayStart is a date
field and StayLength is a numeric field. The calculation gives me the
number of days a guest stays at our B&B. When I view the results of the
query I get date results that do not conform to the answers to the
criteria box. If I type in 6/1/05 as the beginning date and 6/30/05 as
the ending date the query returns all years of 6/1 - 6/30 (i.e 6/11/02,
6/15/03, etc). But does stick to 6/1 - 6/30.

Is there a limitation of the criteria that I do not know? Something
about the calculations?

Thaks in advance.

Best regards,
Scott B