Parameter date format

  • Thread starter Thread starter Stephanie
  • Start date Start date
S

Stephanie

Hi. After much trial and error (mostly error!) I think I might know what the
issue is but not quite how to fix it.

I have parameters [StartDate] and [EndDate], in the form of 01/01/2009 and
06/05/2009 which I'm using as

[LoggedDate] BETWEEN [forms]![frquick]![StartDate] AND
[forms]![frquick]![EndDate]

I have 3 items with a [LoggedDate] of 3/25/2009, 3/26/2009, and 4/7/2009.
The two items from March pull into the query just fine from the parameter,
but the April item will not pull in if I have the parameter on the
[LoggedDate].

Thoughts? I appreciate your help!

Cheers,
Stephanie
 
Set the Format property of the [StartDate] and [EndDate] textboxes on your
form to "Short Date". That should let your query work correctly. (The
problem is that, without telling ACCESS that your textboxes have date values
in them, for ACCESS to know their dates instead of text strings.)

Or, you can change your query to this (which expressly tells ACCESS that
your values from the form's textboxes are dates):

[LoggedDate] BETWEEN Format([forms]![frquick]![StartDate],
"\#mm\/dd\/yyyy\#") AND
Format([forms]![frquick]![EndDate], "\#mm\/dd\/yyyy\#")
 
Ken,

Thanks for the reply. The parameter fields are ShortDate fields. So that
must not be it. The [LoggedDate] field is a Date/Time field, so I'm not sure
what the issue really is. Access didn't like the fancy format date parameter
statement.

Any thoughts?
Thanks,
Stephanie


Ken Snell said:
Set the Format property of the [StartDate] and [EndDate] textboxes on your
form to "Short Date". That should let your query work correctly. (The
problem is that, without telling ACCESS that your textboxes have date values
in them, for ACCESS to know their dates instead of text strings.)

Or, you can change your query to this (which expressly tells ACCESS that
your values from the form's textboxes are dates):

[LoggedDate] BETWEEN Format([forms]![frquick]![StartDate],
"\#mm\/dd\/yyyy\#") AND
Format([forms]![frquick]![EndDate], "\#mm\/dd\/yyyy\#")
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



Stephanie said:
Hi. After much trial and error (mostly error!) I think I might know what
the
issue is but not quite how to fix it.

I have parameters [StartDate] and [EndDate], in the form of 01/01/2009 and
06/05/2009 which I'm using as

[LoggedDate] BETWEEN [forms]![frquick]![StartDate] AND
[forms]![frquick]![EndDate]

I have 3 items with a [LoggedDate] of 3/25/2009, 3/26/2009, and 4/7/2009.
The two items from March pull into the query just fine from the parameter,
but the April item will not pull in if I have the parameter on the
[LoggedDate].

Thoughts? I appreciate your help!

Cheers,
Stephanie
Click to expand...
Click to expand...
 
Ken,

Thanks for the reply. The parameter fields are ShortDate fields. So that
must not be it. The [LoggedDate] field is a Date/Time field, so I'm not sure
what the issue really is. Access didn't like the fancy format date parameter
statement.

Any thoughts?
Thanks,
Stephanie

Ken Snell said:
Set the Format property of the [StartDate] and [EndDate] textboxes on your
form to "Short Date". That should let your query work correctly. (The
problem is that, without telling ACCESS that your textboxes have date values
in them, for ACCESS to know their dates instead of text strings.)

Or, you can change your query to this (which expressly tells ACCESS that
your values from the form's textboxes are dates):

[LoggedDate] BETWEEN Format([forms]![frquick]![StartDate],
"\#mm\/dd\/yyyy\#") AND
Format([forms]![frquick]![EndDate], "\#mm\/dd\/yyyy\#")
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Stephanie said:
Hi. After much trial and error (mostly error!) I think I might know what
the
issue is but not quite how to fix it.

I have parameters [StartDate] and [EndDate], in the form of 01/01/2009 and
06/05/2009 which I'm using as

[LoggedDate] BETWEEN [forms]![frquick]![StartDate] AND
[forms]![frquick]![EndDate]

I have 3 items with a [LoggedDate] of 3/25/2009, 3/26/2009, and 4/7/2009.
The two items from March pull into the query just fine from the parameter,
but the April item will not pull in if I have the parameter on the
[LoggedDate].

Thoughts? I appreciate your help!

Cheers,
Stephanie
Click to expand...
Click to expand...
Click to expand...

Most likely your Date Field includes a Time value.
Therefore any data past 00:00:00 AM of the [EndDate] is not within the
Between [FromDate] And [EndDate] criteria.
Easiest work-around is to just add one day manually to the [EndDate]
when prompted, i.e. instead of entering 11/24/2005 enter 11/25/2005.

A better work-around would be to set
[FromDate]
and
[EndDate]
and their expected Datatype as query parameters in the Query Parameter
Dialog (Click Query + Parameter).
Then change your criteria to:
Between [FromDate] and ([EndDate] + 1)

The best solution, if the Time of day is not necessary, is to run an
Update Query to remove the time value from the date:

Update YourTable Set YourTable.[DateField] = DateValue([DateField]);

Then make sure the time is not included in any new entries, i.e. use
Date() instead of Now().
 
Try this --
[LoggedDate] BETWEEN CVDate([forms]![frquick]![StartDate]) AND
CVDate([forms]![frquick]![EndDate])
 
That worked- Thanks!
What is CVDate and why would that work?

KARL DEWEY said:
Try this --
[LoggedDate] BETWEEN CVDate([forms]![frquick]![StartDate]) AND
CVDate([forms]![frquick]![EndDate])


Stephanie said:
Hi. After much trial and error (mostly error!) I think I might know what the
issue is but not quite how to fix it.

I have parameters [StartDate] and [EndDate], in the form of 01/01/2009 and
06/05/2009 which I'm using as

[LoggedDate] BETWEEN [forms]![frquick]![StartDate] AND
[forms]![frquick]![EndDate]

I have 3 items with a [LoggedDate] of 3/25/2009, 3/26/2009, and 4/7/2009.
The two items from March pull into the query just fine from the parameter,
but the April item will not pull in if I have the parameter on the
[LoggedDate].

Thoughts? I appreciate your help!

Cheers,
Stephanie
Click to expand...
Click to expand...
 
Fred,

Thanks for all of the choices. I continaully feed in data that may/may not
overwrite exisiting data (based on a flag), so the 3rd solution didn't seem
an option. I did try Karl's solution and that seemed to work.

Cheers,
Stephanie

fredg said:
Ken,

Thanks for the reply. The parameter fields are ShortDate fields. So that
must not be it. The [LoggedDate] field is a Date/Time field, so I'm not sure
what the issue really is. Access didn't like the fancy format date parameter
statement.

Any thoughts?
Thanks,
Stephanie

Ken Snell said:
Set the Format property of the [StartDate] and [EndDate] textboxes on your
form to "Short Date". That should let your query work correctly. (The
problem is that, without telling ACCESS that your textboxes have date values
in them, for ACCESS to know their dates instead of text strings.)

Or, you can change your query to this (which expressly tells ACCESS that
your values from the form's textboxes are dates):

[LoggedDate] BETWEEN Format([forms]![frquick]![StartDate],
"\#mm\/dd\/yyyy\#") AND
Format([forms]![frquick]![EndDate], "\#mm\/dd\/yyyy\#")
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Hi. After much trial and error (mostly error!) I think I might know what
the
issue is but not quite how to fix it.

I have parameters [StartDate] and [EndDate], in the form of 01/01/2009 and
06/05/2009 which I'm using as

[LoggedDate] BETWEEN [forms]![frquick]![StartDate] AND
[forms]![frquick]![EndDate]

I have 3 items with a [LoggedDate] of 3/25/2009, 3/26/2009, and 4/7/2009.
The two items from March pull into the query just fine from the parameter,
but the April item will not pull in if I have the parameter on the
[LoggedDate].

Thoughts? I appreciate your help!

Cheers,
Stephanie
Click to expand...
Click to expand...

Most likely your Date Field includes a Time value.
Therefore any data past 00:00:00 AM of the [EndDate] is not within the
Between [FromDate] And [EndDate] criteria.
Easiest work-around is to just add one day manually to the [EndDate]
when prompted, i.e. instead of entering 11/24/2005 enter 11/25/2005.

A better work-around would be to set
[FromDate]
and
[EndDate]
and their expected Datatype as query parameters in the Query Parameter
Dialog (Click Query + Parameter).
Then change your criteria to:
Between [FromDate] and ([EndDate] + 1)

The best solution, if the Time of day is not necessary, is to run an
Update Query to remove the time value from the date:

Update YourTable Set YourTable.[DateField] = DateValue([DateField]);

Then make sure the time is not included in any new entries, i.e. use
Date() instead of Now().
Click to expand...
 
It converts text to a date without Access making a guess as to your intentions.

There are others-
Cint - integer
CDbl - number double


Stephanie said:
That worked- Thanks!
What is CVDate and why would that work?

KARL DEWEY said:
Try this --
[LoggedDate] BETWEEN CVDate([forms]![frquick]![StartDate]) AND
CVDate([forms]![frquick]![EndDate])


Stephanie said:
Hi. After much trial and error (mostly error!) I think I might know what the
issue is but not quite how to fix it.

I have parameters [StartDate] and [EndDate], in the form of 01/01/2009 and
06/05/2009 which I'm using as

[LoggedDate] BETWEEN [forms]![frquick]![StartDate] AND
[forms]![frquick]![EndDate]

I have 3 items with a [LoggedDate] of 3/25/2009, 3/26/2009, and 4/7/2009.
The two items from March pull into the query just fine from the parameter,
but the April item will not pull in if I have the parameter on the
[LoggedDate].

Thoughts? I appreciate your help!

Cheers,
Stephanie
Click to expand...
Click to expand...
Click to expand...
 
Back
Top