After Update Entry

[Sharon Hague]

Hi All

I have a form with a date/time field called "Date".

I have created a label within this form called "Mth"

I want to be able to put an iif function into the After Update properties of my field "Date" so that it returns a value in my "Mth" field dependant on the field "Date" results.

i.e

iif [Date] between #01/01/03# and 31/01/03 then [Mth] = "1"
else iif [Date] between #01/02/03# and 28/02/03 then [Mth] = "2"
else iif [Date] between #01/03/03# and 31/03/03 then [Mth] = "3"
else "error"

I've not much knowledge on VBA and can't seem to get anywhere with the above.

Can anybody help?

 

[Mile-O]

Two things:

1) Don't have a file called Date as this is a reserved word in Access and can be confused, at times, with the Date() function;

2) Rather than make the process of getting a month so complicated, you can use the Month() function.

i.e.

=Month(Date)

 

[Mile-O]

And you don't need to store the month in a field as it can be calculated anytime in a query using the Month() function on your date field.

 

[dcx693]

Mile is right, but a note regarding IIf and If:

IIf() is a formula, which means that it calculated values, it doesn't perform actions (you're confusing it with the "If" statement).

 

[Sharon Hague]

Mile-O-Phile

Thanks for your reply,

I have changed my field "Date" to "MyDate" in my form.

Also, in my after update part of the properties of "MyDate" I have changed the coding to read [Mth] = Month(Date).

However, now when I enter a new record, whatever date I enter changes all other existing records to the same.

Any suggestions?

 

[Mile-O]

Are you using a continuous form?

 

[Sharon Hague]

Mile-O-Phile

Its a subform

 

[Rich]

You don't need any functions to do this, just set the control source of the textbox to that of the datefield and set the format on the property sheet to M

 

[Sharon Hague]

Rich

I didn't realize it was as easy as that! - It's worked o.k

Cheers

 

[dcx693]

Rich, I always forget about those custom formats!

 

[Mile-O]

I just get confused when someone mentions field in relation to a form when, to me, I'd rather read bound control or something less vague (bound textbox.)

I also get confused when I switch on a computer.