LW Irving
|
Posted: 09/29/2003, 12:24 AM |
|
I have been webifying an access database of quotes for a company
The quote numbers are in the format N0310700
where N is an office identifier 03 is the year 10 is the month 7 is the
origin of the quote 00 is the quote number for the month
They want the quote number to auto generate
as I see it I need to find the last or highest quote (how?)
check to see if it is in the current month
if not then it is just get the office identifier + year + month + origin +
00
if a quote already exists for the current month I need to increment the 00
part by 1
is there any documentation on manipulating strings
or has someone done something similar
Regards
Wayne
|
|
|
David Harrison
|
Posted: 09/29/2003, 3:19 AM |
|
How about the following:
select 'N' + substring(convert(char(4),year(getdate()),3,2) +
substring(convert(char(2),right('0' + month(getdate()),2) + '7' +
substring((select convert(char(2),right('0' +
convert(char(2),isnull(max(convert(int,substring(Quote,7,2)) + 1,0)),2) from
Quotes where substring(convert(char(8),Quote),1,1) = 'N' and
substring(convert(char(8),Quote),2,2) =
substring(convert(char(4),year(getdate()),3,2) and
substring(convert(char(8),Quote),4,2) = right('0' +
convert(varchar(2),month(getdate()),2) and
substring(convert(char(8),Quote),6,1) = '7'),7,2)
If I typed everything correctly this should work for MSSQL as for Access I
think there are some slight changes needed for the date functions. This may
be overkill because I don't know the datatype of Quote, so I've tried to
make it work with any datatype.
Hope this helps
David Harrison
"LW Irving" <lirving@coffs.com.au> wrote in message
news:bl8mnu$718$1@news.codecharge.com...
> I have been webifying an access database of quotes for a company
> The quote numbers are in the format N0310700
> where N is an office identifier 03 is the year 10 is the month 7 is the
> origin of the quote 00 is the quote number for the month
> They want the quote number to auto generate
> as I see it I need to find the last or highest quote (how?)
> check to see if it is in the current month
> if not then it is just get the office identifier + year + month + origin +
> 00
> if a quote already exists for the current month I need to increment the 00
> part by 1
>
> is there any documentation on manipulating strings
> or has someone done something similar
>
> Regards
> Wayne
>
>
|
|
|
|