Problem with Month() function as query parameter

[leolanza]

Hello I have been attempting to solve this problem for about 2 days now and need some assistance.

The query I am developing requires furturistic forecasts that are placed manually into a date field(shortdate). I am using the month() function to automatically take todays date, find todays month and look at the forcasts 1 month ahead.

The query seemed to work well until December came. I found out that the month function only seems to work when the following month does not include a year change. I tested it by changing the system date and selecting the forecast query again, and that time it worked fine, the system date was changed to include a month forward that is the same year!

Can anyone assist as this is driving me crazy fast!

 

[boblarson]

Why use the month, just use Date Add

So, you have your date field and the criteria would be:

=DateAdd("m", 1, [YourDateField])

 

[leolanza]

Thanks I will try that later tonight

Doesnt the dateadd function just add date values to fields though.

I will test it and post the results.....thanks again

 

[boblarson]

Thanks I will try that later tonight

Doesnt the dateadd function just add date values to fields though.

I will test it and post the results.....thanks again

What the date add will do is, if the date field says 12/20/2009 then you add one month the effect of the criteria would be =#1/20/2009#

So, if you want the entire month then you could do this instead:

create a field in your query for month and year like:

MyNewFieldName:Year([YourDateFieldNameHere]) & Month([YourDateFieldNameHere])

And then in the criteria you use:

Year(DateAdd("m",1,[YourDateFieldnameHere])) & Month(DateAdd("m",1,[YourDateFieldNameHere]))

and actually that might be better.

 

[leolanza]

Sorry Bob

Ive added the criteria as specified but it supplied the exact same result. Im going to try playing around with it some more and hopefully with some luck get better results.

Leo

 

[raskew]

Hi -

Try this as the criteria for your date field:

Between DateValue([enter mm/yyyy]) And DateAdd("m",1,DateValue([enter mm/yyyy]))-1

Run the query and, when prompted enter mm/yyyy (e.g. 12/2009).
Should return all records for the input month.

HTH - Bob

 

[leolanza]

Thanks for that

It does work but i need to to look automatically at the next months forecast.

Leo

 

[gemma-the-husky]

obviously the problem is that if you add 1 to December, you get to January - which may or may not work for you.

So your code needs to handle the year change, and this will depend how this is implemented in your database - eg you may not even have a next month after month 12

 

[leolanza]

THis seems to be the case, if i add test data for february and change the system date to January the query works fine. However if i the next month also involves a year change it just throws up a problem and doesnt select anything.

The problem with the month function is that it only returns an integer value between 1 and 12 respectivley. I dont think the function was designed to deal with year changes to begin with.

Ive been playing around with IIF statements so that if a selected month falls within a different year it may include this in the selection. However up to now im having no joy.

 

[Brianwarnock]

In your date field's criteria put

Between Dateserial(year(date()),month(date())+1,1) and dateserial(year(date()),month(date())+2,0)

this will select all records with a date between the 1st and last day of the next month and allows for year change, varying length months.

Brian

 

[leolanza]

Thanks

Im going to have a look at this when I get home tonight. I will post the results.


Many thanks to all of you who have spent the time on this.


Leo