Parameter date format


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
 
Ad

Advertisements

K

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

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


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
 
F

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

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

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

KARL DEWEY

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

Stephanie

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
 
Ad

Advertisements

S

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

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

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

Advertisements

K

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


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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top