By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,601 Members | 989 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,601 IT Pros & Developers. It's quick & easy.

Date Query

P: n/a
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.
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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
Nov 12 '05 #2

P: n/a
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
Nov 12 '05 #3

P: n/a
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
Nov 12 '05 #4

P: n/a
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
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.