Date format range

[Tony]

Hey guys,

I use Google Groups quite a bit as it is an enormous wealth of
information, and now I need some help. I have created a query using
parameters to capture a range of date, the date is also formatted.

test: Format([archive_date],"mm/dd/yy")

Between [date1:] And [date2:]

This is where it gets wonky, when I enter 05/01/04 for [date:1] and
05/31/04 for [date2:] it pulls the correct MM and DD but the YY is not
right. Basically I get all dates for the month of May, but it captures
it for 2001, 2002, 2003, and 2004. Can anyone help me pull the right
range?

[Rick Brandt]

Re: Date format range

"Tony" <anthony.hendra ta@ama.ab.ca> wrote in message
news:6d6b1436.0 408030936.1b188 81b@posting.goo gle.com...[color=blue]
> Hey guys,
>
> I use Google Groups quite a bit as it is an enormous wealth of
> information, and now I need some help. I have created a query using
> parameters to capture a range of date, the date is also formatted.
>
> test: Format([archive_date],"mm/dd/yy")[/color]

Problem here is that the Format() function is converting the date to a
string so your BETWEEN clause is going to be an alphabetical test instead
of a date test.

If you just want to strip off the time component and leave it as a date
(formatting doesn't matter in a date test) you can try to use DateValue()
instead of Format().

However; as a "best practice" one should avoid using criteria against an
expression, but instead apply a criteria to the raw field value. The
reason is that as soon as you use an expression (even a very simple one)
the database engine is forced to do a table scan instead of being able to
use an index on the field.

If the field having criteria applied to it doesn't have an index then it
wouldn't matter, but that would be another design flaw (IMO) as searched
and joined fields should always have an index.

A search criteria that could still use an index would look like...

WHERE [archive_date] BETWEEN [date 1] AND DateAdd("d", 1, [date 2])

Notice how in this case I have an expression in the criteria but the
criteria is applied against a raw field instead of a field wrapped in an
expression.


--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[fredg]

Re: Date format range

On 3 Aug 2004 10:36:47 -0700, Tony wrote:
[color=blue]
> Hey guys,
>
> I use Google Groups quite a bit as it is an enormous wealth of
> information, and now I need some help. I have created a query using
> parameters to capture a range of date, the date is also formatted.
>
> test: Format([archive_date],"mm/dd/yy")
>
> Between [date1:] And [date2:]
>
> This is where it gets wonky, when I enter 05/01/04 for [date:1] and
> 05/31/04 for [date2:] it pulls the correct MM and DD but the YY is not
> right. Basically I get all dates for the month of May, but it captures
> it for 2001, 2002, 2003, and 2004. Can anyone help me pull the right
> range?[/color]

That's strange, because I don't get ANY records returned using your
formatting and criteria, and you may be having other criteria problems
as well.

Once you format the field as you have, Access is going to treat the
[date1:] and [date2:] entries as text, not date/time.

To force a date/time value, open the query in Design View.
On the Menu, click on Query + Parameters
Enter
[date1:] Date/Time
[date2:] Date/Time

This should force the entries as date/time and return just the year
entered.

Of course you could just use a normal 4 digit year parameter entry and
avoid having to add that formatted 2 digit year column.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

[Tony]

Re: Date format range

Hey fredg,

I just have a question about this:
[color=blue]
> To force a date/time value, open the query in Design View.
> On the Menu, click on Query + Parameters
> Enter
> [date1:] Date/Time
> [date2:] Date/Time[/color]

What is the date format for this, I am looking for a parameter that is
in short date format? Thanks so much for the your effort!

[fredg]

Re: Date format range

On 5 Aug 2004 09:03:37 -0700, Tony wrote:
[color=blue]
> Hey fredg,
>
> I just have a question about this:
>[color=green]
>> To force a date/time value, open the query in Design View.
>> On the Menu, click on Query + Parameters
>> Enter
>> [date1:] Date/Time
>> [date2:] Date/Time[/color]
>
> What is the date format for this, I am looking for a parameter that is
> in short date format? Thanks so much for the your effort![/color]

I doesn't matter what the date format is.
Dates, in Access, are stored as a double number.
Today's date is 38204.

When you set the parameter as Date/Time, you can enter the parameter
in any valid date format, i.e. 7/1/04 or 7-1-04 or 7-1-2004 or July 1,
2004 and Access will return the correct dates.

You wouldn't need to explicitly set the parameter datatype (except in
a Crosstab query) if you weren't formatting the criteria column.
In other words, if you were using your [Archive_Date] column as
criteria, all you would need do is write, as parameter in that column:
Between [StartDate] and [EndDate].
Because [Archive_Date] is a Date datatype, Access can figure out the
entered parameter is a date, not text.

You should get used to using a 4 digit year. Access will interpret 2
digit years from 00 through 29 as 2000 to 2029. However a 2 digit year
of 30 through 99 will be interpreted as 1930 to 1999. At some point
you're going to have to use 4 digits. Why not get used to it now.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.