In a Ticket table I have a field TicketID which I am using in a format like:
The first set (left side) represents Ticket No. Then month, year and code. Each time when a user wants to enter a new record, a new ID is generated as:
Select max(Left(TicketID,5)) from Ticket
The new id becomes result+1. I want to know that the above field is a string. But when I use LEFT function, as above, it successfully returns max of the first set. Does it automatically convert it to a long integer? If not, how to use it securely so that first the left set is converted to long integer and then the MAX is found? All this needs to be done in a SQL query.
There are actually several questions here. First, the MAX() aggregate function works perfectly well on strings, and of course LEFT() produces a string. Conversion to numeric format is implicit when you use the MAX() result string in an arithmetic operation in Microsoft Access (which you're probably using, since you mentioned a "Long Integer"). You don't have to worry about converting the LEFT() result to a numeric value before taking the maximum, since your strings seem to be formatted with leading zeroes, so they should collate as expected, i.e., numerically.
However, your decision to use an "intelligent" id, where portions of the id have special meaning, is questionable. My advice would be to use a regular old autonumber for the id, and have month, year and code as separate columns. This way you are spared from having to do the MAX(id)+1 trick yourself, which can lead to other problems (e.g., race conditions). You could always combine your four columns for display purposes by concatenating them in your SELECT queries.
This was first published in January 2007