First day of current month

[Guest]

I'm using an update query to change dates in the NewDate field to the first
day of the month that is currently shown in that field. I'm using the
following query that looks like it's working, but I want to make sure I'm
writing it correctly. Thanks much!

DateSerial(Year([NewDate]),Month([NewDate]),1)

If that is correct does the following mean last month:
DateSerial(Year([NewDate]),Month([NewDate])-1,1)

and next month?
DateSerial(Year([NewDate]),Month([NewDate])+1,1)

Thanks again

 

[Guest]

Hi Alex,

Your first equation looks correct to me, but always make a backup of your
database before running an action query for the first time just in case.

You will definitely run into problems with the other two formulas though,
because you will end up specifying month 0 or 13 in some cases. You would
need to modify the expression to use Iif() functions or something to check to
see if the current month is Jan or Dec. Also, in those cases, you will need
to increment or decrement the year in addition to handling the month
differently.

HTH, Ted Allen

 

[Marshall Barton]

I'm using an update query to change dates in the NewDate field to the first
day of the month that is currently shown in that field. I'm using the
following query that looks like it's working, but I want to make sure I'm
writing it correctly. Thanks much!

DateSerial(Year([NewDate]),Month([NewDate]),1)

If that is correct does the following mean last month:
DateSerial(Year([NewDate]),Month([NewDate])-1,1)

and next month?
DateSerial(Year([NewDate]),Month([NewDate])+1,1)

Those are correct.

 

[George Nicholson]

Yes, Yes, and Yes.

Adding/subtracting a month within DateSerial will give you the correct
results even in January & December. (i.e., 12/15/05 + 1 month = Jan 2006,
etc.)

 

[Van T. Dinh]

Alex

If you already got an idea on the expression / how to, the best way is to
*try* it in your own database. The major advantage is that you get instant
answer rather than having to wait for the answers from someone else!

 

[Van T. Dinh]

Hi Ted

DateSerial will hanndle the first and last month correctly. You can even
use:

DateSerial(Year(Date()), Month(Date()) + 1, 0)

for the last day of the current month.

 

[Guest]

Oops, I guess my lack of experience with DateSerial showed on that response.
Thanks Van, Marsh and George for correcting me, and sorry for the incorrect
response Alex. That will be good to know for the future for sure.

Ted