Sorry I know this an old question, but...
I am writing a Basic numbering script for a SQL based application which queries for the max value of a pattern using:- sSql = "select max (CN_DOCUMENT_NUMBER) MaxNr from tn_Documentation where CN_DOCUMENT_NUMBER Like '" & sPrefix & "%' "
I'm simply adding 1 to the result and using the completed string for the next sequential number.
sPrefix is just a text string. It is not a column in the DB. I have examples where sPrefix = "OSI_PROG_" and also where sPrefix = "OSI_PR_" (note the underscore at the end is included in sPrefix. I can't retrieve a second max PR value as the query finds PROG values.
I'm assuming the solution is to use something like
LIKE REPLACE(sPrefix,'_','[_]') + '[0-9][0-9][0-9]
Trouble is I'm not usre of the exact syntax i.e. where the single and double quotes go. I keep getting a message saying column sPrefix doesn't exist.
Can anybody help?
Thanks,
Elk
I am writing a Basic numbering script for a SQL based application which queries for the max value of a pattern using:- sSql = "select max (CN_DOCUMENT_NUMBER) MaxNr from tn_Documentation where CN_DOCUMENT_NUMBER Like '" & sPrefix & "%' "
I'm simply adding 1 to the result and using the completed string for the next sequential number.
sPrefix is just a text string. It is not a column in the DB. I have examples where sPrefix = "OSI_PROG_" and also where sPrefix = "OSI_PR_" (note the underscore at the end is included in sPrefix. I can't retrieve a second max PR value as the query finds PROG values.
I'm assuming the solution is to use something like
LIKE REPLACE(sPrefix,'_','[_]') + '[0-9][0-9][0-9]
Trouble is I'm not usre of the exact syntax i.e. where the single and double quotes go. I keep getting a message saying column sPrefix doesn't exist.
Can anybody help?
Thanks,
Elk
Re: [Elkie] Replace Underscore