Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2013
    Posts
    23

    Unanswered: Multiple condition If statement

    Hi I am getting compile errors on the following If statement.

    Private Sub ProductDescription_AfterUpdate()

    If me!ProductDescription Contains "unprimed" And subfrmOpLink.opnumber = 30
    then [subfrmRoutingop30.PL1] = 0;
    Else
    MsgBox ("This Product is Unprimed and cannot be sent to a Paintline - Please adjust routings.")

    End If
    End Sub

    Can anyone help?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what does contains mean........
    never heard of it
    do you mean if the descrtiption has 'unprimed' in it somewhere then thats a different kettle of fish.. thats not a problem with a multiple if, but inventing capabilities that are not part of Access. but you can use the instr function to do what you want.

    bear in mind that if you are searching for text within text you need to take note of capitalisation and spelling typos (eg 'unprimed' isn't the same as 'un primed' or 'UNPRIMED' or any other permutation. ytou may wellneed to sanitise your data before using the instr function
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2014
    Posts
    8

    I need Help

    Hi...

    I want to add this "=+SUM(B13:B15)+QUOTIENT((SUM(C13:C15)+QUOTIENT((S UM(D1315)),9)),20)" in MS Access's report and as well as in form also. Please help me

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    SUM(B13:B15) and the like are Excel notation and have no meaning in Access. You seem to believe that Access works the same way Excel does, which is totally wrong.
    Have a nice day!

  5. #5
    Join Date
    Jun 2014
    Posts
    8
    Yes you are right sir, this is excel formula I want to add same calculations in my ms access report. what should I do?

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You cannot reference the contents of an Access table using the Excel cell notation. Regarding the contents of an Access (or of any relational database for that matter) table (i.e. the object that stores the data), the notion of "first", "second", etc. does not exists per se. To retrieve a value from a table, you use a query or a domain function (DLookUp(), DCount(), DSum(), etc.). You specify the column using it's name (as it is defined in the table definition) and the row by using a criteria.
    Have a nice day!

  7. #7
    Join Date
    Jun 2014
    Posts
    8
    I have a report like this.

    Description. SumOfField1 SumOfField2 SumOfField3
    Descr.1 223 256 19
    Descr.1 253 563 28
    Descr.1 523 18 7
    Descr.1 425 286 5

    Total ____________________________________
    1424 1123 59

    1.
    Now I want SumOfField2 should be less then 20 if it goes more then 20 add "1" in SumOfField1 and as well as in total. 1123 should not increase more then 20 if =>20 add "1" in SumOfField1
    Example: 1123/20 = 56.15 - 56 should be add in SumOfField1 and .15 remain in SumOfField2.

    2.
    Now I want SumOfField3 should be less then 9 if it goes more then 9 add "1" in SumOfField2 and as well as in total. 59 should not increase more then 9 if =>9 add "1" in SumOfField2
    Example: 59/9 = 6.55. 6 should be add in SumOfField2 and .55 remain in SumOfField3.
    Last edited by mwaleedgul; 09-26-14 at 03:38.

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by mwaleedgul View Post
    I have a report like this.

    Description. SumOfField1 SumOfField2 SumOfField3
    Descr.1 223 256 19
    Descr.1 253 563 28
    Descr.1 523 18 7
    Descr.1 425 286 5

    Total ____________________________________
    1424 1123 59
    Sorry but no, you don't. You must have one or several tables where the data is stored. From this or these tables, you can possibly extract data using a query. In that query, you can add computed columns using functions (SQL functions, built-in VBA function or user-defined VBA functions).

    The report has a RecordSource property. This property can be the name of a table, the name of a report or a SQL expression that extract data from one or several tables. A report is an Access object that has a collection of controls. These control can be "data-aware", meaning that they can be bound to a column in the data set that the RecordSource object returns. To a certain extend, they can also perform computations on columns from the data set. In both cases, they display the bound data or the result of the computation(s) performed on the bound data.

    Personally, I prefer to perform all the computations in a query and only use the report for presenting the resulting data but it's more a question of personal taste than a general rule.

    If you have questions regarding a specific problem, please try to formulate it in a way that's understandable by providing the necessary and relevant information (structure of the table(s), SQL expression of the query (queries), etc.). See: http://www.dbforums.com/microsoft-ac...ml#post4534485
    Have a nice day!

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    put some code behind the report that handles this sort of thing
    you don't 'have' to do everything in SQL (the query/queries), nor do you 'have' to use domain functions. using doman functions ia a report (especiailly in the base query can or detail print can cause serious perfromance issues.

    if you want spreadhseet style operations on specific 'cells' then use code in the detail (and or gfroup ) "on format" events
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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