Calculate date

[Guest]

Hello Everyone!

I have a field in my query called Date. Under criteria I currently have
"Between [Type Start Date] And [Type End Date]" However, I would like the
End Date to just be calculated based on the Start Date. For example, if the
Start Date is 1/1/2007, I want the query to calculate the end date as the end
of that month, 1/31/2006. I would also like another formula to calculate the
end of the month six months for the start date. So for 1/1/2007, the end
date would June 30, 2007. Does anyone know a simple way to do this?

Thank you!!!!

Tandy

 

[John Vinson]

Hello Everyone!

I have a field in my query called Date. Under criteria I currently have
"Between [Type Start Date] And [Type End Date]" However, I would like the
End Date to just be calculated based on the Start Date. For example, if the
Start Date is 1/1/2007, I want the query to calculate the end date as the end
of that month, 1/31/2006. I would also like another formula to calculate the
end of the month six months for the start date. So for 1/1/2007, the end
date would June 30, 2007. Does anyone know a simple way to do this?
Try:

= [Type Start Date] AND < DateSerial(Year([Type Start Date], Month([Type Start Date]) + 1, 1)

and for the second

= [Type Start Date] AND < DateSerial(Year([Type Start Date], Month([Type Start Date]) + 7, 1)

John W. Vinson[MVP]

 

[raskew via AccessMonster.com]

It would be wise to change your field name of [Date] since Date is a reserved
word in Access.
Then:

= [Type Start Date] AND <= DateSerial(Year([Type Start Date], Month([Type Start Date]) + 1, 0) and
= [Type Start Date] AND <= DateSerial(Year([Type Start Date], Month([Type Start Date]) + 6, 0)

Hello Everyone!

[quoted text clipped - 5 lines]

end of the month six months for the start date. So for 1/1/2007, the end
date would June 30, 2007. Does anyone know a simple way to do this?
Try:

= [Type Start Date] AND < DateSerial(Year([Type Start Date], Month([Type Start Date]) + 1, 1)

and for the second

= [Type Start Date] AND < DateSerial(Year([Type Start Date], Month([Type Start Date]) + 7, 1)

John W. Vinson[MVP]

 

[Rick Brandt]

Hi John!

When I try to use either of these, I get the following error message:
The expression you entered has a function containing the wrong number
of arguements. Any ideas???

The Year() functions inside of DateSerial are missing their closing parenthesis.

 

[John Vinson]

The Year() functions inside of DateSerial are missing their closing parenthesis.

Thanks, Rick, and sorry, Tandy - my typo!

Should have been

= [Type Start Date] AND < DateSerial(Year([Type Start Date]), Month([Type Start Date]) + 1, 1)

John W. Vinson[MVP]

 

[John Vinson]

Hi John!

I have a question related to this one. As you know, I ask my user to enter
a start date which pulls all the records from that date to the end of month.
I was curious if I can have the next field pull all the records from that
date the month before to the end of the month before. For example, if the
user is prompted to [Type Start Date] and they enter 11/1/2006, the first
field would pull 11/1/2006 to 11/30/2006. Then could the second field
somehow take that start date, minus a month and pull all the records from
10/1/2006 to 10/31/2006. Would this be possible???

Sure. The DateSerial function takes three arguments: a year (e.g. an
integer 2006); a month; and a day. These numbers can be literal or
calculated; and DateSerial() is very clever about "wrapping" numbers -
that is, though there is no month number 14, you can use 14 in the
second argument of DateSerial and it will calmly return February in
the NEXT year (e.g. DateSerial(2006, 14, 1) = #2/1/2007#).

So...

DateSerial(Year([Type start date]), Month([Type start date]) - 1, 1)

will return the first day of the month PRIOR to the month entered by
the user;

DateSerial(Year([Type start date]), Month([Type start date]), 0)

will return the "zeroth" day of the month the user enters - that is,
the last day of the preceding month.

John W. Vinson[MVP]