update query

  • Thread starter Thread starter kees
  • Start date Start date
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
 
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
 
Al,

I think it should be...
UPDATE tblTelephone SET tblTelephone.TelNo = "4" & [TelNo]
WHERE (((Len([TelNo]))=6));
 
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...
 
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,
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...
 
Back
Top