By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,327 Members | 1,797 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,327 IT Pros & Developers. It's quick & easy.

Formatting in ACCESS 2003

100+
P: 418
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.
Aug 18 '08 #1
Share this Question
Share on Google+
6 Replies


hyperpau
Expert 100+
P: 184
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.
Aug 19 '08 #2

missinglinq
Expert 2.5K+
P: 3,532
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:

Expand|Select|Wrap|Line Numbers
  1. Dim intCount As Integer
  2. Dim strCount As String
  3.  
  4. intCount = DCount("MusicCategoryID", "tblCDDetails")
  5. 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)>
Aug 19 '08 #3

hyperpau
Expert 100+
P: 184
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:

Expand|Select|Wrap|Line Numbers
  1. Dim intCount As Integer
  2. Dim strCount As String
  3.  
  4. intCount = DCount("MusicCategoryID", "tblCDDetails")
  5. 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)>
Aug 19 '08 #4

100+
P: 418
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. :)
Aug 19 '08 #5

100+
P: 418
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.
Aug 19 '08 #6

hyperpau
Expert 100+
P: 184
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.


Expand|Select|Wrap|Line Numbers
  1. Dim intCount as Integer
  2. Dim stCount as String
  3.  
  4. intCount = DCount ("MusicCategoryID", "tblCDDetails")
  5. stCount  = Format(intCount, "000")
Aug 20 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.