DateAdd and DateSerial problem with months and days

H

hom370

On a form I have a combo where a month is entered. It shows the month names
in column one, but it is bound to column two where each month's corresponding
number is, ie Jan=1, Feb=2, etc. So selecting April returns 4. Also on the
form I have a text box where a number is entered and used as the date of the
month in the aforementioned combo box.
I pull those numbers (month number from the combo and date number from the
text box) together using dateserial() and I put that into a dateadd()
statement to find a date previous to the one entered. The date I'm looking
for is the day after the same date last month. For instance, April 31 should
return April 1 and June 15 should return May 16. Here's what I have been
working with:
DateAdd("d",0,DateSerial(Year(Date()),[LastYearMonthToMatch]-1,[LastYearMatchDateReady]+1))
The problem is that it is inconsistent. When Oct 31 is entered, it returns
Oct 2, but should return Oct 1. And when March 31 is entered, it returns
March 4 instead of March 1.
Is there a way to get the results I'm needing?
Thanks!
 
V

Van T. Dinh

Why would you want to add 0 day to a date value as per the posted
expression? 0 is an additive identity so adding (or subtracting) 0 doesn't
do anything.

Besides, the description you post is rather confusing for potential
respondent such as the date "April 31" (there is no such date for any year)
some Fields / controls like [LastYearMonthToMatch] and
[LastYearMatchDateReady] without providing more details on them.

The sample inputs and desired results do not show the patter of what you
want, AFAICS.

Remember that we know nothing about your database and rely completely on the
description. If it is possible, pretend that you know nothing about the
set-up of the database, then re-read the post you are about to send to see
if the description provides the set-up details and your question in a clear,
precise, concise manner. Only then, potential respondent will have a chance
to understand your description as well as your question ...
 
H

hom370

Hi Van,
Thanks for you reply and sorry about the incomplete description. I posted
the wrong (least clear) version of attempt of that statement. I meant to
post this:

DateAdd("d",1,DateSerial(Year(Date()),[LastYearMonthToMatch]-1,[LastYearMatchDateReady]))
or
DateAdd("m",-1,DateSerial(Year(Date()),[LastYearMonthToMatch],[LastYearMatchDateReady]+1))

Anyway, I have it working now. In my incorrect versions I was not
including each add/subtract operation inside its own DateAdd() function.
That was causing the problems I was having.
For the benefit of future searchers, this works:

DateAdd("d",1,DateAdd("m",-1,DateSerial(Year(Date()),[LastYearMonthToMatch],[LastYearMatchDateReady])))

Thanks to FishVal on another board for the solution.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top