date field on forms

[George Applegate]

I am having a problem with dates stored as text and also on forms.

First, some background:

I have some queries that include only certain records based on a date
range of records:

I have a DATEWORK table that has a begin date and and end date in DATE
format. Now, I have some records in an PAYLOG table that are
populated automatically from a time-clock in mm/dd/yyyy format, but
they are TEXT and I cannot change that. I have to live with that.

I have some queries that I use to select records in the PAYLOG based
on the begin date and end date in the DATEWORK table. I got that to
work fine by creating a work field in my query as Datevalue([logdate])
and then > begindate and < enddate. The datevalue puts the text
version of the date into a true date so it selects based on the range
- which is a problem when it goes from 12/29/2008 to 01/05/2009, if
that make sense.

PROBLEM:
However, now I've run into a problem with forms. I have a form that I
allow the user to enter a "start date" and "end date" for the range.
The problem is, I think that is a "text date" also, since it's in a
text box??? For instance, if the user enters "12/28/2008" and
"01/05/2008", on the form, it doesn't seem to work. How do I change
the FORM start date and end date to be considered DATE Fields, or is
that possible.

Once they are date fields, then I can use the datevalue([logdate]) and
I would say the logdate has to be > form!startdate and <
form!enddate???

Any suggestions or ideas?

Thanks,
ga

George Applegate
(e-mail address removed)

 

[Rick Brandt]

I am having a problem with dates stored as text and also on forms.

First, some background:

I have some queries that include only certain records based on a date
range of records:

I have a DATEWORK table that has a begin date and and end date in DATE
format. Now, I have some records in an PAYLOG table that are populated
automatically from a time-clock in mm/dd/yyyy format, but they are TEXT
and I cannot change that. I have to live with that.

I have some queries that I use to select records in the PAYLOG based on
the begin date and end date in the DATEWORK table. I got that to work
fine by creating a work field in my query as Datevalue([logdate]) and
then > begindate and < enddate. The datevalue puts the text version of
the date into a true date so it selects based on the range - which is a
problem when it goes from 12/29/2008 to 01/05/2009, if that make sense.

PROBLEM:
However, now I've run into a problem with forms. I have a form that I
allow the user to enter a "start date" and "end date" for the range. The
problem is, I think that is a "text date" also, since it's in a text
box??? For instance, if the user enters "12/28/2008" and "01/05/2008",
on the form, it doesn't seem to work. How do I change the FORM start
date and end date to be considered DATE Fields, or is that possible.

Once they are date fields, then I can use the datevalue([logdate]) and I
would say the logdate has to be > form!startdate and < form!enddate???

Any suggestions or ideas?

By default an Access query will make a "best guess" as to the DataType of
parameters. You can however, open the parameters dialog while in query
design view and explicitly indicate the DataTypes. In your case you need
to set the form reference parameters to DateTime.