Date and IIF

[scubadiver]

I have a date called A in 'date/time' format and I need a new date called B
based on A.

If the day of month is less than the 11th then N needs to be the 1st of the
month in A. If the day of month is 11th or later then it needs to be the 1st
of the next month.

At the moment I have the following and it is text format:

Month1: IIf(Format([detdate],"dd")<=10,"1/" & Format([detdate],"mm/yy"),"1/"
& (Format([detdate],"mm")+1) & "/" & (Format([detdate],"yy")))

I need it in 'date/time' format.

thanks!

 

[Dave Peterson]

Maybe...

Dim A as date
Dim B as date

'how does A get populated???
'test data
a = dateserial(2008,1,2) + timeserial(12,30,45)

if day(a) < 11 then
b = dateserial(year(a), month(a), 1)
else
b = dateserial(year(a), month(a) + 1, 1)
end if

or as one line:
B = DateSerial(Year(A), Month(A) - CBool(Day(A) > 10), 1)

What's N?

I have a date called A in 'date/time' format and I need a new date called B
based on A.

If the day of month is less than the 11th then N needs to be the 1st of the
month in A. If the day of month is 11th or later then it needs to be the 1st
of the next month.

At the moment I have the following and it is text format:

Month1: IIf(Format([detdate],"dd")<=10,"1/" & Format([detdate],"mm/yy"),"1/"
& (Format([detdate],"mm")+1) & "/" & (Format([detdate],"yy")))

I need it in 'date/time' format.

thanks!

 

[Kevin B]

I believe you were aiming for the Access forum and overshot your mark,
however, see if this works:

=Iif(Day([detdate])<=10,Dateserial(Year([detdate]),
month([detdate]),1),Dateserial(Year([detdate]), month([detdate]) + 1,1))

What it states is if the day of the month in the detdate field is <=10,
return the first day of the current month in the current year, otherwise
return the first day of next month in the current year.

Hopefully I've closed off all my parenthesis

 

[scubadiver]

I *was* looking for the Access forum, d'oh!

I believe you were aiming for the Access forum and overshot your mark,
however, see if this works:

=Iif(Day([detdate])<=10,Dateserial(Year([detdate]),
month([detdate]),1),Dateserial(Year([detdate]), month([detdate]) + 1,1))

What it states is if the day of the month in the detdate field is <=10,
return the first day of the current month in the current year, otherwise
return the first day of next month in the current year.

Hopefully I've closed off all my parenthesis
--
Kevin Backmann

I have a date called A in 'date/time' format and I need a new date called B
based on A.

If the day of month is less than the 11th then N needs to be the 1st of the
month in A. If the day of month is 11th or later then it needs to be the 1st
of the next month.

At the moment I have the following and it is text format:

Month1: IIf(Format([detdate],"dd")<=10,"1/" & Format([detdate],"mm/yy"),"1/"
& (Format([detdate],"mm")+1) & "/" & (Format([detdate],"yy")))

I need it in 'date/time' format.

thanks!

 

[scubadiver]

Thanks for that Kevin. It looks like it has worked.

Cheers!

I believe you were aiming for the Access forum and overshot your mark,
however, see if this works:

=Iif(Day([detdate])<=10,Dateserial(Year([detdate]),
month([detdate]),1),Dateserial(Year([detdate]), month([detdate]) + 1,1))

What it states is if the day of the month in the detdate field is <=10,
return the first day of the current month in the current year, otherwise
return the first day of next month in the current year.

Hopefully I've closed off all my parenthesis
--
Kevin Backmann

I have a date called A in 'date/time' format and I need a new date called B
based on A.

If the day of month is less than the 11th then N needs to be the 1st of the
month in A. If the day of month is 11th or later then it needs to be the 1st
of the next month.

At the moment I have the following and it is text format:

Month1: IIf(Format([detdate],"dd")<=10,"1/" & Format([detdate],"mm/yy"),"1/"
& (Format([detdate],"mm")+1) & "/" & (Format([detdate],"yy")))

I need it in 'date/time' format.

thanks!