Calculating dates

[Guest]

I have a 'Start Date'. I am trying to automate this process. Below is a
script that might help you to understand what I am trying to achieve. The
'between' function is what I would like to have automated. The 'end user'
would always know today's date, but I want the computer (script) to be able
to calculate it.

This works if we just use the Between without the Date() function. However,
I would like it to be automated.

I need a date range of the prior month and todays date be greater than the
last day of the prior month.

Between [Beginning of Prior Month] and [End of Prior Month] and Date()>[End
of Prior Month]

 

[Steve Schapel]

Gina,

Today's date will *always* be after the end of the prior month. Unless
I am missing the meaning here.

Can you give some examples to illustrate what you mean. And also say
where/how you are using this expression - is it in the criteria of a query?

 

[Douglas J. Steele]

Beginning of prior month will be DateSerial(Year(Date), Month(Date) - 1, 1)
End of prior month will be DateSerial(Year(Date), Month(Date), 0)

The check Date()>[End of Prior Month] is unnecessary. Since the prior month
is always earlier than today, that will always be true.

 

[Steve Schapel]

Aaahhh!! *Now* I understand the question .

 

[Guest]

Douglas, thank you for your quick response, it is much appreciated.

I'm a little confused... is this how it should look? I tried to make it a
'between statement'. I'm using it in a query in the date field. The program
added the Quotes and then errors out stating 'data mismatch'. It's a select
query and its pulling from only one table.

SELECT [History Table].[Acct #], [stat SC 995].[SC to 995] AS [New Status],
[History Table].[Reason Code #], [History Table].[Closure Type], [History
Table].[Start Date]
FROM [stat SC 995], [History Table]
WHERE ((DateSerial(Year("Date"),Month("Date")-1,1) Between
DateSerial(Year("Date"),Month("Date")-1,1) And
DateSerial(Year("Date"),Month("Date"),0)))
WITH OWNERACCESS OPTION;

--
Gina

Beginning of prior month will be DateSerial(Year(Date), Month(Date) - 1, 1)
End of prior month will be DateSerial(Year(Date), Month(Date), 0)

The check Date()>[End of Prior Month] is unnecessary. Since the prior month
is always earlier than today, that will always be true.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have a 'Start Date'. I am trying to automate this process. Below is a
script that might help you to understand what I am trying to achieve. The
'between' function is what I would like to have automated. The 'end user'
would always know today's date, but I want the computer (script) to be
able
to calculate it.

This works if we just use the Between without the Date() function.
However,
I would like it to be automated.

I need a date range of the prior month and todays date be greater than the
last day of the prior month.

Between [Beginning of Prior Month] and [End of Prior Month] and
Date()>[End
of Prior Month]

 

[Guest]

Douglas,

Co-worker looked at it and was able to tell that open/close paraenthesis
were needed. Thanks again for all you help. Couldn't have done without you.

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

Gina

Beginning of prior month will be DateSerial(Year(Date), Month(Date) - 1, 1)
End of prior month will be DateSerial(Year(Date), Month(Date), 0)

The check Date()>[End of Prior Month] is unnecessary. Since the prior month
is always earlier than today, that will always be true.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have a 'Start Date'. I am trying to automate this process. Below is a
script that might help you to understand what I am trying to achieve. The
'between' function is what I would like to have automated. The 'end user'
would always know today's date, but I want the computer (script) to be
able
to calculate it.

This works if we just use the Between without the Date() function.
However,
I would like it to be automated.

I need a date range of the prior month and todays date be greater than the
last day of the prior month.

Between [Beginning of Prior Month] and [End of Prior Month] and
Date()>[End
of Prior Month]

 

[Steve Schapel]

Gina,

This won't work. I think the field you are applying the criteria to was
called Start Date, in which case the query's Where clause will be like
this...
WHERE [Start Date] Between DateSerial(Year(Date()),Month(Date())-1,1)
And DateSerial(Year(Date()),Month(Date()),0)

By the way, as an aside, it is not a good idea to use # as part of the
name of a field.