Finding largest number within a Range

[mrmarc]

I need to populate the dropdown in a combo box with the largest number in a field that's between 1 and 5000. There are number above 5000 in the field, but I want the largest number between 1 and 5000. Should I use a query? Or VBA?

 

[Joe4]

I think you should be able to use the DMAX function in Access. For your criteria, you can limit it to records with values less than 5000.
See: https://www.techonthenet.com/access/functions/domain/dmax.php

 

[jackd]

Why do you need a dropdown/combo when you only have 1 value? Why not a text box?
Might be easier if you told us what you need this value for. That is will you be using that value in a query??

 

[mrmarc]

Why do you need a dropdown/combo when you only have 1 value? Why not a text box?
Might be easier if you told us what you need this value for. That is will you be using that value in a query??

Hi Jackd,

Yeah, a text box would probably work just as well. Here's the scenario:

Bob inputs "pallet numbers" when finished product is received. His numbers start at 5000.

Joe inputs "pallet numbers" when product is produced within the plant. His numbers start at 1.

If I do a Dmax to find out the "next" number to input it always goes for (obviously) the highest number, (so a number in the 5000s). This is good for Bob but not good for Joe.

How do I populate a text box for Joe so that he know what pallet number to input next. So if he just inputted 500, and wants to add a new record, I want the text box to be populated with 501.

Note: The Pallet number field can contain multiple entries of the same pallet number.

The key is to keep all information on one table.

I hope this helps. Let me know. Thanks!

 

[Joe4]

If I do a Dmax to find out the "next" number to input it always goes for (obviously) the highest number, (so a number in the 5000s). This is good for Bob but not good for Joe.

Look more closely at the DMAX function. The third argument is for criteria. So you can use the username in there, so you will get the Max value for each specific User.

 

[mrmarc]

I'm not sure about setting up "users".

So how could the Dmax function look?

DMax("PalletNumber", "PalletRecords", PalletNumber < 5000)

or...

 

[Joe4]

Bob inputs "pallet numbers" when finished product is received. His numbers start at 5000.

Joe inputs "pallet numbers" when product is produced within the plant. His numbers start at 1.

DMax("PalletNumber", "PalletRecords", PalletNumber < 5000)

If I understand your correctly, it sounds like you can forget about using "PalletNumber" in the Criteria argument, and use "User" name instead.

 

[jackd]

I agree with Joe4 - it seems the use of pallet number is totally misleading. It appears that pallet number is being used for 2 separate purposes.

It isn't clear what pallet numbers mean in your business. Seems the meaning of pallet number depends on the user Bob or Joe????