By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,968 Members | 1,681 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,968 IT Pros & Developers. It's quick & easy.

Simple query, difficulty with date

P: n/a
I have a form where a user enters a date that I will then base a query
on. In the past, I have simply placed Forms![Form_Name]![Field_Name]
into the criteria for a query and it runs as long as the form is open.
The recordset is then filtered based on what was entered in the form.
However, this strait forward approach seems to break when the form
value to be passed to a query is a date. I have a format and input
mask of ShortDate placed on the form. It is an unbound field. The
recordset always comes up empty because I'm guessing that it is reading
the date on the form as a string instead of a date.

How do I convert my form value to a date in the criteria selection for
a query so that it will run like any other selection criteria I have
handled this way?

Thanks!

Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
bc******@jeffco.k12.co.us wrote in
news:11**********************@g14g2000cwa.googlegr oups.com:
I have a form where a user enters a date that I will then base
a query on. In the past, I have simply placed
Forms![Form_Name]![Field_Name] into the criteria for a query
and it runs as long as the form is open. The recordset is then
filtered based on what was entered in the form. However, this
strait forward approach seems to break when the form value to
be passed to a query is a date. I have a format and input
mask of ShortDate placed on the form. It is an unbound field.
The recordset always comes up empty because I'm guessing that
it is reading the date on the form as a string instead of a
date.

How do I convert my form value to a date in the criteria
selection for a query so that it will run like any other
selection criteria I have handled this way?

Thanks!

Dates need to be delimited with # in the query criteria

e.g. "#"+Forms!myform!mycontrol + "#"
--
Bob Quintal

PA is y I've altered my email address.
Nov 13 '05 #2

P: n/a
I wish that was it... that was actually one of the first things I
tried. In going against an unbound field, I don't think I can avoid
converting the string to a date. When I based the form on an date
field from an underlying (dummy/test) table,
Forms![MyForm]![Field_Name] worked just fine (as I expected it to work
the first time). Therefore, at least that experiment proved that it
is a data type issue. A date format in an unbound text field is not by
recognized by access as a date even when put between two #'s.

I guess I'd better start writing my converts!...

Nov 13 '05 #3

P: n/a
<bc******@jeffco.k12.co.us> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...
I have a form where a user enters a date that I will then base a query
on. In the past, I have simply placed Forms![Form_Name]![Field_Name]
into the criteria for a query and it runs as long as the form is open.
The recordset is then filtered based on what was entered in the form.
However, this strait forward approach seems to break when the form
value to be passed to a query is a date. I have a format and input
mask of ShortDate placed on the form. It is an unbound field. The
recordset always comes up empty because I'm guessing that it is reading
the date on the form as a string instead of a date.

How do I convert my form value to a date in the criteria selection for
a query so that it will run like any other selection criteria I have
handled this way?


Parameters are (usually) resolved by Access on-the-fly and sometimes Access gets
it wrong. You can however open the parameters dialog in the query designer and
explicitly tell Access what DataTypes the parameters are. In your case you
would enter the parameter...

Forms![Form_Name]![Field_Name]

....with a DataType of DateTime.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.