SQL statement question

  • Thread starter Thread starter Simon
  • Start date Start date
S

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!
 
Thanks, but that doesn't seem to work either.

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


Simon said:
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!
Click to expand...
Click to expand...
 
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!



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


Simon said:
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!
Click to expand...
Click to expand...
 
Back
Top