Losing data from ODBC

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

TJT - 17 Mar 2005 22:29 GMT

We are backfeeding data from a fedex application to a access table and we are
having a problem where the value '0123456' will be sent as '123456' I have
worked with fedex to try and accomplish fixing this problem on their end, but
with zero success. I need to write a query that will search my table and if
the value does not have a 0 in front of it that it will add one. any help is
greatly appreciated.

Reply to this Message

Lee-Anne Waters - 18 Mar 2005 00:53 GMT

Hi,

you could try this.

UPDATE Table1 SET Table1.FedexNum = "0" & [Table1]![FedexNum]
WHERE (((Table1.FedexNum) Not Like "0*"));

it would only update those records where a 0 is not present and you couold
run it at the time of import.

hope this helps
Lee-Anne

Reply to this Message

Chris Nebinger - 18 Mar 2005 21:48 GMT

Or, you could use something like:

right("0000000" & FedExNum,7)

That way it will append leading zeros even for 2 digit fedex numbers.

Chris Nebinger

Reply to this Message