Leading Zero and Decimal Point

[didiomm]

Hi All:



Have a question about getting a query to export both leading zero and decimal point that have been inputted in a textbox. I have the field data type set to text, so the leading zeros do show. I also set the field format to 0000000.00. And I set the field size to 10. The 10 would consist of 7 digits, the decimal point, and 2 trailing digits.

However, the output file for this query only shows the leading zeros and the value inputted.

Currently:

User inputs: 0000015.37
Output file: 000001537

So, I need to retain what the user inputs. Ideally if the user inputs 15.37 and the output file could do this: 0000015.37 that would be optimal.

The output file is going into a totally different system that needs this formatted just so

Any suggestions would be greatly appreciated!

[JoeM]

However, the output file for this query only shows the leading zeros and the value inputted.

If you you open the query, what does the value look like?
What format are your exporting the data to?
Do you have an Export Specification you are using?
How are you viewing the output?

[didiomm]

If you you open the query, what does the value look like? The value looks like this in the query: 000001537
What format are your exporting the data to? Text file format
Do you have an Export Specification you are using? Yes, fixed width delimit with width of 10 for this particular value
How are you viewing the output? Once the query is exported (using ImportExportText macro) it is viewed in notepad

[rpeare]

You can leave your data entry field as a number, on any reports, queries, exports (output) by using the format command

format([NumberField], "0000000.000")

[JoeM]

If you you open the query, what does the value look like? The value looks like this in the query: 000001537

So this confirms that the issue has nothing at all do with your export, but rather something before that.
Either, how you are presenting it in your query, or how you are capturing it from the text box.

[Bulzie]

Will there always be 2 decimal numbers input by the user or could they just enter 15 and you would want that to show as 0000015.00 or 15.1 and you want it to be 00000015.10?

[didiomm]

@Bulzie-always show the 2 decimal numbers.
@JoeM-looks like it, I've played around with the input mask for both the textbox and the data table feeding the form that performs the output, but I was still losing the decimal point (in both the query and the text file export)
@rpeare-that seems to work, however when the user navigates away from the record they updated and then returns to it, the decimal point goes away from the text box. Need the data format to not change regardless of the navigating to and from it. I am still working with this solution, though

Thank you all for your input, still working/battling this but any more thoughts and suggestions is still welcomed.

[Bulzie]

1. Do you or users need to see the leading zeros on the form or can they just see the value they input and you add the zeros when you export the field?
2. Are there any limits on the input values they enter such as will it be no more then 2 digits before the decimal or could they really enter up to 7 actual digits before the decimal?

[didiomm]

Hi Bulzie:

The users need to see the leading zeros. It wouldn't matter if they input just the value (like 15.37) and then the leading zeros populate after they hit "enter" or "tab" or if they input all 10 characters (like 0000015.37), but...the zeros and decimal must be there at some point for them to view after their update

I can not manually input the leading zeros myself as this DB is setup to export to a directory to be picked up by a different system. Once all the records are updated by the user it is programmatically exported to that directory.

There should always be 7 digits before the decimal and there should always be 2 digits after the decimal. Most records will only have 2 to 4 digit values before the decimal, but there *could* be data for all 7 digits.

Hope that makes sense

And thanks!

[John_G]

I've played around with the input mask for both the textbox and the data table feeding the form that performs the output

Your problem might be there - if you use a decimal in the input mask, you have to explicitly indicate that the literal character (in this case the decimal point) is to be stored with the data that the user enters. The option is the second part of the input mask - check the help file for details.

[Bulzie]

I'd say make field text. Input mask 0000000.00

It should make them enter all the values and show the decimal on the form but not store it in the table. When you go to export the data, make a query and for this field, add in the decimal.

ExportValue: Left([MyValue],7) & "." & Right([MyValue],2)

[didiomm]

I ended up taking out the input mask for the textbox.

In the table design for the field,

• Field size 10
• "Format" 0000000.00
• "Input Mask" as 0000000.00;0;*

When I run the export, the decimals and the spacing integrity are retained. I created a label field with a note about the format required for the field. If they fail to go by it, the other system that picks it up will kick out the record to be reworked. I was trying to prevent doing that, but the short timeframe for this project kinda took it out of my hands at this point.

Thank you very much to each one of you that tried to help.

[Bulzie]

If you wanted to do some quick validation before saving to try to catch any issues, you could check the length and if there is a decimal at the right spot. You can run this in the BeforeUpdate on he Form or AferUpdate on the Control.

If Len(Me.MyValue) <> 10 then

Msgbox "Incorrect value entered."
Me.MyValue.SetFocus
End

Else

If Mid(Me.MyValue, 8,1) <> "." then

Msgbox "Incorrect value entered."
Me.MyValue.SetFocus

End

End If

End If

[didiomm]

Bulzie:

This is great, and it works very well!

Thank you!