Update query strips leading zero from text

[Todd Lemen]

Access 11.5614.5703
Jet 4.0.8015.0
VBA Retail 6.4.8869, Verision 9969
Executes:
Dim strOldInv As String
strOldInv = Me.VendorInvoice
DoCmd.RunSQL ("UPDATE VendorInvoiceHoldChange SET
VendorInvoiceHoldChange.VendorInvoice = " & strOldInv
& ";")
**************************
1. form control "VendorInvoice" is unbound, no format
defined.
2. Table "VendorInvoiceHoldChange" defines
field "VendorInvoice" as Text, field size = 20
3. Variable strOldInv reads "0119454"
4. After command executes, VendorInvoice field in
VendorInvoiceHoldChange table reads "119545". The
leading zero is removed.

How do I prevent stipping the leading zero from this text?
Any suggestions welcome.

TL

 

[John Viescas]

If VendorInvoice in the table is text, then do:

DoCmd.RunSQL ("UPDATE VendorInvoiceHoldChange SET
VendorInvoiceHoldChange.VendorInvoice = '" & strOldInv
& "';")

Note that I added quotes around the literal. Without that, JET takes the
numeric value of the literal (so it drops the leading zero) and then
converts it to text to do the update.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

 

[Todd Lemen]

Of course... Thanks, John!!

-----Original Message-----
If VendorInvoice in the table is text, then do:

DoCmd.RunSQL ("UPDATE VendorInvoiceHoldChange SET
VendorInvoiceHoldChange.VendorInvoice = '" & strOldInv
& "';")

Note that I added quotes around the literal. Without that, JET takes the
numeric value of the literal (so it drops the leading zero) and then
converts it to text to do the update.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)



.