Automatic date conversion

[juicybeasty]

I have a situation where I need dates in two separate formats.

Currently I have 3 fields - FollowUpDay (eg 19), FollowUpMonth (eg may) and FollowUpYear (eg 03). What I want to do is retain these, but have another field - FollowUpDate - in date/time format.

So I can work with the two formats simultaneously, they both need to be the same, which suggests an automatic update using VBA. My intention is that, on the form, the FollowUpDay, Month, Year fields will not be enabled, and the date could be entered in date/time format.

All I need help with is what code to put in the AfterUpdate event of the FollowUpDate field on the form, in order to separate the date into 3 separate fields.

Or is there a better way of doing it? In fact all I need from the date is the month, to use in a parameter query for reports.

Any suggestions?

 

[dcx693]

Your explanation confused me a bit, but I think you don't need to do any type of separation. Why not just calculate the month using the Month() function in the query that feeds into your report?

 

[namliam]

As a general rule dont store depending results in your table.

To extract the things you want from a date field:
Day(YourDate)
Month(YourDate)
Year(YourDate)

in the after update event you can put
Me.YourDayField = Day(Me.YourDateField)
And so on... But ... -- see first line--

You can use the same formula's in a query to use for parameters and stuff....

Regards

The Mailman

 

[juicybeasty]

Ha ha, I know it was confusing, I was kind of changing my mind as i wrote it. Sorry about that.

Forgive me for sounding thick, but I assume the month() function extracts the month from the date, and then I can store it in a variable, and assign this variable to a text box on my form?

I don't think you're supposed to, but could I then store this in the table to use in my parameter query?

 

[dcx693]

The Month() function does in fact extract the month from a date.

You can store as many things as you want in your table, but a good rule of thumb in database design is to store only what you need and calculate the rest.

If you must have the month on a form control, just use the following in the form control's controlsource: =Month(myformfield).