To get a query to show a set number of values (1 Viewer)

[Sharontha]

I'm pretty new in Access and I thought i was good after my 1 year of experiance untill i saw how far advanced all you guys are.

Anyway this is my question: I have a query field that i want the numbers that i load into the table to show a set number of valuesin front or after the decimal point regardless of the value loaded into the table. EX: loaded 23 into the table but when i run the query i want it to show 23.000000.....(14 zeros) as the 23 and the 14 zeros would add upto 16 or if the number loaded into the table was .23 i want the query result to show ....00000.23 again equaling to 16 numbers. Can this be done?

Any help, any of you can provide will be greatly appreciated.
Thank you.

 

[Paul_Bricker]

You can try playing around with the Format() function. It would look like this:
Format([FieldName],"0000.000000000000")

This gives you 4 digits before the decimal place and 12 after. If you need more flexability than that, you will need a custom function to accomplish what you want.
Paul

 

[simongallop]

In your example you said whole numbers are to be followed by x zeros ie 23.0000000000000000 whilst decimal numbers are to have x leading zeros ie 000000000.23.

What happens when the number is 23.23?

If it is 14 decimal places that you want then the answer is:

Output: Format(FieldName,"#.0000000000000")

 

[Sharontha]

Harry, Thanx for your help . I'm sorry i wasn't too clear on my post. It does not have to be zero's that follow or precede it can be any value as long as it 16 in total.

 

[simongallop]

Paul's answer would be you best bet as with mine you will always have 14 decimal places whereas with his it will always be 16 numbers and a decimal point

 

[Sharontha]

Paul, like Harry was saying teh number could be 23.23 but if the number is 1.1 would this still show correctly? Again i like to thank both you gentlemen for your time.

 

[Paul_Bricker]

As I said, the best thing would be to play with the function a little to see if it does what your looking for. As for the 1.1,
Format("1.1", "0000.000000000000") would return
0001.100000000000

Basically it would always have 4 whole number digits and 12 decimal digits. If your whole number was greater than 9999, then the final number would be greater than 16 digits. If the decimal numbers ran to more than 12 digits, you would still have only 16 digits in your answer. It may be that a custom function would work better, but it's hard to tell without having a better understanding of what your numbers are and why you need 16 places.

Paul

 

[Sharontha]

Paul Thanks for all your help. I'll get back if i still have a problem, with it.

 

[Howlsta]

Hi Sharontha,

You could achieve this in a function, SEE BELOW.

I've tested this and it seems to work for each scenario.

Function test()

Dim mystring As Single
Dim mypos As Integer
Dim zerosneeded As Integer
Dim i As Integer
Dim zeros As String
Dim position As Integer

mystring = YOURFIELDVALUE
i = 0
position = Len(mystring)

If mystring >= 1 Then
mypos = InStr(mystring, ".")
If mypos = 0 Then
zerosneeded = 16 - position
Else
zerosneeded = 16 - position
End If

Else
mypos = position
zerosneeded = 16 - mypos
End If

For i = 0 To zerosneeded
zeros = zeros & "0"
Next i


If mystring >= 1 Then
MsgBox (Format$(mystring, "#." & zeros)), vbOKOnly, "msg"

Else
MsgBox (Format$(mystring, zeros & ".##############")), vbOKOnly, "msg"
End If


End Function


Rich

 

[The_Doc_Man]

If you don't want/need decimal points to align but the 16-digit field width is crucial, use

Left$( Format(value, "#.0000000000000000"), 16)

Or 17 if you don't count the decimal point in the desired width.

This fails if the number is greater than 9,999,999,999,999,999 or less than 0.000000000000001 (if I counted that right, I'm sure you get the idea.)