I am performing a collection of values from different cells on a
worksheet and inserting them into an Access database. When I update
an Access database with the value in C2, which in this case is the
number 2, I need to it to go into the Access record as "0002". The
field in Access is a Text field.

Is there some type of Format function that I can use to push the C2
value into Access the way I need it?

THanks

On Jun 29, 9:38 am, Matt wrote:
I am performing a collection of values from different cells on a
worksheet and inserting them into an Access database. When I update
an Access database with the value in C2, which in this case is the
number 2, I need to it to go into the Access record as "0002". The
field in Access is a Text field.

Is there some type of Format function that I can use to push the C2
value into Access the way I need it?

THanks

Hi Mat
You can use a custom format, (Format Cells go to the Number tab and
select Custom), and insert this following 0000#. However this will not
change the value, only what is displayed. To change the actual value,
to match what is being displayed, will need a small VBA macro. I have
it if you need it, let me know.
Cheers
Dave

On Jun 29, 9:43 am, Limey wrote:
On Jun 29, 9:38 am, Matt wrote:

I am performing a collection of values from different cells on a
worksheet and inserting them into an Access database. When I update
an Access database with the value in C2, which in this case is the
number 2, I need to it to go into the Access record as "0002". The
field in Access is a Text field.

Is there some type of Format function that I can use to push the C2
value into Access the way I need it?

THanks

Hi Mat
You can use a custom format, (Format Cells go to the Number tab and
select Custom), and insert this following 0000#. However this will not
change the value, only what is displayed. To change the actual value,
to match what is being displayed, will need a small VBA macro. I have
it if you need it, let me know.
Cheers
Dave

I have tried using the custom format, and you're right, it only
changes the appearance not the value. If you have that macro I would
appreciate taking a look at it.

Thanks

On Jun 29, 9:47 am, Matt wrote:
On Jun 29, 9:43 am, Limey wrote:



On Jun 29, 9:38 am, Matt wrote:

I am performing a collection of values from different cells on a
worksheet and inserting them into an Access database. When I update
an Access database with the value in C2, which in this case is the
number 2, I need to it to go into the Access record as "0002". The
field in Access is a Text field.

Is there some type of Format function that I can use to push the C2
value into Access the way I need it?

THanks

Hi Mat
You can use a custom format, (Format Cells go to the Number tab and
select Custom), and insert this following 0000#. However this will not
change the value, only what is displayed. To change the actual value,
to match what is being displayed, will need a small VBA macro. I have
it if you need it, let me know.
Cheers
Dave

I have tried using the custom format, and you're right, it only
changes the appearance not the value. If you have that macro I would
appreciate taking a look at it.

Thanks

Hi Mat,

I'm sure there is a much more efficient way of doing this, but this
works for me. Hope it helps you mate.

Sub InsertZeros()
Dim x As Integer
Dim Rng As Range

Selection.NumberFormat = "@"
For Each Rng In Selection
x = Len(ActiveCell)
Select Case x
Case Is = 1
Rng.Value = "0000" & Rng
Case Is = 2
Rng.Value = "000" & Rng
Case Is = 3
Rng.Value = "00" & Rng
Case Is = 4
Rng.Value = "0" & Rng
Case Is = 5
Rng.Value = Rng
End Select
ActiveCell.Offset(1, 0).Activate
Next


End Sub

On Jun 29, 10:07 am, Limey wrote:
On Jun 29, 9:47 am, Matt wrote:





On Jun 29, 9:43 am, Limey wrote:

On Jun 29, 9:38 am, Matt wrote:

I am performing a collection of values from different cells on a
worksheet and inserting them into an Access database. When I update
an Access database with the value in C2, which in this case is the
number 2, I need to it to go into the Access record as "0002". The
field in Access is a Text field.

Is there some type of Format function that I can use to push the C2
value into Access the way I need it?

THanks

Hi Mat
You can use a custom format, (Format Cells go to the Number tab and
select Custom), and insert this following 0000#. However this will not
change the value, only what is displayed. To change the actual value,
to match what is being displayed, will need a small VBA macro. I have
it if you need it, let me know.
Cheers
Dave

I have tried using the custom format, and you're right, it only
changes the appearance not the value. If you have that macro I would
appreciate taking a look at it.

Thanks

Hi Mat,

I'm sure there is a much more efficient way of doing this, but this
works for me. Hope it helps you mate.

Sub InsertZeros()
Dim x As Integer
Dim Rng As Range

Selection.NumberFormat = "@"
For Each Rng In Selection
x = Len(ActiveCell)
Select Case x
Case Is = 1
Rng.Value = "0000" & Rng
Case Is = 2
Rng.Value = "000" & Rng
Case Is = 3
Rng.Value = "00" & Rng
Case Is = 4
Rng.Value = "0" & Rng
Case Is = 5
Rng.Value = Rng
End Select
ActiveCell.Offset(1, 0).Activate
Next

End Sub- Hide quoted text -

- Show quoted text -

It worked perfect! Thanks for the help!

On Jun 29, 10:18 am, Matt wrote:
On Jun 29, 10:07 am, Limey wrote:



On Jun 29, 9:47 am, Matt wrote:

On Jun 29, 9:43 am, Limey wrote:

On Jun 29, 9:38 am, Matt wrote:

I am performing a collection of values from different cells on a
worksheet and inserting them into an Access database. When I update
an Access database with the value in C2, which in this case is the
number 2, I need to it to go into the Access record as "0002". The
field in Access is a Text field.

Is there some type of Format function that I can use to push the C2
value into Access the way I need it?

THanks

Hi Mat
You can use a custom format, (Format Cells go to the Number tab and
select Custom), and insert this following 0000#. However this will not
change the value, only what is displayed. To change the actual value,
to match what is being displayed, will need a small VBA macro. I have
it if you need it, let me know.
Cheers
Dave

I have tried using the custom format, and you're right, it only
changes the appearance not the value. If you have that macro I would
appreciate taking a look at it.

Thanks

Hi Mat,

I'm sure there is a much more efficient way of doing this, but this
works for me. Hope it helps you mate.

Sub InsertZeros()
Dim x As Integer
Dim Rng As Range

Selection.NumberFormat = "@"
For Each Rng In Selection
x = Len(ActiveCell)
Select Case x
Case Is = 1
Rng.Value = "0000" & Rng
Case Is = 2
Rng.Value = "000" & Rng
Case Is = 3
Rng.Value = "00" & Rng
Case Is = 4
Rng.Value = "0" & Rng
Case Is = 5
Rng.Value = Rng
End Select
ActiveCell.Offset(1, 0).Activate
Next

End Sub- Hide quoted text -

- Show quoted text -

It worked perfect! Thanks for the help!

Cool! Glad to help mate. :)