Formatting in ACCESS 2003

[MNNovice]

I have a field called MusicCategoryID in a table called tblCategory. It's a number field. MusicCategoryID is a primary key and auto numbered.

I am keeping track of this field in a form called frmCDDetails which is pulling data from a table called tblCDDetails.

when using the Count or DCount function, I get the result that's shown as 41 but I would like to show as 041.

Is it possible to do? Any help anyone can provide will be greatly appreciated. I am a Novice...Thanks.

[hyperpau]

Yes. BUt you can't assign it back to that AutoNumber Field.
only on the Function result of your DCount

Example:

Dim intCount as Integer

intCount = DCount ("MusicCategoryID", "tblCDDetails")
intCount = Format(intCount, "000")

By the way, I think I answered one of your other posts as well.
If it resolves it, please let us know. thanks.

[missinglinq]

And that code won't produce the desired effect either! You've Dimmed intCount as an Integer

Dim intCount As Integer

you cannot then use the statement

intCount = Format(intCount, "000")

to assign a string value to an Integer, and that's what Format() returns, a string value.

You'd need to add a second variable, a string and do something like this:

Dim intCount As Integer
Dim strCount As String
 
intCount = DCount("MusicCategoryID", "tblCDDetails")
strCount = Format(intCount, "000")

and then use strCount variable for whatever display purpose you need.

I hope that by "keeping track of this field (MusicCategoryID)" you mean doing the tally we're talking about, and not actually displaying it on your form. Autonumbers should really never been seen by the end users; their purpose in life is to provide a unique identifier for each record. If you want an ID number to display for a each record, and want it to be consecutive and without gaps, you need to use an auto-incrementing hack to generate these numbers.

Welcome to Bytes!

Linq ;0)>

[hyperpau]

Right. I overlooked that one. :)

And that code won't produce the desired effect either! You've Dimmed intCount as an Integer

Dim intCount As Integer

you cannot then use the statement

intCount = Format(intCount, "000")

to assign a string value to an Integer, and that's what Format() returns, a string value.

You'd need to add a second variable, a string and do something like this:

Dim intCount As Integer
Dim strCount As String
 
intCount = DCount("MusicCategoryID", "tblCDDetails")
strCount = Format(intCount, "000")

and then use strCount variable for whatever display purpose you need.

I hope that by "keeping track of this field (MusicCategoryID)" you mean doing the tally we're talking about, and not actually displaying it on your form. Autonumbers should really never been seen by the end users; their purpose in life is to provide a unique identifier for each record. If you want an ID number to display for a each record, and want it to be consecutive and without gaps, you need to use an auto-incrementing hack to generate these numbers.

Welcome to Bytes!

Linq ;0)>

[MNNovice]

I went to the Format box of the text box (called txtCategoryNo) and added 000 without any quotation and it's been working. When the result is 2 digits, it's displaying as 040 (eg) and when it is 3 digits it's showing as 232 (eg).

I am using this textbox to help me manually enter the next serial number.

Let me explain. If my next category is Country and it's a John Denver CD the correct serial number should be: CO.JDA.02.031.0230 (for example)

So when I will enter the MusicCategoryID, instantly the txtCategoryNo will display 031 and when I will enter the ArtistID, it will display 02.

Am I making sense? I wish I could e-mail you a sample DB to help me with it.

Anyway, I saw your response to my other query, I will definitely attempt it.

Thanks for your help.

Right. I overlooked that one. :)

[MNNovice]

Sorry, it's me again. Just curious. Exactly where do these VBA code supposed to go? on Event Procedure? Format?

/Quote:
Dim intCount as Integer

intCount = DCount ("MusicCategoryID", "tblCDDetails")
intCount = Format(intCount, "000")/Quote

Yes. BUt you can't assign it back to that AutoNumber Field.
only on the Function result of your DCount

Example:

Dim intCount as Integer

intCount = DCount ("MusicCategoryID", "tblCDDetails")
intCount = Format(intCount, "000")

By the way, I think I answered one of your other posts as well.
If it resolves it, please let us know. thanks.

[hyperpau]

Sorry, it's me again. Just curious. Exactly where do these VBA code supposed to go? on Event Procedure? Format?

/Quote:
Dim intCount as Integer

intCount = DCount ("MusicCategoryID", "tblCDDetails")
intCount = Format(intCount, "000")/Quote

I believe the code I have given you in your other post is already the answer.
You put that on the AfterUpdate Event procedure of your MusicCategoryID and ArtistID comboboxes. and remember, use another variable string.

Dim intCount as Integer
Dim stCount as String
 
intCount = DCount ("MusicCategoryID", "tblCDDetails")
stCount  = Format(intCount, "000")