Date Query

[Kissi5559]

Hello,

Could anyone help me with a query? I have a date field and want to display only
records for a specified month.
Example:
Date:5/12/2004
Query for all records in the month of May, 2004.

Thanks.

[Bradley]

Kissi5559 wrote:

Hello,

Could anyone help me with a query? I have a date field and want to
display only records for a specified month.
Example:
Date:5/12/2004
Query for all records in the month of May, 2004.

Thanks.

Build a query and enter a "field"...

DateMonth: Month([MyDateField])

Add criteria "5"

and another field...

DateYear: Year([MyDateField])

With criteria "2004"

OR.........

DateFilter: Month([MyDateField]) = 5 And Year([MyDateField]) = 2004

and then set theCriteria to "True"

OR.........

Select your date field and add the criteria.... (US dates)

Between #05/01/2004# And #05/30/2004#

There's lots of different ways... :)
--
regards,

Bradley

[Trevor Best]

Bradley wrote:

Kissi5559 wrote:

Hello,

Could anyone help me with a query? I have a date field and want to
display only records for a specified month.
Example:
Date:5/12/2004
Query for all records in the month of May, 2004.

Thanks.

Build a query and enter a "field"...

DateMonth: Month([MyDateField])

Add criteria "5"

and another field...

DateYear: Year([MyDateField])

With criteria "2004"

OR.........

DateFilter: Month([MyDateField]) = 5 And Year([MyDateField]) = 2004

and then set theCriteria to "True"

OR.........

Select your date field and add the criteria.... (US dates)

Between #05/01/2004# And #05/30/2004#

There's lots of different ways... :)

The between will be faster as it won't need to run two functions on each
record during select and also if the date field is indexed it will use
it. It's worth noting that if you use dates in a form and reference
those, e.g. between forms!MyForm!MyDate1 and forms!MyForm!MyDate2 then
the dates will be in the country format specified by windows, same if
you put the dates into the query grid but must be in US format in a SQL
literal string.

Oh BTW Bradley, May has 31 days :-)

--
Error reading sig - A)bort R)etry I)nfluence with large hammer

[Trevor Best]

Bradley wrote:

Between #05/01/2004# And #05/30/2004#

There's lots of different ways... :)

Another thing, your dates may have times on so:

Between #05/01/2004# And #05/30/2004 23:59:59#

or

QueryDate >= #05/01/2004# And QueryDate < #06/01/2004#

I prefer the latter as it's more explicit, to the uninitiated it's not
clear whether "between" actually includes the dates you're looking at.
If you think about it, if you drive your car between two posts, do you
want to hit the posts?

--
Error reading sig - A)bort R)etry I)nfluence with large hammer

[Bradley]

Trevor Best wrote:

Bradley wrote:

Kissi5559 wrote:

Hello,

Could anyone help me with a query? I have a date field and want to
display only records for a specified month.
Example:
Date:5/12/2004
Query for all records in the month of May, 2004.

Thanks.

Build a query and enter a "field"...

DateMonth: Month([MyDateField])

Add criteria "5"

and another field...

DateYear: Year([MyDateField])

With criteria "2004"

OR.........

DateFilter: Month([MyDateField]) = 5 And Year([MyDateField]) = 2004

and then set theCriteria to "True"

OR.........

Select your date field and add the criteria.... (US dates)

Between #05/01/2004# And #05/30/2004#

There's lots of different ways... :)

The between will be faster as it won't need to run two functions on
each record during select and also if the date field is indexed it
will use it. It's worth noting that if you use dates in a form and
reference those, e.g. between forms!MyForm!MyDate1 and
forms!MyForm!MyDate2 then the dates will be in the country format
specified by windows, same if you put the dates into the query grid
but must be in US format in a SQL literal string.

Gereral rule is if you are building an SQL string manually or a where
clause in code (or in a property value?), use US dates (?)
Oh BTW Bradley, May has 31 days :-)

I wondered that when I posted.. probably why I don't use that method
often ;) Usually I will know the month and year so using the between
method would require working out the end of month date so I figure might
as well use Month() and Year() in some applications.
--
regards,

Bradley