Dcount (1 Viewer)

[jumpin]

Hi, i'm trying to count the numbers between 60001 and 60061 with dcount and put the result in a text box, but all i get is an error, this is the code i'm using .... =DCount("*","[ALL]","[AddNumber] >= 60001 And [AddNumber] <= 60061")

 

[Pat Hartman]

Perhaps you could share the error message.

 

[jumpin]

all it says in the text box is #Error

 

[June7]

AddNumber is a number type field?

What circumstances would return a result other than 60?

 

[jumpin]

AddNumber is a number type field?

What circumstances would return a result other than 60?

you're right, the addnumber type was short text, let me explain further, the table consists of numbers from 03001 to 89500, which is why i had to put the type as short text, so it would except the numbers starting with a '0', is there a way of writing the code so it works with numbers starting with '0'?

 

[The_Doc_Man]

DCount normally doesn't care about nulls (just ignores them). The arguments are a field (or * ), a record source, and 0 or more criteria.

Usually, "*" is valid in that position. For snorts and giggles, you MIGHT try "[AddNumber]" in place of the "*" - though I'm not betting on that.

Don't know your tables, but if [ALL] is a valid table name or a validly named SELECT query, you should be OK.

That leaves the criteria. The only way that would fail is if [AddNumber] isn't really a number OR if there is a hidden, non-printing character in the mix.

You might try to create a query to see what it returns:

SELECT * FROM ALL WHERE [AddNumber] >= 60001 AND [AddNumber] <= 60061 ;

 

[The_Doc_Man]

Your post crossed mine in posting. The real question is why those numbers have leading zeros. Your problem has nothing to do with accepting numbers with leading zeros. When you have the numbers in a table, are they to be primarily treated as numbers or primarily treated as strings that happen to look like numbers? The display format for those numbers can be controlled with a Format function. But why are they text? (NOTE: Access would correctly handle numbers with leading zeroes for input.)

 

[jumpin]

DCount normally doesn't care about nulls (just ignores them). The arguments are a field (or * ), a record source, and 0 or more criteria.

Usually, "*" is valid in that position. For snorts and giggles, you MIGHT try "[AddNumber]" in place of the "*" - though I'm not betting on that.

Don't know your tables, but if [ALL] is a valid table name or a validly named SELECT query, you should be OK.

That leaves the criteria. The only way that would fail is if [AddNumber] isn't really a number OR if there is a hidden, non-printing character in the mix.

You might try to create a query to see what it returns:

SELECT * FROM ALL WHERE [AddNumber] >= 60001 AND [AddNumber] <= 60061 ;

 

[jumpin]

All i get is an #Name? in the text box

 

[jumpin]

Your post crossed mine in posting. The real question is why those numbers have leading zeros. Your problem has nothing to do with accepting numbers with leading zeros. When you have the numbers in a table, are they to be primarily treated as numbers or primarily treated as strings that happen to look like numbers? The display format for those numbers can be controlled with a Format function. But why are they text? (NOTE: Access would correctly handle numbers with leading zeroes for input.)

they are to be primarily treated as numbers, the reason they are text is so they display in a text box as they are in typed in the table, if i change the field type to number, it deletes the leading zero

 

[The_Doc_Man]

Ah, but if you then decide to use the FORMAT function, you can put as many leading zeros as you want for display purposes, because remember that Access doesn't care what you put in a text box. It's just a display artifice. If you use the raw number, you get... the raw number. If you use FORMAT( n, "000000" ) you get a number with leading zeros. OR better yet, you can diddle with the text box's FORMAT property to the same effect. So instead of displaying raw numbers, display formatted numbers. Computers can do that for you.

 

[June7]

Or to use field as is:

=DCount("*","[ALL]","[AddNumber] >= '60001' And [AddNumber] <= '60061'")

=DCount("*","[ALL]","[AddNumber] BETWEEN '60001' AND '60061'")

 

[jumpin]

Ah, but if you then decide to use the FORMAT function, you can put as many leading zeros as you want for display purposes, because remember that Access doesn't care what you put in a text box. It's just a display artifice. If you use the raw number, you get... the raw number. If you use FORMAT( n, "000000" ) you get a number with leading zeros. OR better yet, you can diddle with the text box's FORMAT property to the same effect. So instead of displaying raw numbers, display formatted numbers. Computers can do that for you.

I've tried both yours and june7 codes and they work upto a point, but they are not counting the numbers with a leading zero. i changed the format to '00000' on the numbers field but it did'nt make any difference

 

[June7]

If the field is text type, then it should work with leading zeros. Does for me.

If you want to switch to number field then leading zeros should not be a concern and parameters would not have leading zeros, in spite of display settings. Format property does not alter actual data in field. Wrapping data in Format() function does alter value used.

How do you want user to enter data? How do you want data saved? Text field allows leading zeros and can use InputMask property, although it can be tricky to implement.

A number field can be set to display with leading zeros but user input will not require the leading zerios.

 

[Gasman]

Why not just use Val() on the field and compare against actual numeric values?

 

[June7]

Val() is definitely an option.

 

[Gasman]

Val() is definitely an option.

Well TBH, if they had to be numbers, they would be numeric in my db, then all this is moot.

 

[The_Doc_Man]

All i get is an #Name? in the text box

Missed this one earlier. THAT is a different problem. When you see #Error then there is something intrinsically wrong with your expression, whichever one it is complaining about. When you see #Name, you have spelled something incorrectly and Access can't find whatever it is that you asked to use.

Here is your problem in a nutshell. Your list of numbers is going to used as a list of numbers. Store them as numeric. Since your largest number is over 83K, data sub-type LONG. Put a format on the text box and anyplace else on the form where that number might show up.

BUT if you have issues with leading zeros, you must remember that for a comparison to work (e.g. [AddNumber] <= 'nnnnn' ), there can be no numbers for which the leading digits should be - but are not - the right number of zeros. Sorts with uneven-length digit strings is everyone's worst nightmare to straighten out.

 

[Pat Hartman]

is there a way of writing the code so it works with numbers starting with '0'?

this thread seems to be circling the drain.

You have a "code" rather than a "number". In the US the Zip code is always text because it is a code and some values start with 0 including mine SSN is also a code and so must also always be stored as text due to the potential for leading zeros.

Therefore, you need to enclose your values in single or double quotes as shown by @June7 in #12. But since you are working with a string, the leading zeros are relevant and if you are really looking for

=DCount("*","[ALL]","[AddNumber] >= '060001' And [AddNumber] <= '060061'")

then you need to include the leading zeros.

A larger question is "why". The answer is that you are most likely mushing multiple values together. Perhaps category and subcategary and in order to make this work at all, the category values need to be zero filled and the subcategory values need to be zero filled. Using non-atomic fields violates first normal form which is the lowest of all the normalizan forms and the absolute minimum requirement for a valid table. ALL columns must be atomic. ie. the contain one and only one attribute.