highest number

[crisp]

i have a single table full of about 400 randomly generated numbers. I want something to calculate the highest number and return it for use in a form.

thanks for any help. if it makes any difference im using access 2002.

 

[crisp]

err ive changed my mind i whoud like to create a query to show all avalable 3 digit numbers. so that the result can be used in a combo box on a form.

 

[Uncle Gizmo]

Off the top of my head I can think of two ways to do this. The first way would be a function that counts the number of characters in the string and returns true if there are three characters. You would use this to return a Boolean result in the query and then use the true ones in your combo box.

I don't know if this would work, but it's interesting. Divide the number by a 1,000 and if the answer is less than one then it's a three digit number or less. From these divide by a hundred and if the answer is less than one then it's not a three digit number.

Function fThreeDidget(varNumber As Variant) As Boolean

fThreeDidget = False

If Len(Str(varNumber)) = 4 Then
fThreeDidget = True
End If

End Function

I just tested the above code on various lengths of digit numbers in Long integer format and instead of using “3” for the length as you would expect I had to use “4” to get the right result, Interesting. Must be a reason for it, If anyone has an explanation I would be interested.

 

[chris_dono]

Off the top of my head I can think of two ways to do this. The first way would be a function that counts the number of characters in the string and returns true if there are three characters. You would use this to return a Boolean result in the query and then use the true ones in your combo box.

I don't know if this would work, but it's interesting. Divide the number by a 1,000 and if the answer is less than one then it's a three digit number or less. From these divide by a hundred and if the answer is less than one then it's not a three digit number.

Function fThreeDidget(varNumber As Variant) As Boolean

fThreeDidget = False

If Len(Str(varNumber)) = 4 Then
fThreeDidget = True
End If

End Function

I just tested the above code on various lengths of digit numbers in Long integer format and instead of using “3” for the length as you would expect I had to use “4” to get the right result, Interesting. Must be a reason for it, If anyone has an explanation I would be interested.

To get the three digit numbers:
Simply put a criteria on the number field in your query to say <1000

To return the highest number:
Sort the field descending, then in the properties for the query, simply change the top predicate to 1 and it will return the first number only

 

[Uncle Gizmo]

Yes , I missed that ! And using that idea you could have "Between 100 And 999" in the criteria.

 

[crisp]

thanks, but no good. I proberbly shoud have thaught about what i wrote a bit more.

I hav a table full of three digit numbers. I want a query wich will show me every three digit number that isnt used in the table.

eg the table has 456, and 546, the query whoud show me every other possable combination of the numbers.

Its proberbly really simple like the rest of the problems ive had with this system im making, but im a simple person.

sorry.

 

[crisp]

I dont know if thsi will make any differenc to how i go about solving this but...

The numbers come from a table of stock codes, the stock codes follow this format AA-123-34 << The last 2 digits are check digits.

I use this expression to strip away all but the three digits in the middle 'Mid(Stock_TBL!Stock_ID,4,3)'

There must be a way to modify it in some way to dispaly all possable combinations that havent been taken.

 

[Len Boorman]

i dont know it this will make any difference to the way i do it but the data is stored as text as the number may be 000.

shurely it cant be this hard to show numbers that arnt there.

Question is

If these are randomly generated then why do you want to see those that are not there.

If you want to use those that are not there then use a logical sequence.

If it is a random text string then what is it purpose ?.

Have you normalised your tables ?.

L

 

[crisp]

The tables are normalised

I am improving on an existing system that used the same stock codes, and it is not an option to fiddle with the stock codes.

It is not randomly generated, when the stock codes where originaly created and applyed to the original system they where in sequence however after a few years of editing and adding and removing stock they aint excatly in sequence anymore.

----------------------------------------------

ive had in idea of how i might do it but ive got no idea how to impliment it.

Starting fromm 000 an expression or piece of code checks to see if the number exists in the query, if it dose it adds 1 and trys again it continues like this and returns the lowest free number.

 

[Len Boorman]

Personally I would be reluctant to use a reference that possibly had been used previously. Would not continuing the sequence from the current highest reference be an option

L

 

[crisp]

the current highest number is at the moment 893 so no there is no room for a large number of products to be added.

there are only about 200 products in the database at the moment.

ive had another idea on how to do it.

an expression in a query to build a list of all combinations of 3 digits (or me create a table full of em) and a criterea to remove used numbers, in the query.

 

[Len Boorman]

If you creatre a table of all possibles
Then a UNION ALL of all possibles and Actual Used
Then a Find Non Duplicates query will tell you which are available

L

 

[crisp]

is there a quick way to populate the table.

Sounds good (but a bit messy) Thanks!

 

[Len Boorman]

Use Excel and copy and paste

Sometimes the small step approach allows you (and others who may inherit your application) to see and understand more easily exactly what is going on.

L

 

[crisp]

thanks ive got it working!