Access Date Function Query

[Vivian Dunn]

Can anyone please help with what I am sure is actually a very simple
question.

In the charity, that I work for, I use a database which records the support
that we give to our clients. One of the fields in the database records the
start date of such support. I have a particular query that I wish to run
monthly so that it lists all the clients whose support started during the
previous month.

In the initial version of the query, I used the 'Between' and 'And'
functions to prompt me to enter the appropriate start and end dates.
However, I was sure that this could be automated and so, rather by trial and
error, I have now changed the date criteria to Between
DateSerial(Year (Date()),Month( Date())-1,1) And (Date()-Day(Date())).

This works ok at present but I realise that it will throw a wobbly when we
get to the January report. However, in any case, I expect there is a much
more elegant way to do what I am attempting. Can anyone offer any advice
please?

Thanks.

[MGFoster]

Re: Access Date Function Query

Start of previous month:

dateserial(year (dateadd("m",-1,date)),month( dateadd("m",-1,date)),1)

--
MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

Vivian Dunn wrote:[color=blue]
> Can anyone please help with what I am sure is actually a very simple
> question.
>
> In the charity, that I work for, I use a database which records the support
> that we give to our clients. One of the fields in the database records the
> start date of such support. I have a particular query that I wish to run
> monthly so that it lists all the clients whose support started during the
> previous month.
>
> In the initial version of the query, I used the 'Between' and 'And'
> functions to prompt me to enter the appropriate start and end dates.
> However, I was sure that this could be automated and so, rather by trial and
> error, I have now changed the date criteria to Between
> DateSerial(Year (Date()),Month( Date())-1,1) And (Date()-Day(Date())).
>
> This works ok at present but I realise that it will throw a wobbly when we
> get to the January report. However, in any case, I expect there is a much
> more elegant way to do what I am attempting. Can anyone offer any advice
> please?[/color]

[Bob Quintal]

Re: Access Date Function Query

"Vivian Dunn" <vivian.dunn@sp eed-mail.co.uk> wrote in
news:42703a7d$1 @news.greennet. net:
[color=blue]
> Can anyone please help with what I am sure is actually a very
> simple question.
>
> I have now
> changed the date criteria to Between
> DateSerial(Year (Date()),Month( Date())-1,1) And
> (Date()-Day(Date())).[/color]
[color=blue]
> Can anyone offer any advice please?
>[/color]
Date()-Day(Date()) corrects for the previous year, so use the
dateadd function to go back one additional month.

BETWEEN Dateadd("m",(Da te()-Day(Date()),-1) AND (Date()-Day(Date())

--
Bob Quintal

PA is y I've altered my email address.

[Vivian Dunn]

Re: Access Date Function Query


"Bob Quintal" <rquintal@sPAmp atico.ca> wrote in message
news:1114645165 .e5f70846640ac6 0a046de36ce5d3d e16@teranews...
[color=blue]
> Date()-Day(Date()) corrects for the previous year, so use the
> dateadd function to go back one additional month.
>
> BETWEEN Dateadd("m",(Da te()-Day(Date()),-1) AND (Date()-Day(Date())
>[/color]

Many thanks to both of you.