date field on forms

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
gappleg8.DeleteThis@nospam.fmctc

On Sat, 27 Dec 2008 09:10:05 -0600, George Applegate wrote:

> 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.


--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com