Concatenate 2 Form Controls To 1 Table Column

[Soule]

Hello, everyone -


I have a concatenation and control source problem in a .mdb database in Access 2007 that I'd appreciate any help on:

I originally had a text box control on a form (formatted as a short date, mm/dd/yyyy) that I wanted to split apart into three text box controls, one for mm, one dd and one yyyy. I want to concatenate the values in the three of them into one column ("Start Date") on the form's corresponding table. The three text box controls are not control sourced to "Start Date" column. I successfully tested concatenation of the three box controls into another text box control ("mm/dd/yyyy") on the form, including date "slashes", using the expression = ([mm control] & "/" & [dd control] & "/" & [yyyy control]). The problem is, I'm not sure if it is necessary to create this concatenated control in the form and then control source it to the "Start Date" column I want.

* If I DO have to concatenate ON THE FORM, how do I control source to the table column...the "control source" property has been used up by the concatenation of the three split boxes.

* If I DON'T have to concatenate on the form and can write an expression that allows the "Start Date" table colum to draw DIRECTLY from the 3 date controls, how do I do it? Do I do it on the property sheet of the column in table design view? Using a concatenation expression in any validation rule property didn't work.

Any advice would be appreciated.

Frustrated,
FJ

[hertfordkc]

http://allenbrowne.com/ser-51.html

It is a tip from RuralGuy for popup calendar controls. Is there a reason why you don't want to or can't use one?

[Soule]

Hello, hertfordkc - Thanks for replying -

I want to give my boss the option of data entering a date in one box mm/dd/yyyy or three boxes, mm, dd and yyyy. I want the form to have the right data entry flow for him...

[Soule]

I figured it out guys. I went with the "On Lost Focus" method, and I thought since I was using an event procedure, my code didn't need DIM statements like a module does. Here is the simple code that did it to which I will consider adding RJD's suggestion of resetting my mm, dd, yyyy concatanatee controls back to null after they combine and populate:

Private Sub yyyy_control_LostFocus()
Dim Month As Integer
Dim Day As Integer
Dim Year As Integer

Month = [mm control].Value
Day = [dd control].Value
Year = [yyyy control].Value

start_date_control.Value = Month & "/" & Day & "/" & Year

End Sub
--------------
Thanks for your suggestions!

S.