Drop a Zero form the invoice Number

[jmiller79]

I get a flat file form a customer, I import the file into a MS Access table, but somehow i need to drop the first zero form the invoice number, any help would be great

 

[traingamer]

try an update query

right([invoicenumber],len([invoicenumber]) - 1)

(if all have a leading zero)

traingamer

 

[PHV]

Take a look at the Mid function:
newInvoiceNumber = Mid(oldInvoiceNumber, 2)
Or more sophisticated:
If Left(oldInvoiceNumber, 1) = "0" Then
newInvoiceNumber = Mid(oldInvoiceNumber, 2)
Else
newInvoiceNumber = oldInvoiceNumber
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jmiller79]

Not working, I try to do n update query but no luck, The invoice number has letters and numbers, is that why, Any help would be great , thanks

 

[PHV]

UPDATE yourTable
SET InvoiceNumber=Left([InvoiceNumber],InStr([InvoiceNumber],"0")-1) & Mid([InvoiceNumber],InStr([InvoiceNumber],"0")+1)
WHERE InStr([InvoiceNumber],"0") > 0

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SkipVought]

Give us several examples of your INPUT data. Ones that work and ones that don't.

What's the format length of the receiving field?

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}