I have cell that contains a number with a custom format of 0000. If
there is a 1 in the cell then it displays 0001. I use the value in
that cell and when I update an Access table. When it is inserted into
the Access table it comes across as 1 instead of 0001. I have tried
changing the format in the access table but it doesn't make a
difference. I need it to go as 0001. How can I do this?

Thanks

hi.
Formating does not change the data. it only changes the way data looks on
the sheet to humans. excel still sees it as 1 and passes it to access as 1.
the only way you might do this is to format the cell to text and do the same
in access.

Regards
FSt1

"Matt" wrote:

I have cell that contains a number with a custom format of 0000. If
there is a 1 in the cell then it displays 0001. I use the value in
that cell and when I update an Access table. When it is inserted into
the Access table it comes across as 1 instead of 0001. I have tried
changing the format in the access table but it doesn't make a
difference. I need it to go as 0001. How can I do this?

Thanks

How are you updating the Access table ?

if via ADO and a recordset then:
rs.Fields("id").Value = Right("0000" & rngVal.value,4)

if via an update statement then:
sSQL = "update tblName t set t.id ='" & Right("0000" &
rngVal.value,4) & "' , t.field2 = ...." etc

Tim


"Matt" wrote in message
oups.com...
I have cell that contains a number with a custom format of 0000. If
there is a 1 in the cell then it displays 0001. I use the value in
that cell and when I update an Access table. When it is inserted into
the Access table it comes across as 1 instead of 0001. I have tried
changing the format in the access table but it doesn't make a
difference. I need it to go as 0001. How can I do this?

Thanks