Dates

[Stuart Masters]

Hope one of you out there can help!

I have a date query to return all dates in 2 months time:

Month(DateAdd(" m",2,Date()) )

However this seems to be returning dates 2 months ahead for any year. Any ideas?

Also, how can I get the query to bring up overdue dates on the same report.

Any help would be very much appreciated on this topic.

Thanks

stu

[Peter Russell]

Re: Dates

Stuart Masters previously wrote:
[color=blue]
> Month(DateAdd(" m",2,Date()) )[/color]

This expression returns the month number (only) of the month that is two
months on from the current month.

Can you explain again what it is you want to get? And where you are
trying to get it.

Peter Russell

[Stuart Masters]

Re: Dates

I currently have a date column in a table which has the date of next
assessment in it. I use this column in a query to bring up supposed
dates of assessments in two months time.

I would like the expression to work from a query so that, for example,
in the month of May someone who accessed this report based on the query,
would be given only the names of those people due for re-assessment in 2
months time (i.e., July).

Thanks for any help you can provide me with

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Wayne Morgan]

Re: Dates

Stuart,

Create a calculated field in your query.

Example:
FilterDate:Form at([DateField], "mmyyyy")

For the criteria, try
Format(DateAdd( "m",2,Date( )), "mmyyyy")

This should get the year portion in as well as the month portion. If you
wish, you can uncheck the show box for this field so that it won't be
returned by the query, but just used to filter the results.

--
Wayne Morgan
MS Access MVP


"Stuart Masters" <stuart.masters @pcs-tr.swest.nhs.uk > wrote in message
news:402cca0b$0 $200$75868355@n ews.frii.net...[color=blue]
> I currently have a date column in a table which has the date of next
> assessment in it. I use this column in a query to bring up supposed
> dates of assessments in two months time.
>
> I would like the expression to work from a query so that, for example,
> in the month of May someone who accessed this report based on the query,
> would be given only the names of those people due for re-assessment in 2
> months time (i.e., July).
>
> Thanks for any help you can provide me with
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]

[Stuart Masters]

Re: Dates

Thanks Wayne works a charm. All I need now is to know how to bring up
overdue dates for the same year, i.e., <Now()(how does this work in the
criteria?)

Stu

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Wayne Morgan]

Re: Dates

<Now() will work as criteria for a date field. If the field is just a date
(no time) you could also use <Date(). To determine Over Due, I would need a
little more explanation. While you can see if today is past the Due Date,
you also need to check a field to see if the item was accomplished. Do you
want to know if it is still overdue or if it is overdue and was overdue when
accomplished. This will probably require setting criteria on more than one
field.

--
Wayne Morgan
MS Access MVP


"Stuart Masters" <stuart.masters @pcs-tr.swest.nhs.uk > wrote in message
news:402cf206$0 $202$75868355@n ews.frii.net...[color=blue]
> Thanks Wayne works a charm. All I need now is to know how to bring up
> overdue dates for the same year, i.e., <Now()(how does this work in the
> criteria?)
>
> Stu
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]

[Salad]

Re: Dates

Stuart Masters wrote:
[color=blue]
> Thanks Wayne works a charm. All I need now is to know how to bring up
> overdue dates for the same year, i.e., <Now()(how does this work in the
> criteria?)
>
> Stu[/color]

Do not use NOW() unless comparing dates AND times. If comparing dates
only, you Date or other date functions like Date(), DateSerial, DateAdd,
DateDiff, etc.