DCount for Number

[The Bey]

Can you do a DCount for 10 digit number?

I have a query which could have results either of text or a number. I've got the text sorted, but is there a way I can do, say:

=DCount("*","[qryName],"ColumnName='##########'")

or maybe something to say "does not = text"

 

[Galaxiom]

What matters is the data type of the field. A text field can hold numbers but they are treated as text so require the quotes.

 

[gemma-the-husky]

either way, you possibly run into problems once your number goes above 2billion approx - ie the maximum number a long can hold. thatr is why a 10 digit number is giving you problems - it is overflowing

you may be able to get a bigger integer using a double, but i am not sure.

changing to text may not help.

could you split the number into two parts maybe. after all you don't REALLY need a 10 digit number.

out of interest, why would a large number be a column header? that doesn;t really make sense to me.

 

[The Bey]

What it is is a work order number. Originally the work order is given a priority ("CAT D" in this case) but then once the work order has been addressed it becomes an "MWO number".

The MWO number consists of the year [in 4 digits] followed by 6 numbers. I considered using a date string with 6 more digits but the problem is that the MWO no. will be made in a seperate filing system.

The column name won't be the large number; the column's contents will contain the number

 

[The Bey]

Could I perhaps set the DCount to search for a Double rather than specific details?

 

[Galaxiom]

This sounds like you are mixing information into one field. Best avoid mixing strings and numbers into the one field type. You loose much of the advantage of a number when it is forced to be treated as text.

Use two separate fields. One for the MWO number (Null if not yet allocated) and different one to indicate the Priority.

BTW You would not store any part of that which is repeated such as "CAT". Just store "D". If it is any more than a couple of letters it is better to use a number with a lookup.

Another alternative would be to use the one field but with a negative number to indicate it is a Works Order. The Priority could be represented by the negative number.

 

[Galaxiom]

You could use Long to store the number.
It won't fall over until invoice number 486348 in 2147 so it won't be your problem then.

 

[Galaxiom]

I also wonder about the strategy that requires your DCount to work on different fields. Maybe you should be looking at designing to avoid that.

Having said that, the equals sign suggests you are using that as a dynamic ControlSource being constructed in VBA. You could concatenate the quotes as required using logic based on the fieldname.

However this can get clumsy if if you try to hard code it. I have used a different approach when constructing on-the-fly SQL by retrieving the Type property of the field from the table itself to determinine the delimiters to be used for a field value.

 

[The Bey]

That's a very in depth reply and I appreciate the hasty response.

I instead decided to use a load of text boxes with their control sources as DCounts pretty much saying any value that isn't "CAT D", or "Complete" is an MWO number. Was tedious but it's doing the job nicely and I know I can control what's happening.

It'd be nice to have a query/report to show these details but that seems tricky as I can only use 1 control source, as opposed to text boxes on forms that I can unite quite easily.

Any suggestions?

 

[Galaxiom]

I instead decided to use a load of text boxes with their control sources as DCounts ...

That is generally a receipe for a very slow loading form because each Domain function is essentially a separate query. Be sure to index any fields in the table where you are applying conditions.

Performance may deteriorate as the number of records increases.

Matching text is also slower than using integers to represent values.

 

[tobyjenkins]

I agree with Galaxiom - far easier to count or lookup if you use discrete fields for difference information...

Have one field that stores the 10-digit number formatted to '0000000000' to catch any leading zeros with the date...

Then you could have a lookup field that merely states the job's status (Cat D, Current, Complete or whatever) - you could then dcount using this field:

iCount = DCount("id", "yourTable", "status<" & 2)

Or something like that...