Parameter date format

[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

 

[Ken Snell [MVP]]

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\#")

 

[Stephanie]

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

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

 

[fredg]

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

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

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

 

[KARL DEWEY]

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

 

[Stephanie]

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

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

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

 

[Stephanie]

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

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

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

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

 

[KARL DEWEY]

It converts text to a date without Access making a guess as to your intentions.

There are others-
Cint - integer
CDbl - number double

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

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

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