UPDATE a new field with the combination of thre others

[justagrunt]

Hi,
I tried an UPDATE query on an existing table to populate a new field with
the following;

"Tag"= [DevicetagID] & "-" & Format([TagID1],"0000") & Format([TagID],"00")

but the result was nothing.

The existing table has 3000 entries.
The fields in question that have data that is relevent are;


DeviceTagID = Text
TagID1 = number
TagID = number

The new field
Tag = Text

How can this new filed be updated with the data from the other three?
When this field is populated it becomes unique and will assist in a one to
many relationship.

I tried to write a macro but got lost;
Dim SQL As String
DoCmd.SetWarnings False
SQL = "UPDATE [ProjectEntity]" & _
"SET [Tag] = [DevicetagID] & "-" & Format([TagID1],"0000") &
Format([TagID],"00")'"
"WHERE ??????????

DoCmd.RunSQL SQL

 

[Douglas J. Steele]

Because you're trying to put quotes inside a quoted string, you need to
double them.

From what I can see, you don't need a WHERE clause, so try:

SQL = "UPDATE [ProjectEntity]" & _
"SET [Tag] = [DevicetagID] & ""-"" & " & _
"Format([TagID1],""0000"") & Format([TagID],""00"")"

 

[John Spencer]

Pardon me, but Douglas Steele missed a space after [ProjectEntity].

SQL = "UPDATE [ProjectEntity] " & _
"SET [Tag] = [DevicetagID] & ""-"" & " & _
"Format([TagID1],""0000"") & Format([TagID],""00"")"

My personal preference is to always add the space at the beginning of
the continued line. That way it is a lot easier to check that it is
there. And SQL doesn't care if I've added one at the end and one at the
beginning.

SQL = "UPDATE [ProjectEntity] " & _
" SET [Tag] = [DevicetagID] & ""-"" & " & _
" Format([TagID1],""0000"") & Format([TagID],""00"")"

 

[Douglas J. Steele]

Dang! Thanks, John.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Pardon me, but Douglas Steele missed a space after [ProjectEntity].

SQL = "UPDATE [ProjectEntity] " & _
"SET [Tag] = [DevicetagID] & ""-"" & " & _
"Format([TagID1],""0000"") & Format([TagID],""00"")"

My personal preference is to always add the space at the beginning of the
continued line. That way it is a lot easier to check that it is there.
And SQL doesn't care if I've added one at the end and one at the
beginning.

SQL = "UPDATE [ProjectEntity] " & _
" SET [Tag] = [DevicetagID] & ""-"" & " & _
" Format([TagID1],""0000"") & Format([TagID],""00"")"
---
John Spencer
Access MVP 2001-2005, 2007

Because you're trying to put quotes inside a quoted string, you need to
double them.

From what I can see, you don't need a WHERE clause, so try:

SQL = "UPDATE [ProjectEntity]" & _
"SET [Tag] = [DevicetagID] & ""-"" & " & _
"Format([TagID1],""0000"") & Format([TagID],""00"")"