update query syntax

  • Thread starter Thread starter Randy Starkey
  • Start date Start date
R

Randy Starkey

If I want to update a field to the alpha combination of 3 text fields, what
is the correct syntax? I have a field I want to update to
FirstName+space+MiddleName+space+LastName.

Thanks!

--Randy Starkey
 
Follow up - these are all fields in the same table and some are nulls. Field
I'm updating is called FormalName.

Thanks!
 
John,

I agree. However this is an under the hood one-time operation in an access
app written by another company. They have the calculated field built into
their app. I had to go in and make multiple changes in some names. 400
records. I did this directly in the table, to save having to open 400 forms.
The problem is he has no recalc except on record save. So unless I open 400
records and resave each one, my under the hood changes don't get reflected
in the calculated field. Thus the update query as a one-time fix.

Thanks for the syntax. I didn't have the foggiest of the correct scripting.

Can you recommend a good book or source for this sort of work? A person who
works occasionally in Access and needs a good reference tool for stuff like
this?

Thanks!

--Randy Starkey
 
John,

Tried pasting this in the update field of my update query and it wrote all
zeros in the updated field. After saving the design, it also automagically
put quotes around the field names and the UPDATE section.

I'm assuming I'm doing something wrong! <G>

Thanks!

--Randy Starkey
 
Got it to work, but I'm using the exclamation point between table and field
instead of the period. Are both acceptable?

Also...

1) could you recommend a reference book on this sort of stuff for me?
2) I now have some leading spaces on a few of these. I'm trying to figure
out syntax to test for a leading space and get rid of it if it exists. I
tried a simple find and replace in the table, but couldn't figure out the
replace syntax.

Thanks!

--Randy Starkey


John Spencer (MVP) said:
Sorry, I should have warned you to try this on a COPY of your data first.

Try the following in the Update To cell. Note the added brackets around the
fieldname and the optional addition of the Tablename. Since you only have one
table involved in this query, you should not need the table name, but it doesn't
hurt to have it.

.[FirstName] & (" " +
.[MiddleName]) & (" " + [TName].[LastName])

If that doesn't work can you copy and post the SQL of your query?

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

This might give us a hint in where we are mis-advising you or where the problem
in communication lies.

Randy said:
John,

Tried pasting this in the update field of my update query and it wrote all
zeros in the updated field. After saving the design, it also automagically
put quotes around the field names and the UPDATE section.

I'm assuming I'm doing something wrong! <G>

Thanks!

--Randy Starkey

store
the NULL.
If with
extra fields,
what
Click to expand...
Click to expand...
 
I _THINK_ the preferred method is to use the period for separating tablename and
fieldname
Click to expand...

Quite correct. The period is valid SQL syntax; ! is not SQL, it's VBA,
and refers to Collections and members of Collections.
 
Back
Top