Move Field Data to New Field

[Guest]

I have 2 fields lets call X & Y. I run a query to produce the selected records for both fields from entry off of forms in my database

What would I need to do to move data from these 2 fields to a new field Z? I am having trouble with the correct SQL statement to get data from existing fields to empty fields.

I have added this field to main table.

 

[Andrew Smith]

Since you are already storing the values of X and Y in the table, you should
not add any fields to the table that can be calculated from the values of X
and Y. Instead you should create a query based on your table and add a
calculated field to the query that calculates the value of Z from X and Y.

I have 2 fields lets call X & Y. I run a query to produce the selected

records for both fields from entry off of forms in my database.

What would I need to do to move data from these 2 fields to a new field Z?

I am having trouble with the correct SQL statement to get data from existing
fields to empty fields.

 

[Guest]

What you say makes perfect sense but I am slow at building expressions!!

Can you provide an example of what the calulated field would look like if I wanted to move dates from Field X and Y into Field Z? My problem is knowing what criteria to use to do this

 

[Andrew Smith]

This does not involve any criteria, unless you also want to limit which
records are returned.

Create a new query in design view. Add your table, and double click on the *
at the top of the list of fields in the table to add all fields to the query
(or select just the fields you need). Then enter your expression in a blank
field cell. You enter the field name followed by a colon followed by the
expression. You can include field names in the expression, and these must be
enclosed in square brackets if the field name includes spaces or other
special characters.

You don't say what fields X and Y are, or what type of data, or what
expression you would use to get Z, so it's a bit difficult to give you any
further help. As a simple example, if X and Y are numeric and Z is just the
sum of X and Y then you would enter:

Z:[Field X] + [Field Y]

If they are text and you want to string them together separated by a space
then you would put:

Z:[Field X] & " " & [Field Y]

So if X was "Andrew" and Y "Smith" then you would get "Andrew Smith" in Z.

What you say makes perfect sense but I am slow at building expressions!!!

Can you provide an example of what the calulated field would look like if

I wanted to move dates from Field X and Y into Field Z? My problem is
knowing what criteria to use to do this

 

[Guest]

Thanks for helping so much

The fields X and Y are date fields (mm/yy/dd). Field X always has a date entered and Field Y may or may not. I need to combine the two fields so that field Z is in date order after they are combined.

Ultimately I am doing this because I need a report to show the combined field along with original Field X date values.

 

[Andrew Smith]

Do you mean that field Z should equal X if there is nothing in Y, or equal Y
if there is something in Y? If so use the IIf function:

Z:IIf(IsNull([Field Y]),[Field X],[Field Y])

Thanks for helping so much.

The fields X and Y are date fields (mm/yy/dd). Field X always has a date

entered and Field Y may or may not. I need to combine the two fields so
that field Z is in date order after they are combined.

Ultimately I am doing this because I need a report to show the combined

field along with original Field X date values.