Format of value

[Tim]

Hi guys,

My utility table has a field called invoice# that's value
like following:

Invoice#
bv01256
Tyed12
yer0456

I want to bulid a query to select the field Invoice# and
have format like following:

Invoice#
01256
00012
00456

Does anyone can show me how to do it?

Thanks in advance.

Tim.

 

[Jeff Boyce]

Tim

It sounds like you want to get the numeric value of the text field, and
display it in a format with leading zeros.

Take a look at Access HELP on the Value() function and the Format property.
What you enter in the query field might look something like (actual syntax
may vary):

Expr1: Value([YourInvoiceFieldName])

Then you can right-click the column and set the Format property to something
like:

"000000"

Good luck

Jeff Boyce
<Access MVP>

 

[R. Hicks]

I think Jeff is referring to the Val() function ...

RD

 

[Jeff Boyce]

Thanks! (I did warn 'actual syntax may vary... <g>)

Jeff

 

[John Spencer (MVP)]

Unfortunately VAL will return a number based on leading numeric characters.
With the text values you gave, I think you will have to write a VBA function to
get only the numbers.

One function that might would be one below. Then yhou could apply format to that

Format(fStripToNumbersOnly(YourField),"000000")

Copy and paste the function below into a module and save that.

Public Function fStripToNumbersOnly(ByVal varText As Variant) As String
'Takes input and returns only the numbers in the input. Strips out
'all other characters. Handles nulls, dates, numbers, and strings.

Const strNumbers As String = "0123456789"
Dim strOut As String
Dim intCount As Integer

If Len(varText & "") = 0 Then
strOut = ""

Else
varText = varText & ""
For intCount = 1 To Len(varText)
If InStr(1, strNumbers, Mid(varText, intCount, 1)) > 0 Then
strOut = strOut & Mid(varText, intCount, 1)
End If
Next intCount
End If

fStripToNumbersOnly = strOut

End Function

Jeff and R. Hicks,

Thanks for respone. I tried the function Val(), but it
gave the value is zero not what I expected.

Do you have any idea?

Thanks.

Tim.

-----Original Message-----
Tim

It sounds like you want to get the numeric value of the text field, and
display it in a format with leading zeros.

Take a look at Access HELP on the Value() function and the Format property.
What you enter in the query field might look something like (actual syntax
may vary):

Expr1: Value([YourInvoiceFieldName])

Then you can right-click the column and set the Format property to something
like:

"000000"

Good luck

Jeff Boyce
<Access MVP>

.

 

[Tim]

John,

The code works exactly what I want. Thank you very much.

Tim.

-----Original Message-----
Unfortunately VAL will return a number based on leading numeric characters.
With the text values you gave, I think you will have to write a VBA function to
get only the numbers.

One function that might would be one below. Then yhou could apply format to that

Format(fStripToNumbersOnly(YourField),"000000")

Copy and paste the function below into a module and save that.

Public Function fStripToNumbersOnly(ByVal varText As Variant) As String
'Takes input and returns only the numbers in the input. Strips out
'all other characters. Handles nulls, dates, numbers, and strings.

Const strNumbers As String = "0123456789"
Dim strOut As String
Dim intCount As Integer

If Len(varText & "") = 0 Then
strOut = ""

Else
varText = varText & ""
For intCount = 1 To Len(varText)
If InStr(1, strNumbers, Mid(varText, intCount, 1)) > 0 Then
strOut = strOut & Mid(varText, intCount, 1)
End If
Next intCount
End If

fStripToNumbersOnly = strOut

End Function

Jeff and R. Hicks,

Thanks for respone. I tried the function Val(), but it
gave the value is zero not what I expected.

Do you have any idea?

Thanks.

Tim.

-----Original Message-----
Tim

It sounds like you want to get the numeric value of the text field, and
display it in a format with leading zeros.

Take a look at Access HELP on the Value() function and the Format property.
What you enter in the query field might look something like (actual syntax
may vary):

Expr1: Value([YourInvoiceFieldName])

Then you can right-click the column and set the Format property to something
like:

"000000"

Good luck

Jeff Boyce
<Access MVP>

.

.

 

[Jeff Boyce]

Thanks, John. That slipped by me... <g>

Jeff Boyce
<Access MVP>