CodeCharge Studio
search Register Login  

Visual PHP Web Development

Visually Create Internationalized Web Applications, Web Reports, Calendars, and more.
CodeCharge.com

YesSoftware Forums -> Archive -> CodeChargeStudio.Discussion

 string manipulation

Print topic Send  topic

Author Message
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
>
>


   


These are Community Forums for users to exchange information.
If you would like to obtain technical product help please visit http://support.yessoftware.com.

PHP Reports

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.
CodeCharge.com

Home   |    Search   |    Members   |    Register   |    Login


Powered by UltraApps Forum created with CodeCharge Studio
Copyright © 2003-2004 by UltraApps.com  and YesSoftware, Inc.