this month minus 1 5

[sedgely]

I am setting up a query to run on the first day of each month, what i want it to do is to return records where the date is within the previous month. I've figured out that i need to firstly work out the month part of todays date and then to return all records where the date is "month - 1". but i can't figure out how to actually do it, any suggestions anyone?

Cheers
Craig

 

[RivetHed]

I think month(now())-1 might do it

 

[sedgely]

Thanx 4 that - worked a treat

Craig

 

[danvlas]

Yes, it worked because it's February... For January it would return 0...

Month(Date-Day(Date)) will always return a valid month...

Good luck



Daniel Vlas
Systems Consultant

 

[sedgely]

Cheers Daniel
Not thought about that, couldn't get your solution to work but have figured a way round it.
Thanks

Craig

 

[WildHare]

Check out the DATESERIAL function - it can be used to return a variety of useful date parameters:

Last day of the previous month:
DateSerial(Year(Date()), Month(Date()), 1-1)

Last day of Current Month:
DateSerial(Year(Date()), Month(Date())+1,1) -1

First Day of Previous Month:
DateSerial(Year(date()), Month(Date())-1,1)

First Day of Current Month:
DateSerial(Year(Date()), Month(Date()), 1)


Jim
Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[JaneInMA]

Wildhare, just a brilliant example of how a small tip can help a lot. Thanks, I feel dateserial may be my new favourite piece of code. It will make my crosstab queries self sustaining!

 

[RivetHed]

hey that is handy Wildhare, I think your more deserving of the star than me on this one.

 

[WildHare]

Although I appreciate the sentiment, I had nothing to do with the implementation or inclusion of the DateSerial function in Access.



Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[JaneInMA]

yes but code is only useful when you learn about it and how to use it. Your tip to look in this direction was helpful.

 

[Terrier]

I am just reading this as I want to set up a query/report to alert me when an item is 60 days off it's expiry date.

So the query will include Month(Date-Day(Date)) - 60?

 

[danvlas]

No...it will return something between -59 and -48...
Date() - 60 will return what you need.

And now I managed to figure why Craig couldn't work out my formula. In a query, the Date function must be written like Date(). I use the formula in code and the () are not necessary... So, Craig, try it in this way:

Month(Date-Day(Date()))

Regards,

Dan
Daniel Vlas
Systems Consultant

 

[Terrier]

OK.

I am trying to build this into a query (using expression builder) to display Items whose expiry date is 60 days or less, but some items do not have an expiry date. How do I get around this.

BTW the expression I have come up with is &quot;If «Expr» [Expiry_date]&quot;<=(Date()-60)

But it doesn't look like it works...help?!?

 

[RivetHed]

hi, you should try IIf([Expiry_date]<=(Date()-60),&quot;Something&quot;,&quot;Something else&quot

 

[Terrier]

Tried that and got an error msg saying DATA TYPE MISMATCH IN CRITERIA TYPE

 

[RivetHed]

And the Expiry_date field is set up with a date type?

 

[JeremyNYC]

Wildhare,

I'm in a rush to finish a report for a client before getting out of here to catch a flight and I needed this code quick. One quick search and it's done.

Thanks much.

Jeremy ==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[Terrier]

Rivethed,

Yes it is a medium date.

 

[RivetHed]

Ok, if you just want a calculated field to tell you wether an item is within your timeframe or not try:

IIf([expiry_date]<=Date()-60,&quot;summat&quot;,&quot;summat else&quot

if your putting this in the criteria of your expiry date field try:

[expiry_date]<=Date()-60

with the calculated field option you might want a third option for no expiry date? if so try this:

IIF(IsNull([expiry_date]),&quot;No Expiry Date&quot;,IIf([expiry_date]<=Date()-60,&quot;summat&quot;,&quot;summat else&quot)

HTH

 

[Terrier]

RivetHed

Many thanks for your help, I am truly grateful, I have spent most of the week trying to get that to work.

Mark