Find Highest Number

[guamlet]

I have a column of data that takes the form of a letter followed by a number (i.e. H004). Sometimes the number is followed by a period and more letters and numbers (i.e. H004.2a.3d). I want to search for the highest number in the characters before the first period (the H004) and return one number higher (i.e. H005). Any suggestions?

 

[Russell Hauf]

Try something like this:

• NewNum: Int(Val(Mid([CData],2)))

Where CData is your "column" with the data you described.

HTH,

Russell

 

[guamlet]

Thanks for the info, but I am having trouble with the [Data] part. The [Data] is a field in a table and I'm writing this in VB for an unbound form. I tried to do the

.[Field] thing, but it says it cannot find "|". Am I doing something wrong? thanks for your help!!!

 

[Russell Hauf]

I'd have to see your code...

 

[guamlet]

This is the code that I was trying to use. "Cell Lines" is the table, and "LineName" is the column.

var(mid([Cell Lines].[LineName],1,3))

 

[Russell Hauf]

Sorry, that is still not enough info. Also, you don't want to use Var, you want to use Val. You can do Mid([x],1,3) if all of your numbers are 3 characters in length, but if not, then you'll need to use something like what I have above. Oh, and if they are all 3 characters in length then you can use CLng or CInt instead of Val.

 

[guamlet]

I'm not sure what information you need....how about another approach...I wrote two queries. One query gives me a list of the entries that begin with the letter "H" and the second query looks at that query and spits out the max value. Is there a way to get that max value and put it in a list box on a form when the form opens?

 

[Russell Hauf]

Sure, base the list box on the query. So are you wanting to create a new record with the "next highest number" as the value for that record's field? I'm not sure what you're trying to do since you say your form is unbound. Maybe tell me a little more about what you are trying to do...sorry to be so thick headed.

 

[guamlet]

You are definitely not being thickheaded...I'm just not explaining myself properly...

Ok, the form is unbound because of the code behind it. What it does is uses rs.AddNew and inserts all the fields in a table once the command button is pressed. I had to use an unbound form because of a subform that will automatically fill in fields based on a FindFirst that runs off of a combo box containing the entries in the CellLines column. When I was using a bound form, the FindFirst would just jump to the current record, and that's when people were overwriting it. That's basically the method behind the madness.

Now, why to I want this information? I want a list box (or text box or whatever) that basically tells people (as soon as the form opens or refreshes) what the highest H00* is so that they don't have to go looking for it and accidentally use an old one. The list box also has to do the same for R00*, P00* and any other letter that my lovely group decides to use. So I was hoping that I could have some way of displaying the highest number for each letter (R,H,P) in a nice little area on the form. I was also hoping to use pure coding and not have to create a query for each letter but I suck at SQL.

Does this clear it up a little?

Guamlet

 

[Russell Hauf]

Yes, it does. One more quick question: Do the numbers only go up to 9? So you have R00*, etc. So can it only be R001,...,R009? Or can it go up to R999? I think that SQL is going to be your best bet here, sorry. But we'll fix you up.