Date Format problem in parameter query

[aaronk23]

i have a simple query that references a text box on a form as a
parameter. the text box holds a date and uses the Short Date format for
its Format property...nothing fancy

when i try to use it in my query it returns no records (when i know it
should). the really weird part is the date/value that returns from the
text box is a wingding character or a box (looks like a 'hard return'
character).

if i turn off the formatting, it returns the value in the text box.

i have tested this on several computers w/ the same results. what's
more, i have done this sort of thing many times in other db's and never
seen this before.

 

[Allen Browne]

Aaron, it sounds like Access is confused about the data type of the field.

1. Are you applying this on a calculated field?
- Yes: wrap the calculation in CVDate()
- No: when you open your table in design view, what data type is this field?

2. Declare the parameter, so Access understands it correctlly.
In query design view, choose Parameters on the Query menu.
In the dialog, enter something like this:
[Forms].[Form1].[Text0] Date/Time

If that does not solve the problem, post the SQL statement from your query
(SQL View, on View menu.)

 

[aaronk23]

allen, thanks for the reply. this is an unbound text box that holds a
date as criteria for a query.

it seems that #1 does not apply to this situation. i will give #2 a try
as soon as i get back into the office.

i wish i could post a screen shot of this funky situation. i have been
working w/ access rather intensley for 5+ years and have never seen
this before.

although i can post the sql, it is NOT at all complex. the query is
basically the primary key field and a date field (whose criteria comes
from the unbound text box in question). when i moved the reference to
the unbound text box from the criteria section up to the field section
(so the contents of the unbound text box are displayed in a calc'd
field in the query recordset) i get the wingding-looking-thing; but
ONLY in that calc'd field...the primary key/date fields look
fine...thus NOT a font issue.

i have done a MS Office Detect/Repair thing to see if the installation
was damaged....that did NOT help.

(is there a way to be notified by email when/if you reply to this
message?)

again, thanks

Aaron, it sounds like Access is confused about the data type of the field.

1. Are you applying this on a calculated field?
- Yes: wrap the calculation in CVDate()
- No: when you open your table in design view, what data type is this field?

2. Declare the parameter, so Access understands it correctlly.
In query design view, choose Parameters on the Query menu.
In the dialog, enter something like this:
[Forms].[Form1].[Text0] Date/Time

If that does not solve the problem, post the SQL statement from your query
(SQL View, on View menu.)


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

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

i have a simple query that references a text box on a form as a
parameter. the text box holds a date and uses the Short Date format for
its Format property...nothing fancy

when i try to use it in my query it returns no records (when i know it
should). the really weird part is the date/value that returns from the
text box is a wingding character or a box (looks like a 'hard return'
character).

if i turn off the formatting, it returns the value in the text box.

i have tested this on several computers w/ the same results. what's
more, i have done this sort of thing many times in other db's and never
seen this before.