Increment a text number 1

[BrianLe]

In Access 97, I would like a way to increment a text "value" by one with a command button. I have:

tblTrainNo
TrainID autonum
TrainNo text

TrainNo is in the form of YYYYPAT#, like 2007PAT23.

Can I add a cmdButton to frmAddNewTrainNo that will increment the last TrainNo by 1? frmAddNewTrainNo has a record source tblTrainNoQuery, which is:

SELECT tblTrainNo.TrainNo
FROM tblTrainNo
ORDER BY Val(Mid(TrainNo,8)) DESC;

For example, if the last TrainNo is 2007PAT89, I would like the button to add 2007PAT90 to tblTrainNo.

Thanks, Brian

 

[PHV]

You may try this:
Me!TrainNo = Year(Now) & "PAT" & (Nz(DMax("Mid(TrainNo,8)", "tblTrainNo", "Left(TrainNo,4)=Year(Now())"), 0) + 1)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BrianLe]

Works great until TrainNo = 2007PAT100. It won't find TrainNo's above 2007PAT100. It looks like there is something wrong in the tblTrainNoQuery that is causing the problem. I can manually add TrainNo's above 2007PAT100 to tblTrainNo. However, when I run query tblTrainNoQuery, the TrainNo's above 2007PAT99 are at the bottom of the list. How can I get it to find TrainNo's above 2007PAT100? Thanks, Brian

 

[MikeGeitner]

I can't get it work either. It won't add 1 to any record over 2007PAT9, so the highest train number I get is 2007PAT10 which will keep repeating. I think when it looks for the "DMax" of the "Mid" starting at the eighth character, it stops at "9", the highest number and keeps adding 1 to that.

You could add a field to the table to store the incremented train number, which you could use to find the max for that year. Then save the concatenated "2007PATxxx" to another field?

 

[VRoscioli]

I doubt that it would affect it, but you could try Val(Mid(TrainNo,8)) instead of just Mid(TrainNo,8)


-V

 

[MikeGeitner]

This works, but I'm sure it could be much better:

Dim strYearPat, strTrain, strNumb As String

strYearPat = Year(Now) & "PAT"
strTrain = Nz(DMax("[TrainNo]", "tblTrainNo", "Left(TrainNo,4)=Year(Now())"), Year(Now) & "PAT" & 0)
strNumb = Mid(strTrain, 8) + 1

Me!TrainNo = strYearPat & strNumb

 

[postermmxvicom]

You could add a field to the table to store the incremented train number, which you could use to find the max for that year. Then save the concatenated "2007PATxxx" to another field?

I like this idea. I use something like it in a database I am working on now.

I would break the TrainNo into three parts. So instead of having one field you would have 3 (or 2 if the 'PAT' part always stays the same). One field for the year, one for the 'PAT' (if it is something that can change), and one for the ending number.

Then I use a query to make a look up table that combines those three fields into one. So that way, I can use the query for displaying and using the info as if it was one string, but I can use the data in the table for manipulating the numeric part since it's separated there.

 

[PHV]

Sorry for the typo.
You may try this:
Me!TrainNo = Year(Now) & "PAT" & (Nz(DMax("Val(Mid(TrainNo,8))", "tblTrainNo", "Left(TrainNo,4)=Year(Now())"), 0) + 1)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MikeGeitner]

PHV, I couldn't get the Val to work, now I see why. It's the qoutes.............

Nice.

 

[BrianLe]

PHV's latest suggestion works great for what I have now, but I 'll keep the other suggestions in mind. Thanks to all.