update query

K

kees

I have a table with a record telephone number where I have to concatenate an
extra digit depending on a condition. I have succeeded to make the
expression.
expr2:<dbname>.<phonenr>
Expr3:IFF(LEN([expr2]=6,"4"&[expr2]&,[expr2]
This means if I have a phonenumber comprising 6 digits I concatenate a 4
infront of it.
Now I want to make an update query to alter the record and write it back to
the same phone record.
What is the syntax for an update query?
Regards,
Bart
 
A

Al Camp

Kees,
Rule #1... Back up your table!
For Update queries, I always use the query design grid. Much safer that
way, and I can expirement before I commit to the real update.
Create an update query and place your [PhoneNo] on the grid.
Set Update To: "4" & [PhoneNo]
Create a calculated field on the grid...
Len([PhoneNo])
Set Criteria: = 6
That should do it...

My SQL looked like this...
UPDATE tblTelephone SET tblTelephone.TelNo = "4" & "TelNo"
WHERE (((Len([TelNo]))=6));

hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions
 
S

Steve Schapel

Al,

I think it should be...
UPDATE tblTelephone SET tblTelephone.TelNo = "4" & [TelNo]
WHERE (((Len([TelNo]))=6));
 
A

Al Camp

Steve,
Not sure if you'll chcek back to this post... I've been out for a few
days.
Took me 5 minutes to "finger" out what the difference between your code
and mine... the "TelNo" vs [TelNo]

Hmmm... since my TelNo is a text field, it should concatenate with the
"4" as [TelNo]. I would think using
"4" & "TelNo"
would yield "4TelNo" instead of "4603-555-1234"
Am I missing something? (my update worked)
Thanks

Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions



Steve Schapel said:
Al,

I think it should be...
UPDATE tblTelephone SET tblTelephone.TelNo = "4" & [TelNo]
WHERE (((Len([TelNo]))=6));

--
Steve Schapel, Microsoft Access MVP


Al said:
Kees,
Rule #1... Back up your table!
For Update queries, I always use the query design grid. Much safer
that way, and I can expirement before I commit to the real update.
Create an update query and place your [PhoneNo] on the grid.
Set Update To: "4" & [PhoneNo]
Create a calculated field on the grid...
Len([PhoneNo])
Set Criteria: = 6
That should do it...

My SQL looked like this...
UPDATE tblTelephone SET tblTelephone.TelNo = "4" & "TelNo"
WHERE (((Len([TelNo]))=6));
Click to expand...
Click to expand...
 
S

Steve Schapel

Al,

I'm not sure. Maybe I'm missing something. Yes, agree that "4" &
"TelNo" will yield "4TelNo", and "4" & [TelNo] will yield
"4603-555-1234", which I assume is what Kees wanted. I thought it was
your post that indicated "4" & "TelNo" and mine that indicated "4" &
[TelNo], no?

Sorry, I just butted in here in an effort to avoid Kees getting
confused, and now it looks like we all are ;-)
 
A

Al Camp

Steve,
No problem... I also picked up on Kees "TelNo" in his original post, but
as I developed my response, that got left off.
Thanks... catch you around the NG,
Al Camp

Steve Schapel said:
Al,

I'm not sure. Maybe I'm missing something. Yes, agree that "4" & "TelNo"
will yield "4TelNo", and "4" & [TelNo] will yield "4603-555-1234", which I
assume is what Kees wanted. I thought it was your post that indicated "4"
& "TelNo" and mine that indicated "4" & [TelNo], no?

Sorry, I just butted in here in an effort to avoid Kees getting confused,
and now it looks like we all are ;-)

--
Steve Schapel, Microsoft Access MVP

Al said:
Steve,
Not sure if you'll chcek back to this post... I've been out for a few
days.
Took me 5 minutes to "finger" out what the difference between your
code and mine... the "TelNo" vs [TelNo]

Hmmm... since my TelNo is a text field, it should concatenate with
the "4" as [TelNo]. I would think using
"4" & "TelNo"
would yield "4TelNo" instead of "4603-555-1234"
Am I missing something? (my update worked)
Thanks
Click to expand...
Click to expand...
 
Top