Formatting numbers

[wtrimble]

I have a field called "sample number" where I need the number entered to be two digits (e.g. 01, 09, 43...) and I also need the number to be a "number" because I need to perform simple calculations with it. However when a single digit is placed in the field, it shows up as "1" and not "01". I need to dlookup this number using left() or right() and it has to be two digits long. Anyone know how to do make the number two digits?... can't figure it out with using format and field size and what not.

Thanks

 

[DCrake]

You can still store the number as a number, but have a query that formats the number the way you want.

Format([YourNumberField],"00")

and refer to this in your form

David

 

[wtrimble]

I need to lookup several rields within a record. Is there anyway to format it in the table, I hate having extra queries if I don't need them

 

[MSAccessRookie]

I need to lookup several rields within a record. Is there anyway to format it in the table, I hate having extra queries if I don't need them

What DCrake says is probably the best thing to do in your case, since I am unaware of a way to do what you want. We may not always want them (or even like them), but Queries turn out to be valuable allies in database programming. Besides, the overhead of formatting the value in a Query is minimal.

 

[wtrimble]

Damn.... I may have to restructure my whole database. I need to have users input "project numbers" in one table which need to be formatted as such: 2009/033/REG. Than in a different form, all of the project numbers in the previous table are referenced in a combo box. In that form a "sample number" is entered in, in a text box. Once a sample number is added to a project number, the format needs to be: 2009/033/REG/02 or 2009/033/REG/22.... where there can be multiple "sample numbers" to a project number.

Right now I have the project number split into three fields on the first table and the entry form:
1. Project Year: 2009
2. Project Number: 033
3. Type: REG
and a query combines with "/" to make the final project number. In that query I can format project number: 33 to be "033". However following that same process to produce the final sample number: 2009/033/REG/02.... I referenced the query to get the final project and combine it with "/" and the sample number. But when it displays the full project number, it comes up as: 2009/33/REG/02, and doesn't keep the "000" format for the middle project number.

Maybe I need to combine the project number and sample number all at once with the correct formatting???

 

[wtrimble]

I figured it out. Made on query where I combine the correctly formatted project number. Than in another query I again combined the project number with the correctly formatted sample number. Just had to combine the project number twice to get it.

Thanks guys

 

[Poppa Smurf]

Use could use CINT or CLONG.