Calculating a date based on user entry

  • Thread starter Thread starter Stan
  • Start date Start date
S

Stan

I want to create query to find the following date:

If the date enter is 9/15/03 I want the query to return
10/1/03. I can do this using the DateSerial function.

The second part is if the date is the first of the month
then used that date. if the enter date is 10/1/03 then use
the entery 10/1/03.

I do not know how to write the if statement to look at the
entry date and determine if it is the first of the month.

here is an example of what I want:

iff date is firstofmonth then use entry date, iff date is
greater than first of month then use first of next month.
 
Try using an immediate if statement. Something like the code below.

IIF(Day([Enter Date]) = 1,
[Enter Date],
DateSerial(Year([Enter Date]),Month([Enter Date])+1,0))

That should all be on one line, but for ease of reading I've split it onto three.
 
You can do this without using the IIf function:

DateYouWant = DateSerial(Year(EnterDate), (Month(EnterDate - 1) + 1) Mod 12,
1)
 
Back
Top