formatting a date

[NigelShaw]

Hi,

how could i format a date so that whatever month i am in, a date would be set for a specific day number but the month & year change?

for example-

i need to fix a recurring date every month to 16th.
if the current date was 01-03-2009 and i clicked my button, the date set would 16-03-2009.

my thought was, if the button was pressed BEFORE 16th, the date would be changed to 16-03-2009 but if it was pressed AFTER the 16th, the date would be set to 16-04-2009

i cant seem to get my formatting right.


many thanks,


Nigel

 

[SimonB1978]

Hi,

Try that:

In VBA:
If Day(my_date) <= 16 Then
my_date = DateSerial(Year(my_date), Month(my_date), 16)
Else
my_date = DateSerial(Year(my_date), Month(my_date) + 1, 16)
Endif

In SQL:
IIf(Day(my_date) <= 16, DateSerial(Year(my_date), Month(my_date), 16), DateSerial(Year(my_date), Month(my_date) + 1, 16) AS formatted_date


That should work assuming your date in is Date/Time format.


Simon B.

 

[NigelShaw]

Hello mate,

i went with the VBA version. works a treat thanks very much



nigel

 

[SimonB1978]

You're welcome!

Simon B.