current month

[John]

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

 

[Duane Hookom]

You could use something like:
WHERE Format([Date Field], "yyyymm") = Format(Date(), "yyyymm")

 

[Rick Brandt]

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
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)

 

[John]

I tried that but get an syntax error.

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

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
have to put in the date range but use the systems date to determine the month.

Thanks in advance

 

[Jeff Boyce]

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

 

[Marshall Barton]

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

 

[John]

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.

 

[Duane Hookom]

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

I tried that but get an syntax error.

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

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

 

[Jeff Boyce]

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

 

[KatyTK]

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