• upsizing querys and iif (Access 2000 sp3)

    Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » upsizing querys and iif (Access 2000 sp3)

    • This topic has 16 replies, 4 voices, and was last updated 21 years ago.
    Author
    Topic
    WSgarbsmj
    AskWoody Lounger
    March 19, 2004 at 6:10 pm #402540

    We’re trying to upsize to SQL 2000 and created an adp (project) to store the queries. Most of the queries went over from the A2K .mdb database. One query that didn’t involved the calculation of a code based on an IIF(something, ‘12345’, somethingelse). In recreating the stored procedure, the SQL check doesn’t like the IIf statement. Can anyone help me with an alternative here? please

    Reply | Quote
    Viewing 1 reply thread
    Author
    Replies
    • WScharlotte
      AskWoody Lounger
      March 19, 2004 at 11:39 pm #801848

      IIF doesn’t exist in T-SQL, so SQL Server can’t handle it. If you post the SQL of your query, someone should be able to help you modify it for SQL Server. If you need to do this in SQL, you’ll need a SQL user-defined function, but there may be alternatives.

      Reply | Quote
      • WSgarbsmj
        AskWoody Lounger
        March 22, 2004 at 1:21 pm #802599

        Here’s part of the big, ugly query. I set up a view using CASE ELSE and was able to do the first IIF(customer = “999999”), but was unable to handle the nested IIF statement below::

        SELECT CustHeader.SHIPDATE, ShipData.CUSTOMER, CustHeader.CUST_NAME, CustHeader.SHIP_VIA, CustHeader.NET_DAYS,
        ShipData.SHIP_TO_NAME, qryHTS.HTSDescr, IIf(([ShipData].[CUSTOMER]=”999999″ And [HTSDescr]=”MOUSE EARS -“),”1234.56.78.90″,
        IIf((Left([ShipData].[CUSTOMER],2)=”HO” And [HTSDescr]=”MOUSE EARS -“),”1234.56.78.90”,[qryHTS].[HTS])) AS HTS1,
        …………………………..

        Unfortunately, we rely on IIF a lot. So other than CASE’ing ourselves into an early grave, is there a better alternative?

        Reply | Quote
        • WScharlotte
          AskWoody Lounger
          March 22, 2004 at 1:44 pm #802614

          CASE statements provide the If-then-else functionality in T-SQL, so that is what you have to use, however, they provide much more functionality in SQL Server than in Access SQL.

          Reply | Quote
          • WSgarbsmj
            AskWoody Lounger
            March 23, 2004 at 3:55 pm #803469

            Sorry, Charlotte – I tried to find out where I could duplicate my nested iif statement using Case, but I’m at a loss.

            Can you point me in the right direction? Basically, most of our IIF statements involve two fields that need to be evaluated before we want to make a change. For example, if the item is a fruit and it’s yellow, then it needs the “fragile, it’s a banana” code. But if it’s a fruit and it’s not yellow, then it doesn’t.

            Since all the examples of Case I’ve seen involve just one field, I can’t figure out what to do. dizzy

            Reply | Quote
            • WSShane Sargent
              AskWoody Lounger
              March 23, 2004 at 6:32 pm #803538

              Mmmmm…bananas! The trick is to embed the WHEN…ELSE statement withing the CASE statement. OK, let’s make a fake table to play with. Run this SQL script to make a table named Product that has 3 fields (an identity field named ID, and two fields to hold text values named Color and Durability):

              CREATE TABLE [dbo].[Product] (
	[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
	[Color] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Durability] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

              Populate the new table. In the first record, make Color be “Yellow” and Durability be “Fragile”. Now populate a few more records; go with: Yellow/Durable, Green/Fragile, and Green/Durable.

              If the Access query looks like:

              SELECT *, IIF([Color] = "Yellow" AND [Durability] = "Fragile", "Banana", "Not Banana")
  AS WhatIsIt
FROM Product

              then the SQL Server query looks like this:

              SELECT *, 
  CASE
    WHEN P.Color = 'Yellow' AND P.Durability = 'Fragile' THEN
      'Banana'
    ELSE
      'Not Banana'
  END
AS WhatIsIt
FROM Product P
              Reply | Quote
            • WSgarbsmj
              AskWoody Lounger
              March 23, 2004 at 6:54 pm #803552

              Thanks Shane – I’m trying it out right now.

              It’s starting to make sense now…..

              Reply | Quote
            • WSgarbsmj
              AskWoody Lounger
              March 23, 2004 at 6:54 pm #803553

              Thanks Shane – I’m trying it out right now.

              It’s starting to make sense now…..

              Reply | Quote
            • WSgarbsmj
              AskWoody Lounger
              March 23, 2004 at 7:25 pm #803574

              Shane,

              It worked! Thanks ! thewave!

              Now I can go on vacation with one less worry. thankyou

              Reply | Quote
            • WSgarbsmj
              AskWoody Lounger
              March 23, 2004 at 7:25 pm #803575

              Shane,

              It worked! Thanks ! thewave!

              Now I can go on vacation with one less worry. thankyou

              Reply | Quote
            • WSShane Sargent
              AskWoody Lounger
              March 23, 2004 at 6:32 pm #803539

              Mmmmm…bananas! The trick is to embed the WHEN…ELSE statement withing the CASE statement. OK, let’s make a fake table to play with. Run this SQL script to make a table named Product that has 3 fields (an identity field named ID, and two fields to hold text values named Color and Durability):

              CREATE TABLE [dbo].[Product] (
	[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
	[Color] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Durability] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

              Populate the new table. In the first record, make Color be “Yellow” and Durability be “Fragile”. Now populate a few more records; go with: Yellow/Durable, Green/Fragile, and Green/Durable.

              If the Access query looks like:

              SELECT *, IIF([Color] = "Yellow" AND [Durability] = "Fragile", "Banana", "Not Banana")
  AS WhatIsIt
FROM Product

              then the SQL Server query looks like this:

              SELECT *, 
  CASE
    WHEN P.Color = 'Yellow' AND P.Durability = 'Fragile' THEN
      'Banana'
    ELSE
      'Not Banana'
  END
AS WhatIsIt
FROM Product P
              Reply | Quote
          • WSgarbsmj
            AskWoody Lounger
            March 23, 2004 at 3:55 pm #803470

            Sorry, Charlotte – I tried to find out where I could duplicate my nested iif statement using Case, but I’m at a loss.

            Can you point me in the right direction? Basically, most of our IIF statements involve two fields that need to be evaluated before we want to make a change. For example, if the item is a fruit and it’s yellow, then it needs the “fragile, it’s a banana” code. But if it’s a fruit and it’s not yellow, then it doesn’t.

            Since all the examples of Case I’ve seen involve just one field, I can’t figure out what to do. dizzy

            Reply | Quote
        • WScharlotte
          AskWoody Lounger
          March 22, 2004 at 1:44 pm #802615

          CASE statements provide the If-then-else functionality in T-SQL, so that is what you have to use, however, they provide much more functionality in SQL Server than in Access SQL.

          Reply | Quote
        • WBell
          AskWoody_MVP
          March 22, 2004 at 4:18 pm #802710

          Adding to Charlotte’s comments, if you use CASE statements in SQL Server, you usually are talking about Stored Procedures rather than Views, which most Access SELECT queries can be converted to. And for someone who is used to the nice compact syntax of IIF statements, CASE statements seem positively ugly! But as Charlotte points out, at least through SQL Server 2000, they are the only game in town. That’s one of the reasons we typically use linked SQL Server tables rather than the ADP approach.

          Reply | Quote
        • WBell
          AskWoody_MVP
          March 22, 2004 at 4:18 pm #802711

          Adding to Charlotte’s comments, if you use CASE statements in SQL Server, you usually are talking about Stored Procedures rather than Views, which most Access SELECT queries can be converted to. And for someone who is used to the nice compact syntax of IIF statements, CASE statements seem positively ugly! But as Charlotte points out, at least through SQL Server 2000, they are the only game in town. That’s one of the reasons we typically use linked SQL Server tables rather than the ADP approach.

          Reply | Quote
      • WSgarbsmj
        AskWoody Lounger
        March 22, 2004 at 1:21 pm #802600

        Here’s part of the big, ugly query. I set up a view using CASE ELSE and was able to do the first IIF(customer = “999999”), but was unable to handle the nested IIF statement below::

        SELECT CustHeader.SHIPDATE, ShipData.CUSTOMER, CustHeader.CUST_NAME, CustHeader.SHIP_VIA, CustHeader.NET_DAYS,
        ShipData.SHIP_TO_NAME, qryHTS.HTSDescr, IIf(([ShipData].[CUSTOMER]=”999999″ And [HTSDescr]=”MOUSE EARS -“),”1234.56.78.90″,
        IIf((Left([ShipData].[CUSTOMER],2)=”HO” And [HTSDescr]=”MOUSE EARS -“),”1234.56.78.90”,[qryHTS].[HTS])) AS HTS1,
        …………………………..

        Unfortunately, we rely on IIF a lot. So other than CASE’ing ourselves into an early grave, is there a better alternative?

        Reply | Quote
    • WScharlotte
      AskWoody Lounger
      March 19, 2004 at 11:39 pm #801849

      IIF doesn’t exist in T-SQL, so SQL Server can’t handle it. If you post the SQL of your query, someone should be able to help you modify it for SQL Server. If you need to do this in SQL, you’ll need a SQL user-defined function, but there may be alternatives.

      Reply | Quote
    Viewing 1 reply thread
    Reply To: upsizing querys and iif (Access 2000 sp3)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




DON'T MISS OUT!
Subscribe to the Free Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

View the Forum

  • Recent Replies
  • My Replies
  • My Active Topics
  • New Posts in the Last day
  • Private Messages
  • Knowledge Base
  • How to use the Forums
  • All Forums

    • Recent Topics

    My Profile

    March 2025
    S M T W T F S
     1
    2345678
    9101112131415
    16171819202122
    23242526272829
    3031  

    Remembering Woody

     

    Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.

    Mastodon profile for DefConPatch
    Mastodon profile for AskWoody

     

    Home • About • FAQ • Posts & Privacy • Forums • My Account
    Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts

    Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.