current month

I have a query that has the date and time as 11/26/2008 12:42:33pm, what I
want to do is pull the current months data using the system date/time. I've
tried a few different things and I only get partial data, but if I put in
#11/1/2008# and #12/1/2008# I get the correct records. I don't want to
have to put in the date range but use the systems date to determine the month.

Thanks in advance

You could use something like:
WHERE Format([Date Field], "yyyymm") = Format(Date(), "yyyymm")
--
Duane Hookom
Microsoft Access MVP


"John" wrote:

I have a query that has the date and time as 11/26/2008 12:42:33pm, what I
want to do is pull the current months data using the system date/time. I've
tried a few different things and I only get partial data, but if I put in
#11/1/2008# and #12/1/2008# I get the correct records. I don't want to
have to put in the date range but use the systems date to determine the month.

Thanks in advance

On Wed, 26 Nov 2008 09:49:00 -0800, John wrote:

I have a query that has the date and time as 11/26/2008 12:42:33pm, what
I want to do is pull the current months data using the system date/time.
I've tried a few different things and I only get partial data, but if I
put in
#11/1/2008# and #12/1/2008# I get the correct records. I don't want to
have to put in the date range but use the systems date to determine the
month.

Thanks in advance

SELECT *
FROM TableName
WHERE DateField = DateSerial(Year(Date()), Month(Date()), 1)
AND DateField DateSerial(Year(Date()), Month(Date())+ 1, 1)



--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

I tried that but get an syntax error.

"Duane Hookom" wrote:

You could use something like:
WHERE Format([Date Field], "yyyymm") = Format(Date(), "yyyymm")
--
Duane Hookom
Microsoft Access MVP


"John" wrote:

I have a query that has the date and time as 11/26/2008 12:42:33pm, what I
want to do is pull the current months data using the system date/time. I've
tried a few different things and I only get partial data, but if I put in
#11/1/2008# and #12/1/2008# I get the correct records. I don't want to
have to put in the date range but use the systems date to determine the month.

Thanks in advance

John

So, if you are only interested in the "current month"'s data, and if today
is only the 26th, it seems like the only time you'd get the entire month's
data is if you ran this query on the last day of the month.

If you're out that day, or it falls on a weekend, or you forget, or ...,
you'd be in a new month. How do you plan to get "last month's" data, if
that's what you need?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"John" wrote in message
...
I have a query that has the date and time as 11/26/2008 12:42:33pm, what I
want to do is pull the current months data using the system date/time.
I've
tried a few different things and I only get partial data, but if I put in
#11/1/2008# and #12/1/2008# I get the correct records. I don't want to
have to put in the date range but use the systems date to determine the
month.

Thanks in advance

John wrote:

I have a query that has the date and time as 11/26/2008 12:42:33pm, what I
want to do is pull the current months data using the system date/time. I've
tried a few different things and I only get partial data, but if I put in
#11/1/2008# and #12/1/2008# I get the correct records. I don't want to
have to put in the date range but use the systems date to determine the month.


Add a calculated field to the query:

field/expression Month(thedatefield)
criteria =Month(Date())

--
Marsh
MVP [MS Access]

Jeff,

That is a good point, My first attempt I wanted a text box on a form that I
could input my date range into and then have the query run. That would have
been Ideal.

"Jeff Boyce" wrote:

John

So, if you are only interested in the "current month"'s data, and if today
is only the 26th, it seems like the only time you'd get the entire month's
data is if you ran this query on the last day of the month.

If you're out that day, or it falls on a weekend, or you forget, or ...,
you'd be in a new month. How do you plan to get "last month's" data, if
that's what you need?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"John" wrote in message
...
I have a query that has the date and time as 11/26/2008 12:42:33pm, what I
want to do is pull the current months data using the system date/time.
I've
tried a few different things and I only get partial data, but if I put in
#11/1/2008# and #12/1/2008# I get the correct records. I don't want to
have to put in the date range but use the systems date to determine the
month.

Thanks in advance

When you have a syntax error you should reply back with your full SQL view.
It's a bit difficult to troubleshoot something we can see.
--
Duane Hookom
Microsoft Access MVP


"John" wrote:

I tried that but get an syntax error.

"Duane Hookom" wrote:

You could use something like:
WHERE Format([Date Field], "yyyymm") = Format(Date(), "yyyymm")
--
Duane Hookom
Microsoft Access MVP


"John" wrote:

I have a query that has the date and time as 11/26/2008 12:42:33pm, what I
want to do is pull the current months data using the system date/time. I've
tried a few different things and I only get partial data, but if I put in
#11/1/2008# and #12/1/2008# I get the correct records. I don't want to
have to put in the date range but use the systems date to determine the month.

Thanks in advance

John

I'd suggest two controls on the form, [StartDate] and [EndDate], so your
query can point to those values in a "Between ... And ..." statement.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"John" wrote in message
...
Jeff,

That is a good point, My first attempt I wanted a text box on a form that
I
could input my date range into and then have the query run. That would
have
been Ideal.

"Jeff Boyce" wrote:

John

So, if you are only interested in the "current month"'s data, and if
today
is only the 26th, it seems like the only time you'd get the entire
month's
data is if you ran this query on the last day of the month.

If you're out that day, or it falls on a weekend, or you forget, or ...,
you'd be in a new month. How do you plan to get "last month's" data, if
that's what you need?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"John" wrote in message
...
I have a query that has the date and time as 11/26/2008 12:42:33pm, what
I
want to do is pull the current months data using the system date/time.
I've
tried a few different things and I only get partial data, but if I put
in
#11/1/2008# and #12/1/2008# I get the correct records. I don't want
to
have to put in the date range but use the systems date to determine the
month.

Thanks in advance

Hi John.

Try the following as the criteria in your query: Year([Name of column]) =
Year(Now()) And Month([Name of column]) = Month(Now())

Hope this works OK for you.

Katy

"John" wrote:

I have a query that has the date and time as 11/26/2008 12:42:33pm, what I
want to do is pull the current months data using the system date/time. I've
tried a few different things and I only get partial data, but if I put in
#11/1/2008# and #12/1/2008# I get the correct records. I don't want to
have to put in the date range but use the systems date to determine the month.

Thanks in advance