Professional Web Applications Themes

Access Upsize to SQL Query Problem - Microsoft SQL / MS SQL Server

I've been called upon to upsize a fairly basic Access Database to SQL so we can integrate the data with our SQL based CRM. We need to leave the front end intact so I chose to convert to an ADP. I've managed to get all the tables over and a fair amount of the forms, reports and querries. What I need help with is a select query that didn't upsize. I've recreated the query except two calculated fields. The calculated field is as follows "Real Price: IIf(([Seat Assignments].[Price Category]="Crossbar") And ([Contract Length]>1),200,[Full Price])". Real Price is needed for a VBA ...

  1. #1

    Default Access Upsize to SQL Query Problem

    I've been called upon to upsize a fairly basic Access
    Database to SQL so we can integrate the data with our SQL
    based CRM. We need to leave the front end intact so I
    chose to convert to an ADP. I've managed to get all the
    tables over and a fair amount of the forms, reports and
    querries. What I need help with is a select query that
    didn't upsize. I've recreated the query except two
    calculated fields. The calculated field is as
    follows "Real Price: IIf(([Seat Assignments].[Price
    Category]="Crossbar") And ([Contract Length]>1),200,[Full
    Price])". Real Price is needed for a VBA module that
    calculates a field on a form that is used for 4 other
    fields. Should I be doing this in the query, or the VBA
    module? I've tried the Case statement but Access
    gives "Teh desigener does not graphically support the Case
    SQL construct." The IIF statement gets changed into a
    text entry so the value of Real Price for every entry is
    IIf(([Seat Assignments... I've also tried entering the
    IIF in the module replacing the [Real Price] in the
    following equation"FullSeasonTotal = Nz(DSum("[Real
    Price]", "Full Season Total", "[Account Number] =" & Forms!
    [Season Ticket Holders].[Account Number]), 0)."
    Brian Guest

  2. #2

    Default Re: Access Upsize to SQL Query Problem

    Use CASE instead of IIF:

    IIf(([Seat Assignments].[Price
    Category]="Crossbar") And ([Contract Length]>1),200,[Full
    Price])".

    becomes

    CASE
    WHEN [Seat Assignments].[Price Category]="Crossbar" And [Contract Length]>1
    THEN 200 ELSE [Full Price]
    END


    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Brian" <com> wrote in message news:00cf01c361c3$fd8ed860$gbl...
    I've been called upon to upsize a fairly basic Access
    Database to SQL so we can integrate the data with our SQL
    based CRM. We need to leave the front end intact so I
    chose to convert to an ADP. I've managed to get all the
    tables over and a fair amount of the forms, reports and
    querries. What I need help with is a select query that
    didn't upsize. I've recreated the query except two
    calculated fields. The calculated field is as
    follows "Real Price: IIf(([Seat Assignments].[Price
    Category]="Crossbar") And ([Contract Length]>1),200,[Full
    Price])". Real Price is needed for a VBA module that
    calculates a field on a form that is used for 4 other
    fields. Should I be doing this in the query, or the VBA
    module? I've tried the Case statement but Access
    gives "Teh desigener does not graphically support the Case
    SQL construct." The IIF statement gets changed into a
    text entry so the value of Real Price for every entry is
    IIf(([Seat Assignments... I've also tried entering the
    IIF in the module replacing the [Real Price] in the
    following equation"FullSeasonTotal = Nz(DSum("[Real
    Price]", "Full Season Total", "[Account Number] =" & Forms!
    [Season Ticket Holders].[Account Number]), 0)."

    Tom Guest

  3. #3

    Default Re: Access Upsize to SQL Query Problem

    Thanks Tom,
    I've tried the Case statement but Access
    gives "The desigener does not graphically support the Case
    SQL construct.", and I can't get past that.




    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Brian Guest

  4. #4

    Default Re: Access Upsize to SQL Query Problem

    Convert the SQL into a stored proc or view and then have Access use that.

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Brian Cooper" <com> wrote in message news:#f58$phx.gbl...
    Thanks Tom,
    I've tried the Case statement but Access
    gives "The desigener does not graphically support the Case
    SQL construct.", and I can't get past that.




    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!

    Tom Guest

Similar Threads

  1. Query against MS Access problem
    By Scott_thornton in forum Coldfusion Database Access
    Replies: 3
    Last Post: August 13th, 03:30 PM
  2. Access Union Query in CF--Problem
    By weezerboy in forum Coldfusion Database Access
    Replies: 7
    Last Post: November 9th, 01:53 AM
  3. MS Access d/b saved parameter query problem
    By Larry Rekow in forum ASP Database
    Replies: 3
    Last Post: August 3rd, 07:38 PM
  4. HELP! Access upsize problem
    By Ken Schaefer in forum ASP Database
    Replies: 1
    Last Post: June 30th, 11:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139