Re: Price Data Formatting

Hi Mike.

Thanks for your comments.

Yes, I understand how the Format() function works. The exercise here,
however, is that the data format is NOT KNOWN during design time but is
based on the format of the data loaded during runtime.

As I posted elsewhere, my original solution to this problem was to read in a
sampling of the data to determine where the decimal point is located.

Then I would use that to select the correct sPattern() array element that
contains a matching pattern.

So sPattern(3) might have "#.000" as the string within so that I can use it
as...

sPrice = Format(sngPriceData, sPattern(iDecVal))

Usually when I ask a question on this forum someone ALWAYS has a better way
to do what I've done. So I ask in order to learn from experts like yourself.
I've come a long way because of you and others here (Rick, Steve, Larry,
(fill in the blank), etc. etc.).

Steve posted an alternative to my bulky way of doing this. Instead of
creating the array sPattern() to hold the different patterns, I can simply
use the String() function to concate "0" to a standard "0." pattern, or
"#.".

What do you think about that approach?

Also, is looping through some of the data and looking for the decimal point
location the correct/best approach to determining the existing format as I
have done? Or is there a easier/better way?

Thanks Mike.

Webbiz





"Mike Williams" <gagamomo@xxxxxxxxxxx> wrote in message
news:5cad10a4-e66a-46c6-9d5d-113b479fd6ba@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On 18 Aug, 22:37, "Webbiz" <nore...@xxxxxxx> wrote:

What I have been doing now is loading the data and noting
the maximum digits found right of the decimal and left of the
decimal. How I do this is through a LOOP, using INSTR

I can't seem to get answers through the MS server at the moment so I'm
posting this from a Google Groups account. Anyone else having this
problem at the moment? Anyway, you don't need to do any of that stuff.
The Format function will do it all for you. Try this:

Private Sub Command1_Click()
Dim j As Single
j = 12.34
Print Format(j, "0.0000")
j = 0.345
Print Format(j, "0.0000")
End Sub

In the line Format(j, "0.0000") the first zero tells VB that you want
it to insert a leading zero before the decimal point if the number
does not have a "whole" part. Otherwise, if you did not want the
leading zero you would instead use Format(j, "#.0000"). The second
four zeros simply tell VB how many decimal digits you want to display
(four in this case) and VB will then add sufficient zeros to the
output to make it have the correct number of decimal places.

In this case the example is "hard coded" to format to four decimal
places, specified by the four zeros after the decimal point in the
line at(j, "#.0000"). However, if you do not know at design time how
many decimal places you require you can "build" the string "#.0000" at
runtime, inserting the appropriate number of zeros into the Format
instruction, so that you could for example build the string "#.000" if
your code decided at runtime that it wanted three decimal places. If
that's what you need to do and if you want any help with it then post
again.

When you print out the results of course, as the above simple example
does, the output is printed left aligned. If you instead want it to be
right aligned (as is often the case with such numbers) then you'll
need to add more code, but I haven't included any such code in this
example because much depends on how you are outputting the result
(printing it to a picturebox or a printer or something or perhaps
displaying it in some kind of control such as a ListBox or a Label or
whatever)

Mike





.

Relevant Pages

  • Re: DLookup Problem
    ... zeros in the data. ... not what you see in the textbox based on your Format settings. ... processes, controls, and queries consistent with this. ... > been able to successfully display the "0" is if I place "000" in the ...
    (microsoft.public.access.macros)
  • Re: Format a string into a decimal and keep leading zeros.
    ... The leading zeros are formatting of the output. ... will need to apply a format mask to the display field, ... >the leading zeros to get into the database. ...
    (comp.databases.progress)
  • Re: Stop excel from dropping the 0 in the beginning of a number?
    ... I format the Excel ISBN column to the 10 zeros, ... You can do the entire thing at once with a macro, ...
    (microsoft.public.excel.setup)
  • Re: How do I stop Excel from dropping leading zeros?
    ... Data to import into Excel. ... valid leading zeros and the values are not fixed width. ... Is there a way to disable auto format? ... I also need to drop the last digit (the check ...
    (microsoft.public.excel.misc)
  • RE: Help Importing to Access 2003 SSNs from Excel 2003
    ... zeros and hyphens. ... There doesn't seem to be a way to format the field ... You would think that Access and Excel would compliment each other on the ... Social Security number format, but apparently not. ...
    (microsoft.public.access.externaldata)
Loading