SQL statement question

[Simon]

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!

 

[KARL DEWEY]

Try this --
[work_center] = [work_center] & Right("0" & [operation], 2)

 

[Simon]

Thanks, but that doesn't seem to work either.

Try this --
[work_center] = [work_center] & Right("0" & [operation], 2)
--
KARL DEWEY
Build a little - Test a little

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!

 

[Simon]

I changed the syntax slightly and now it does work.
(+ instead of & and single quotes inside the Right function)

[work_center] = [work_center] + Right('0' & [operation], 2)

Thanks Karl, I appreciate the help!

Try this --
[work_center] = [work_center] & Right("0" & [operation], 2)
--
KARL DEWEY
Build a little - Test a little

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!