[AccessD] More SQL Server 7 Stored Procedure help if possible

I used the example that was given by Robert Djabarov this time.  Except that I created my own AvailabilityCodes table first, so I missed out the tope half of the code.  Ran like a dream and very fast...........

Thanks for everyone's help resolving this issue..............

Paul


From: "Marcus, Scott (GEAE, RHI Consulting)" <scott.marcus at ae.ge.com>
Date: Wed 26/Feb/2003 20:19 GMT
To: "'accessd at databaseadvisors.com'" <accessd at databaseadvisors.com>
Subject: RE: [AccessD] More SQL Server 7 Stored Procedure help if possible

Paul,

Did you get this issue resolved? If so what was the solution?

Scott Marcus


-----Original Message-----
From: Marcus, Scott (GEAE, RHI Consulting)
[mailto:scott.marcus at ae.ge.com]
Sent: Wednesday, February 26, 2003 12:37 PM
To: 'accessd at databaseadvisors.com'
Subject: RE: [AccessD] More SQL Server 7 Stored Procedure help if
possible


Sorry about that last post. I forgot to change the table name and field name.
This should do it...
(watch for line wrap)

UPDATE tblYourTableName
SET AvailabilityCode =
    IIF(AvailabilityCode = "A","1",
    IIF(AvailabilityCode = "N","2",
    IIF(AvailabilityCode = "S","3",
    IIF(AvailabilityCode = "H","4","NA"))));

Again, this is untested. I don't even know if it will work...that's your job.
Hope it helps.

Scott Marcus

-----Original Message-----
From: Marcus, Scott (GEAE, RHI Consulting)
[mailto:scott.marcus at ae.ge.com]
Sent: Wednesday, February 26, 2003 12:29 PM
To: 'accessd at databaseadvisors.com'
Subject: RE: [AccessD] More SQL Server 7 Stored Procedure help if
possible


Here is an untested method to do it in Access with one query....
(Watch for line wrap)

UPDATE tblParts_Chain
SET tblParts_Chain.CurrentUserLogin = IIF(tblParts_Chain.CurrentUserLogin =
"A","1",
                                      IIF(tblParts_Chain.CurrentUserLogin =
"N","2",
                                      IIF(tblParts_Chain.CurrentUserLogin =
"S","3",
                                      IIF(tblParts_Chain.CurrentUserLogin =
"H","4","NA"))));

Scott Marcus

 
-----Original Message-----
From: paul.hartland at fsmail.net [mailto:paul.hartland at fsmail.net]
Sent: Wednesday, February 26, 2003 11:47 AM
To: dba-SQLServer at databaseadvisors.com; accessd at databaseadvisors.com;
dba-vb at databaseadvisors.com
Subject: [AccessD] More SQL Server 7 Stored Procedure help if possible


To all,

If you read my first email regarding Stored Procedure's you will know that I am
quite a novice at the moment.  I have a table called tblAvailabilityAll which
contains fields PayrollNo and an AvailabilityCode.  The Availability codes are
A,N,S,H and when an employee leaves, in the first instance we need to update
A,N,S,H to 1,2,3,4 accordingly.

Anyone have any idea how to do this within one Stored Procedure (or is it
possible in a single Stored Procedure.....???

I would be grateful fr any sample code etc...

Thanks in advance

Paul Hartland

__________________________________________________________________________
Freeserve AnyTime - Go online whenever you want for just £6.99 a month for
your first 3 months, that's HALF PRICE! And then it's just £13.99 a month
after that.

For more information visit http://www.freeserve.com/time/ or call free on 
0800 970 8890


_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


__________________________________________________________________________
Freeserve AnyTime - Go online whenever you want for just £6.99 a month for
your first 3 months, that's HALF PRICE! And then it's just £13.99 a month
after that.

For more information visit http://www.freeserve.com/time/ or call free on 
0800 970 8890