Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Mar 2008
    Location
    Suburb of Chicago, Illinois
    Posts
    56

    Unanswered: Custom Field problem - Access 2003

    I'm trying to define a new report field in my query (Query Builder) using a large amount of code, but Access says it's too long and trunckates it. I need the entire code to properly define the field. Is there any way to get it all into the report field definition? NOTE: I can't show the entire code in this posting as it exceed the 10,000 char limit (It's about 49,000 char long).

    IF [InvoiceDetail.MaterialId] = "4511100003"
    AND [InvoiceHeader.BillingType] = "G2"
    THEN [InvoiceDetail.Quantity] *-2
    ELSE IF [InvoiceDetail.MaterialId] = "4511100003"
    AND [InvoiceHeader.BillingType] = "RE"
    THEN [InvoiceDetail.Quantity] *-2
    ELSE IF [InvoiceDetail.MaterialId] = "4511100003"
    AND [InvoiceHeader.BillingType] = "S1"
    THEN [InvoiceDetail.Quantity] *-2
    ELSE IF [InvoiceDetail.MaterialId] = "4511100003"
    AND [InvoiceHeader.BillingType] = "ZLG2"
    THEN [InvoiceDetail.Quantity] *-2
    ELSE IF [InvoiceDetail.MaterialId] = "4511100003"
    AND [InvoiceHeader.BillingType] = "ZZSA"
    THEN [InvoiceDetail.Quantity] *-2
    ELSE IF [InvoiceDetail.MaterialId] = "4511100003"
    AND [InvoiceHeader.BillingType] = "ZZSR"
    THEN [InvoiceDetail.Quantity] *-2
    ELSE IF [InvoiceDetail.MaterialId] = "4511100003"
    AND [InvoiceHeader.BillingType] = "ZZG2"
    THEN [InvoiceDetail.Quantity] *-2
    ELSE IF [InvoiceDetail.MaterialId] = "4511100003"
    THEN [InvoiceDetail.Quantity] *2
    ELSE IF [InvoiceDetail.MaterialId] = "4511100015"
    AND [InvoiceHeader.BillingType] = "G2"
    THEN [InvoiceDetail.Quantity] *-2
    ELSE IF [InvoiceDetail.MaterialId] = "4511100015"
    AND [InvoiceHeader.BillingType] = "RE"
    THEN [InvoiceDetail.Quantity] *-2
    ELSE IF [InvoiceDetail.MaterialId] = "4511100015"
    AND [InvoiceHeader.BillingType] = "S1"
    THEN [InvoiceDetail.Quantity] *-2
    ELSE IF [InvoiceDetail.MaterialId] = "4511100015"
    AND [InvoiceHeader.BillingType] = "ZLG2"
    THEN [InvoiceDetail.Quantity] *-2
    ELSE IF [InvoiceDetail.MaterialId] = "4511100015"
    AND [InvoiceHeader.BillingType] = "ZZSA"
    THEN [InvoiceDetail.Quantity] *-2
    ELSE IF [InvoiceDetail.MaterialId] = "4511100015"
    AND [InvoiceHeader.BillingType] = "ZZSR"
    THEN [InvoiceDetail.Quantity] *-2
    ELSE IF [InvoiceDetail.MaterialId] = "4511100015"
    AND [InvoiceHeader.BillingType] = "ZZG2"
    THEN [InvoiceDetail.Quantity] *-2
    ELSE IF [InvoiceDetail.MaterialId] = "4511100015"
    THEN [InvoiceDetail.Quantity] *2
    ELSE IF [InvoiceDetail.MaterialId] = "4511100020"
    AND [InvoiceHeader.BillingType] = "G2"
    THEN [InvoiceDetail.Quantity] *-2
    ELSE IF [InvoiceDetail.MaterialId] = "4511100020"
    AND [InvoiceHeader.BillingType] = "RE"
    THEN [InvoiceDetail.Quantity] *-2
    ELSE IF [InvoiceDetail.MaterialId] = "4511100020"
    AND [InvoiceHeader.BillingType] = "S1"
    THEN [InvoiceDetail.Quantity] *-2
    ELSE IF [InvoiceDetail.MaterialId] = "4511100020"
    AND [InvoiceHeader.BillingType] = "ZLG2"
    THEN [InvoiceDetail.Quantity] *-2
    ELSE IF [InvoiceDetail.MaterialId] = "4511100020"
    AND [InvoiceHeader.BillingType] = "ZZSA"
    THEN [InvoiceDetail.Quantity] *-2
    ELSE IF [InvoiceDetail.MaterialId] = "4511100020"
    AND [InvoiceHeader.BillingType] = "ZZSR"
    THEN [InvoiceDetail.Quantity] *-2
    ELSE IF [InvoiceDetail.MaterialId] = "4511100020"
    AND [InvoiceHeader.BillingType] = "ZZG2"
    THEN [InvoiceDetail.Quantity] *-2
    ELSE IF [InvoiceDetail.MaterialId] = "4511100020"
    THEN [InvoiceDetail.Quantity] *2
    ELSE IF [InvoiceDetail.MaterialId] = "4511100027"
    AND [InvoiceHeader.BillingType] = "G2"
    THEN [InvoiceDetail.Quantity] *-2
    ELSE IF [InvoiceDetail.MaterialId] = "4511100027"
    AND [InvoiceHeader.BillingType] = "RE"
    THEN [InvoiceDetail.Quantity] *-2
    ELSE IF [InvoiceDetail.MaterialId] = "4511100027"
    AND [InvoiceHeader.BillingType] = "S1"
    THEN [InvoiceDetail.Quantity] *-2
    ELSE IF [InvoiceDetail.MaterialId] = "4511100027"
    AND [InvoiceHeader.BillingType] = "ZLG2"
    THEN [InvoiceDetail.Quantity] *-2
    ELSE IF [InvoiceDetail.MaterialId] = "4511100027"
    AND [InvoiceHeader.BillingType] = "ZZSA"
    THEN [InvoiceDetail.Quantity] *-2
    ELSE IF [InvoiceDetail.MaterialId] = "4511100027"
    AND [InvoiceHeader.BillingType] = "ZZSR"
    THEN [InvoiceDetail.Quantity] *-2
    ELSE IF [InvoiceDetail.MaterialId] = "4511100027"
    AND [InvoiceHeader.BillingType] = "ZZG2"
    THEN [InvoiceDetail.Quantity] *-2
    ELSE IF [InvoiceDetail.MaterialId] = "4511100027"
    THEN [InvoiceDetail.Quantity] *2
    ELSE IF [InvoiceDetail.MaterialId] = "4511100037"
    AND [InvoiceHeader.BillingType] = "G2"
    THEN [InvoiceDetail.Quantity] *-2
    ELSE IF [InvoiceDetail.MaterialId] = "4511100037"
    AND [InvoiceHeader.BillingType] = "RE"
    THEN [InvoiceDetail.Quantity] *-2
    ELSE IF [InvoiceDetail.MaterialId] = "4511100037"
    AND [InvoiceHeader.BillingType] = "S1"
    THEN [InvoiceDetail.Quantity] *-2
    ELSE IF [InvoiceDetail.MaterialId] = "4511100037"
    AND [InvoiceHeader.BillingType] = "ZLG2"
    THEN [InvoiceDetail.Quantity] *-2
    ELSE IF [InvoiceDetail.MaterialId] = "4511100037"
    AND [InvoiceHeader.BillingType] = "ZZSA"
    THEN [InvoiceDetail.Quantity] *-2
    ELSE IF [InvoiceDetail.MaterialId] = "4511100037"
    AND [InvoiceHeader.BillingType] = "ZZSR"
    THEN [InvoiceDetail.Quantity] *-2
    ELSE IF [InvoiceDetail.MaterialId] = "4511100037"
    AND [InvoiceHeader.BillingType] = "ZZG2"
    THEN [InvoiceDetail.Quantity] *-2
    ELSE IF [InvoiceDetail.MaterialId] = "4511100037"
    THEN [InvoiceDetail.Quantity] *2
    ELSE IF [InvoiceDetail.MaterialId] = "4511100045"
    AND [InvoiceHeader.BillingType] = "G2"
    THEN [InvoiceDetail.Quantity] *-2
    ELSE IF [InvoiceDetail.MaterialId] = "4511100045"
    AND [InvoiceHeader.BillingType] = "RE"
    THEN [InvoiceDetail.Quantity] *-2
    ELSE IF [InvoiceDetail.MaterialId] = "4511100045"
    AND [InvoiceHeader.BillingType] = "S1"
    THEN [InvoiceDetail.Quantity] *-2
    ELSE IF [InvoiceDetail.MaterialId] = "4511100045"
    AND [InvoiceHeader.BillingType] = "ZLG2"
    THEN [InvoiceDetail.Quantity] *-2
    ELSE IF [InvoiceDetail.MaterialId] = "4511100045"
    AND [InvoiceHeader.BillingType] = "ZZSA"
    THEN [InvoiceDetail.Quantity] *-2
    ELSE IF [InvoiceDetail.MaterialId] = "4511100045"
    AND [InvoiceHeader.BillingType] = "ZZSR"
    THEN [InvoiceDetail.Quantity] *-2
    ELSE IF [InvoiceDetail.MaterialId] = "4511100045"
    AND [InvoiceHeader.BillingType] = "ZZG2"
    THEN [InvoiceDetail.Quantity] *-2
    ELSE IF [InvoiceDetail.MaterialId] = "4511100045"
    THEN [InvoiceDetail.Quantity] *2
    ELSE IF [InvoiceDetail.MaterialId] = "4511100055"
    AND [InvoiceHeader.BillingType] = "G2"
    THEN [InvoiceDetail.Quantity] *-2
    ELSE IF [InvoiceDetail.MaterialId] = "4511100055"
    AND [InvoiceHeader.BillingType] = "RE"
    THEN [InvoiceDetail.Quantity] *-2
    ELSE IF [InvoiceDetail.MaterialId] = "4511100055"
    AND [InvoiceHeader.BillingType] = "S1"
    THEN [InvoiceDetail.Quantity] *-2
    ELSE IF [InvoiceDetail.MaterialId] = "4511100055"
    AND [InvoiceHeader.BillingType] = "ZLG2"
    THEN [InvoiceDetail.Quantity] *-2
    ELSE IF [InvoiceDetail.MaterialId] = "4511100055"
    AND [InvoiceHeader.BillingType] = "ZZSA"
    THEN [InvoiceDetail.Quantity] *-2
    ELSE IF [InvoiceDetail.MaterialId] = "4511100055"
    AND [InvoiceHeader.BillingType] = "ZZSR"
    THEN [InvoiceDetail.Quantity] *-2
    ELSE IF [InvoiceDetail.MaterialId] = "4511100055"
    AND [InvoiceHeader.BillingType] = "ZZG2"
    THEN [InvoiceDetail.Quantity] *-2
    ELSE IF [InvoiceDetail.MaterialId] = "4511100055"
    THEN [InvoiceDetail.Quantity] *2
    ELSE IF [InvoiceDetail.MaterialId] = "4511100056"
    AND [InvoiceHeader.BillingType] = "G2"
    THEN [InvoiceDetail.Quantity] *-1
    ELSE IF [InvoiceDetail.MaterialId] = "4511100056"
    AND [InvoiceHeader.BillingType] = "RE"
    THEN [InvoiceDetail.Quantity] *-1
    ELSE IF [InvoiceDetail.MaterialId] = "4511100056"
    AND [InvoiceHeader.BillingType] = "S1"
    THEN [InvoiceDetail.Quantity] *-1
    ELSE IF [InvoiceDetail.MaterialId] = "4511100056"
    AND [InvoiceHeader.BillingType] = "ZLG2"
    THEN [InvoiceDetail.Quantity] *-1
    ELSE [InvoiceDetail.Quantity]

  2. #2
    Join Date
    Mar 2008
    Location
    Suburb of Chicago, Illinois
    Posts
    56

    If Then Else problem

    I'm trying to replicate in MS Access 2003, code I use in Crystal Reports. Can someone help me debug the following code?

    IF [InvoiceHeader.BillingType] = "RE"
    THEN ([InvoiceDetail.TotalNetPrice]-[InvoiceDetail.FreightPrice]) *-1
    ELSE IF [InvoiceHeader.BillingType] = "G2"
    THEN ([InvoiceDetail.TotalNetPrice] -[InvoiceDetail.FreightPrice]) *-1
    ELSE ([InvoiceDetail.TotalNetPrice] -[InvoiceDetail.FreightPrice])

    Thanks,

    Krazy

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Move the code into a global function in a Module and call it from your query.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    I think that the problem you are having is with the THEN part. You need assign the expression to something. For example:

    "THEN somevariable = ([InvoiceDetail.TotalNetPrice]-[InvoiceDetail.FreightPrice]) *-1"

    Also, don't forget to end the if statement with "End If"

    C

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have you looked at the help system for control statements
    the general style is
    if (condition) = value then
    'insert some statements
    elseif if (condition) = value then
    'insert some statements
    elseif (condition) = value then
    'insert some statements
    else
    'insert some statements
    end if

    however you would be better of in my books, as its a single field being examined to use a select case
    eg
    select case variable
    case is = valueA
    'insert some statements
    case is = valueB
    'insert some statements
    case else
    end select

    the alternative is to have a SQL table with the rule encoded

    eg FreightRates
    RateCode Description Factor
    RE <desciption> -1
    G2 <Description> -1
    youd then pull in the rate adjustment factor from the table on a join
    but that may be a sophistication to far for now....
    HTh
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You're better off placing all of thois "rules" into a table and joing to it to get the correct value
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    for the sheer number of conditions and terms I think you 'MUST' go down the route of pulling these adjustment factors from a table... otherwise uyou will be forever dipping into this application to make chanegs as the amrketing bozo's dream up some new scheme.
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    *****NOTE these threads have been merged *****
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Mar 2008
    Location
    Suburb of Chicago, Illinois
    Posts
    56

    Thank you - one more problem please

    Thank you all for your help.
    I was able to debug the If THEN ELSE statements and
    I was able to include the large amount of code into a module.
    Last edited by KrazyKasper; 05-15-08 at 13:51.

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what function or procedure is this code placed in.. it has to reside somewhere in an Access project as a subroutine or function. a sub doesn't have a return value, other than that it the same as as a function
    mind you I don't think your code is actually valid
    there is no assignment of the value// id expect something like
    CreditItem = ([InvoiceDetail.TotalNetPrice] - [InvoiceDetail.FreightPrice]) * -1

    Code:
    If [InvoiceHeader.BillingType] = "RE" Then
      CreditItem ([InvoiceDetail.TotalNetPrice] - [InvoiceDetail.FreightPrice]) * -1
    ElseIf [InvoiceHeader.BillingType] = "G2" Then
      CreditItem ([InvoiceDetail.TotalNetPrice] - [InvoiceDetail.FreightPrice]) * -1
    ElseIf [InvoiceHeader.BillingType] = "S1" Then
       CreditItem ([InvoiceDetail.TotalNetPrice] - [InvoiceDetail.FreightPrice]) * -1
    ... and so on
    elseif MUST end with a End If, and its good practice to have a else as well

    I still think you would be better off using a select case statement if you 'MUST' go down this route

    Code:
    select case [InvoiceHeader.BillingType] 
      case is = "RE" :CreditItem ([InvoiceDetail.TotalNetPrice] - [InvoiceDetail.FreightPrice]) * -1
      case is =  "G2" : CreditItem ([InvoiceDetail.TotalNetPrice] - [InvoiceDetail.FreightPrice]) * -1
      case is =  "S1" : CreditItem ([InvoiceDetail.TotalNetPrice] - [InvoiceDetail.FreightPrice]) * -1
    ..and so on

    select CASE must terminate with a 'End Select', and is alwasy good practice to have a Case Else


    BUT I think th real solution to this problem is a rules based SQL Table
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Mar 2008
    Location
    Suburb of Chicago, Illinois
    Posts
    56

    Thanks Healdem

    Thanks healdem for your suggestion.
    However, I am very new to Access and don't know anything about CASE statements or how to use tables.
    Given the time, I might be able to learn it but I don't have that luxury right now.

    Krazy

  12. #12
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I am very new to Access and don't know anything about CASE statements or how to use tables.
    In all honesty, this is scary. The first thing you need to learn in any database development tool is how to use tables.

    It's not a luxury. It's a necessity.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by KrazyKasper
    Thanks healdem for your suggestion.
    However, I am very new to Access and don't know anything about CASE statements or how to use tables.
    Given the time, I might be able to learn it but I don't have that luxury right now.

    Krazy
    well, in which case now is a very, VERY good time to start learning.
    the select case in my books is preferable in this position because its easier to read.. in terms of efficiency in running the program it makes little or no difference. but code that is easier to read is code that is easier to maintain n the medium to long term.

    you have the inbuilt access help which although not perfect (what is) it is still pretty good in my books. its even context sensitive (place the cursor over the word and press F1, acesss even has intellisense, which means as you type in a function or property the IDE displays choixes of properties or parameters required.. generally speaking, as an Access developer our first port of call should be the help system.

    you have been given an example of the select case statement....

    I could understand why someone fresh out of the box may choose not to implement this in a rules table.. it can be a bit daunting.... however that sort of solution is right up there for some of the knowledgeable people on this forum.... people like PKStormy, StarTrekker, IzyRider and many, many others. But to come out with statements like...."Given the time, I might be able to learn it but I don't have that luxury right now."... doesn't strike me that you want to learn, you just want solutions.

    one of the things you have to accept is that each language has a different way of doing things.. what works in Cystal Reports may wrk only there, there is some common ground between VB & VBA.

    have you resolved your problem with the assignment within your if statement.
    Code:
    CreditItem ([InvoiceDetail.TotalNetPrice] - [InvoiceDetail.FreightPrice]) * -1
    is perfectly valid VB
    providng there is a function called credititem.
    but I suspect you want to assign the value of [code]([InvoiceDetail.TotalNetPrice] - [InvoiceDetail.FreightPrice]) * -1[code] to another variable, control or column in the db/form/report. presumably the control/variable is called CreditItem
    so it should be
    Code:
    CreditItem = ([InvoiceDetail.TotalNetPrice] - [InvoiceDetail.FreightPrice]) * -1
    or
    Code:
    CreditItem.text = ([InvoiceDetail.TotalNetPrice] - [InvoiceDetail.FreightPrice]) * -1
    looking at your code again, for readability purposes I;d refine it a bit
    Code:
    Dim ShippingFactor as single 'strictly speaking not required as single
    ShippingFactor = 0 ' set our default value
    select case [InvoiceHeader.BillingType] 
      case is = "RE" :ShippingFactor = -1
      case is =  "G2" : ShippingFactor = -1
      case is =  "S1"..... 'add more conditions as required
    end select
    CreditItem = ([InvoiceDetail.TotalNetPrice] - [InvoiceDetail.FreightPrice]) *shippingfactor
    you can coalesce similar values into the same statement
    case is="RE","G2","S1"
    whereas if you did that as an IF
    if [InvoiceHeader.BillingType] ="RE" or [InvoiceHeader.BillingType] ="G2" or [InvoiceHeader.BillingType] ="S1"
    you see what Imean about a select case being a lot easier to read?

    the point abou tthe table is that it doesn't require application changes each time some wazzock come up with a new code or discont rule.. its down to them to enter the data. There;s nothing better than when they come to you and demand you make a change right here right now, and you are up to your ears in something else to tell 'em to make their changes to their data.

    the rule table is undoubtedly the way to go with this sort of problem, but if its a one off then it may not be worth the pain of learning it. if you think you are going to be developing other things in Access then in my books ou are going to have to learn how to use tables and SQL at some time, so you may as well get over it now, rather than delay it for another day.

    A final comment, there are some experienced developer on here, its rare for them to suggest something without good reason. Very often the reason the original developer is hitting a problem is because they have the blinkers on, and can see a specific solution, but cannot understand why they cannot make that solution work in Access.... sometimes y'just have to accept that the language wont support what you want to do in the way you want to do it. You've already found that with a monstrous if construct... something that wold probably have been replaced with a few lnes of code (say 5..10) and dumping the responsibility for maintaining all these discount codes onto the users of the system, as opposed to trying to hard code them..... hard coding means YOU take on the responsibility for the rules, dumping them into data, dumps the responsibility back onto the people who should carry it.. thois that think up these rules in the first place.

    HTH
    I'd rather be riding on the Tiger 800 or the Norton

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by healdem
    well, in which case now is a very, VERY good time to start learning.
    Quote of the month.
    George
    Home | Blog

  15. #15
    Join Date
    Mar 2008
    Location
    Suburb of Chicago, Illinois
    Posts
    56

    Thanks!

    I'll give it a shot.
    Yes I did resolve my problem with the IF statement.
    Thanks,

    Krazy

Posting Permissions

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