Calculating a date based on user entry

[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.

 

[John Spencer (MVP)]

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.

 

[Andrew Smith]

You can do this without using the IIf function:

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