query to find dates 3 months prior to 1st of current month

N

nycdon

trying to figure easy way to get 1st of current month (3/1/2004) - figure
then i could use DateAdd to minus 3 months - the first of month is stumping
me here this morning - can i do this in a query?
thanks!
Don
 
M

Marshall Barton

nycdon said:
trying to figure easy way to get 1st of current month (3/1/2004) - figure
then i could use DateAdd to minus 3 months - the first of month is stumping
me here this morning - can i do this in a query?
Click to expand...


Use the DateSerial function.

DateSerial(Year(Date()), Month(Date()) -3, 1))
 
J

Jason

I am trying to run this and I understand the Date serial... I need to view
just one month, Automatically. This is what I have so far. I just need to
know how to add the rest of the months days into this... I use DateAdd
correct?

DateSerial(Year(Date()),Month(Date())-1,1)

That will give me the Nov 1st Entry but I want to see all for that month,
not just bvy using - or + 30 or 31, Since the DateSerial actually recognizes
the month (and just doesnt go back 31 days) It should be able to just show me
the previous month (Nov in this case) no matter what date today is. So when
Jan 1st rolls around it will display Dec 1st-31st. Sorry if that doesnt sound
clear, I think that gets my goal across.
 
D

Douglas J. Steele

Sorry, are you asking how to get the last day of the month? That's

DateSerial(Year(Date()),Month(Date()),0)
 
J

Jason

No, I am trying to get the ENTIRE month, Using the dateSerial so no matter
what the Current Date is it will show me the entire previous month. Currently
I am using this:
=Now()-31 Or 30 according to the month.
Click to expand...

However, This requires me to run the aquery on the First... But since I have
a data access page based on info from this query.... The chart is showing
incorrect info.
 
J

Jason

Actually, Since the last digit in the code:

DateSerial(Year(Date()),Month(Date()),-1,0)
Where 0 equals end of month. and 1-0 doesnt display all the information,
how/what operator/function would I use to view all data for ONLY that month?
 
F

fredg

I am trying to run this and I understand the Date serial... I need to view
just one month, Automatically. This is what I have so far. I just need to
know how to add the rest of the months days into this... I use DateAdd
correct?

DateSerial(Year(Date()),Month(Date())-1,1)

That will give me the Nov 1st Entry but I want to see all for that month,
not just bvy using - or + 30 or 31, Since the DateSerial actually recognizes
the month (and just doesnt go back 31 days) It should be able to just show me
the previous month (Nov in this case) no matter what date today is. So when
Jan 1st rolls around it will display Dec 1st-31st. Sorry if that doesnt sound
clear, I think that gets my goal across.
Click to expand...
No need for DateAdd to show just the one month's records.

Between DateSerial(Year(Date()),Month(Date())-1,1) and
DateSerial(Year(Date()),Month(Date()),0)

The 0 day of the current month is the last day of the previous month.
 
J

Jason

OK, Wierd story... While at another computer I input that code, went back to
my laptop, opened the database... and i get a message stating "Undefined Date
in Expression".... I tried to recopy the code you gave me and tried doing it
by hand, and nothing... still the same.... does it matter that i went from
2003 to 2002? I see the DateSerial function in 2002, So whats the problem?
 
F

fredg

OK, Wierd story... While at another computer I input that code, went back to
my laptop, opened the database... and i get a message stating "Undefined Date
in Expression".... I tried to recopy the code you gave me and tried doing it
by hand, and nothing... still the same.... does it matter that i went from
2003 to 2002? I see the DateSerial function in 2002, So whats the problem?
Click to expand...

That PC has a missing reference.
Open any module in Design view (or click Ctrl + G).
On the Tools menu, click References.
Click to clear the check box for the type library or object library
marked as "Missing:."

An alternative to removing the reference is to restore the referenced
file to the path specified in the References dialog box. If the
referenced file is in a new location, clear the "Missing:" reference
and create a new reference to the file in its new folder.

See Microsoft KnowledgeBase articles:
283115 'ACC2002: References That You Must Set When You Work with
Microsoft Access'
Or for Access 97:
175484 'References to Set When Working With Microsoft Access' for
the correct ones needed,
and
160870 'VBA Functions Break in Database with Missing References' for
how to reset a missing one.

For even more information, see
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html
 
D

Douglas J. Steele

Between DateSerial(Year(Date()),Month(Date()) - 1, 1) And
DateSerial(Year(Date()),Month(Date()),0)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Jason said:
No, I am trying to get the ENTIRE month, Using the dateSerial so no matter
what the Current Date is it will show me the entire previous month.
Currently
I am using this:
=Now()-31 Or 30 according to the month.
Click to expand...

However, This requires me to run the aquery on the First... But since I
have
a data access page based on info from this query.... The chart is showing
incorrect info.
Click to expand...
 
Top