combo box only returns 2 decimal places 5

[jeffmoore]

I have a combo box that is getting it's data form a table.
the data in the table is a double and has data out to 3 decimal places. the data format in the combo's query is set for fixed 3 decimal places , the control is set for fixed 3 decimal places ... still when the form runs it displays only 2 decimal places...
TIA
Jeff

 

[Vie]

Jeff,

Try changing the format from Fixed to General Number.

HTH
Vie

 

[TheAceMan1]

How are ya jeffmoore . . .

Vie has the right idea just the wrong format. The general format displays the number as it was input. What you need is the Fixed Format.

TheAceMan

 

[Vie]

Jeff,

Wow. I see what you're saying. No matter if the query is Fixed and Decimal 3, the combobox rounds to decimal 2 even when it too is Fixed, Decimal 3. Baffling.

Vie

 

[TheAceMan1]

jeffmoore! . . . . Vie . . . . .

I setup a table with two Numeric fields having the following properties:

Field Size as Double
Format as Fixed
Decimal Places as three

On opening the table and entering some data, I got what I expected; all entries have three decimals no matter what!

Then I went on to query design and drag-dropped the fields. Did nothing else. On running the query I got what I expected; all entries have three decimals no matter what! This shows properties were inherited properly.

Next I created a form with the RecordSource based on the table then on the query then on SQL. On opening and entering data I got what I expected; all entries have three decimals no matter what! Again inheritance is working properly.

The only suggestion I can offer here is to set the properties in the table, then reinherit the RecordSource & Numeric Controls for the form.

If your using the table as the RecordSource, to reinherit (should'nt be necessary): delete it from the RecordSource, save the form, then select the table again.

If you using a query, to reinherit: in query design, delete the numeric fields, then drag-drop the same back from the field list.

Wether the RecordSource is table or query or SQL, you still have to reinherit the numeric controls on the form. To do so, delete them, then drag-drop the same from the field list.

Unless something else is wrong, I can't understand why the same results are'nt obtained. Let me know . . . ;-)

TheAceMan

 

[Vie]

TheAceMan1 and jeffmoore (sorry if I'm hijacking your thread here - I have a form in another db where I'm going to have do this same thing once I get round to it),

In my experiment yesterday, the table and the query both show 3 decimals. It's the combo box that doesn't, even when it's RowSource is set to the query.

Anyway, I redid everything this morning according to your suggestion and still the combo box shows 2 decimal places. I put a textbox on the form and the textbox shows 3.

Is there something about combo boxes - are they restricted to only 2 decimal places? I couldn't find anything about the topic on Microsft's website.

hmmmm.....


Vie

 

[jeffmoore]

I'll try your segestions on monday thanks

 

[RoyVidar]

I think the combos and lists interpret the contents as text. One workaround might be thru formatting. Here, formatting the field in the query

[tt]MyField: Format([YourDate],"0.000")[/tt]

to "force" it into a string containing three decimals.

Sometimes when I have to display both left and right aligned text in a combo or list, I "add a little more fun" by adding some extra spaces to make it "right aligned" (using a font like for instance Courier New):

[tt]MyField: Space(len(Format([YourDate],"0.000"))) & Format([YourDate],"0.000")[/tt]

- this isn't tested much, when using this kind of formatting, I usually populate the combo/list thru VBA...

- if the number from the combo is supposed to be used in cacluations, I'd recommend appropriate conversion

Roy-Vidar

 

[TheAceMan1]

To All . . . .

Thanks to the lead provided by RoyVidar and close to 3hrs of troubleshooting & testing, Ive come up with a direct resolution. Using a query or SQL for the RowSource, in query desigh view add the following custom field:

fmtNum:Format([YourNumberFieldName],"0.000")

Use this field as the display field for the combobox.

My testing (close to 3hrs) revealed that this is not a problem nor a bug. Its an explicit function of the ComboBox Control. What it does is take priority in formatting numeric fields. The format is General with two Decimal Places. (Bear in mind that despite this, the full input value is available in VBA).

The main problem is you simply should'nt reformat the text portion, with VBA for example. If you did, the reformat would'nt match the list format and the NotInList error (if enabled) would be raised. This is what prompted me to try a custom field.

Special thanks to RoyVidar for his insight, for this is certainly one for the books! ;-)



TheAceMan

 

[jeffmoore]

Thank you very much for all the time spent researching this problem. I'll try your solutions on monday.
Again Thank you
Jeff

 

[RoyVidar]

Thanx, all!

Nice that this "old tweak" could prove useful for others too. I must confess to being a bit too lazy and QAD (Quick and Dirty ;-)) to use time on figuring out why things doesn't work the way I'd like them to, I instead concentrate on workarounds and keyword searches. Funny though, after joining TT, I've found that some of my "workarounds" weren't needed at all, but that's the joy of TT membership, continuing to learn new things making both you apps and yourself more efficient. Haven't yet found anything better than using the format function for this in lists/combos, though...

Bear in mind that if you use this formatting, forcing the number to be a string containing 3 decimals, the original number (containing more than three decimals?) will not be available from the combo - it would need to be extracted from the table, or with a tweak of the combo, adding another hidden column to it containing the unformatted table field.

The justifying, isn't really the topic of this thread, but thought I could add it, and of course also added a typo;-)

One would need to subtract the length of the formatted output from some value (or one could calculate max length of the output some way...) here using 15:

[tt]MyField: Space([blue]15-[/blue]len(Format([YourDate],"0.000"))) & Format([YourDate],"0.000")[/tt]

Last time I searched for something on this kind of justifying (back in 2000), I didn't find anything, so I've been using the space function for that. Since then, I guess I've improved on my searching skills, and found something on Stephen Lebans site: JustiCombo.

Unfortunately, it doesn't address the challenge concerning the number of decimals, that would still need the "format tweak", but it at least it offers easy to use center and right justifying of columns.

Roy-Vidar

 

[TheAceMan1]

How are ya RoyVidar . . . . .

As a quick point . . . you said: Bear in mind that if you use this formatting, forcing the number to be a string containing 3 decimals, the original number (containing more than three decimals?) will not be available from the combo

You are correct in this, but bear in mind two things.

1) The aditional field is for display purposes only! We wanna see what we wanna see!

2) The origional numeric field is where the programmer will get the actual data(the field is a part of the RowSource query . . . it has to be), using the combobox Column Property.

I bow on one knee to your knowledge . . . . . .

TheAceMan

 

[jeffmoore]

WoooHooo!! I used "TheAceMan1"'s formating suggestion works like a champ.
Again ... Thanks for all the time and effort...
Jeff