populating fields on a form using other fields on that form

[dupontk]

Here it is I have four fields on a form that come from a query. Fields a,b,c,d.
a is a text field that looks like a date 1/2/2009, b is a time that looks like 8:00:00 AM and c is a concatanated text field 1/2/2009 8:00:00 AM.

Now d is a Date/Time Field formated as General date . I want to set the control property of d to transfer the date which is a text field into the d field so it will be an actual date. So I entered as the control property =[C] . I saved the record , closed the form and looked at the table that has a,b,c,d . The d field remains empty. Why ?? I also tried as the control property =CDate([C]) still doesn't work. Lastly I created an update query on the table and tried to update the d field and no go. Help

[NeoPa]

You can only sensibly do this with the Update Query approach.

Having said that of course, the whole concept is highly dodgy. You really do not want to duplicate data in your database instead of simply working it out on the fly. See Normalisation and Table structures for a full explanation why.

[dupontk]

I realize what you are saying but to avoid having someone type in the date and time in a field on the form, All they have to do is type the date and from a pull down combo box select the time, You might appreciate the problem of entering all the / and colons and getting the date corectly formated on data entry. I figured I could assist with pull downs. I don't mind the duplication since this project will not contain thousands of records. Ken

[NeoPa]

@dupontk
I doubt you do, or at least you don't understand the full ramifications of that approach.

That said, it's really your funeral and if you want to update the field (d) then I can help you do that.

Let's take it from what you have already tried. Can you post the SQL for the update query you tried. From that I will see your actual field names and table name etc.

[dupontk]

UPDATE test SET test.d = CDate([C]), test.g = CDate([f]);

I tried it with d=[C] and g = [F]

[NeoPa]

I would expect that to work Ken.

Can you explain what did happen when it was run.

I'm ignoring the [G] bit as this wasn't part of the original question. For (good) reasons of simplification I expect.

[dupontk]

When I ran the Update query it said 1 record not updated due to data type conversion.

[dupontk]

Hey I got it. I had to run 2 update queries . I thank you for the help. Your invited to the funeral.

[NeoPa]

@dupontk
You shouldn't need two queries (I'm assuming you're talking about one for each field). Did you note the data in the record that didn't get updated? It probably contained some string data that couldn't be interpreted as a date for some reason.
@dupontk
Things going that well huh :D

No worries, we can help you past some of the difficulties.