SQL select query

[Josh Andrews]

Hi, I have an application that automates the Reference Number (or Ticket Number) for every new record encoded in the database in this pattern/hash:
REQ1111-0001
where: 'REQ' is a string (my prefix for request),
'11' - current year (4th & 5th char);
'11' - current month (6th & 7th char);
'-' dash (1 character; 8th place);
'0001' - number that increments every time a new record is added.

I attempted to first write a Select statement (SQL/MS Access) to know the last ticket number in the database for the current year and current month, but I don't know how to include the reserved word 'MAX' in this statement:

SELECT * FROM PO_tbl WHERE (((PO_tbl.[PO_Num]) 
Like 'REQ' & Format(Now(),'yy') & Format(Now(),'MM') & '%'))

Everytime I try to insert MAX in that statement, errors occur. Can anyone please help me? Thanks.

[DaveRook]

Hi

It looks like you're after the latest ticket. Can you not order by and select top 1?

Dave

[DaveRook]

You may have to use the LEFT function as well to skip the REQ word to bring back numbers only

Dave

[NeoPa]

This looks more like an Access question than a SQL Server one, but try something like :

SELECT Max([PO_Num]) AS [LastNo]
FROM   [PO_tbl]
WHERE  ([PO_Num] Like 'REQ' & Format(Date(),'yymm') & '-%')

PS. If it really is SQL Server use "+" in place of "&" for string concatenation.

[Josh Andrews]

Hi DaveRock, yes I need the last Ticket Number so that I can just increment the number (last four chars of the ticket num; "long" datatype) and use the same pattern. The problem is that I cannot just use the Select TOP 1 because the HASH PATTERN that I am trying to implement is slightly different from the previous months (I need to add characters to accomodate new records).

* REQ1111-0001 <--- my new HASH Ticket Number
* RQ11-0001 <--- old pattern

[Josh Andrews]

Thanks NeoPa, I also tried that query. Do you think we can include the MAX word for that? I don't know how because I am just starting to script :-(

[NeoPa]

Josh, it already includes the call to Max(). Why would you ask that?

You also indicate that the existing data is in a different format from what you indicated originally. I find this hard to believe. Why would you ask such a question as this and not include that information if that were true? It would make no sense. However, you do say something which appears to be along those lines, so it would help if you could explain what it is you were trying to say.

You also seem to have missed my asking earlier for you to indicate what you are working with in terms of SQL engines. I appreciate it wasn't a direct question but it was a braod enough prompt I would have thought. This still being unclear makes trying to help you quite confusing so I ask explicitly, what are you working with here (Jet/Access or SQL Server)?