SQL statement question

I am updating a table using the following SQL statement:
CurrentDb.Execute "UPDATE TimecardDetails SET work_center = work_center +
operation WHERE employee_id ='" & oldEmpId & "'"
I am combining the fields work_center and operation into the work_center
field. They are both text data types. This works fine -- but now I find that
some of the values in the operation field have only a single digit and they
are supposed to be two digits. Example: the work_center field might hold a
value of 10 and the operation field could have the value of 9 but when I run
the UPDATE statement, I'd like to make sure that the values are two digits by
adding a leading zero if necessary (09) . So when the two fields are
combined, the work_center field would then hold a value of 1009 for example.
I tried:
CurrentDb.Execute "UPDATE TimecardDetails SET work_center = work_center +
format(operation,"00") WHERE employee_id ='" & oldEmpId & "'"
but that doesn't work.
Any idea if this can be done in the SQL statement??
Any help would be very much appreciated! Thanks!
.

Relevant Pages

  • Re: Joins
    ... FROM TableA INNER JOIN TableB ... The ext_zip attribiute will always have 11 digits. ... is there a way to feed an SQL statement the ext_zip records from Table ...
    (microsoft.public.access.queries)
  • Re: Update Query and Timestamp
    ... It seems there is nothing wrong in your SQL statement itself, ... Public Sub Foo() ... Why are my other fields not updating? ... UPDATE query to a SELECT query, ...
    (microsoft.public.access.queries)
  • Re: Subform within Subform on Main Form data refresh
    ... When you say that you're updating the subform's query... ... Setting it directly to an SQL statement? ... > subform is still not updating with the new data. ...
    (microsoft.public.access.formscoding)
  • Re: Converting a number to Text
    ... I'm having a tough time with my SQL statement when I try to join two ... your text is not very long, if all you expect to do with it is to compare it ... comparing different data types (and in particular, ...
    (comp.databases.informix)
  • Re: Is there a maximum size to a Python program?
    ... that the replication may only require updating one or more fields, ... problem with a generating a single SQL statement to cover all requests. ... the other for subsequent updates. ... flexibility in runtime generation of the code. ...
    (comp.lang.python)
Quantcast