Results 1 to 11 of 11
  1. #1
    stalk is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2014
    Posts
    143

    How to auto populate results bases on field values?

    if t1 ,p1,h1 = 0 then res= negative


    if t1,p1,h1 > 0 then res=positive
    if t1 or p1 or h1 >0 and test =1 then res= 'Repeat'
    if t1 or p1 or h1 > 0 and test= 2 then 'invalid'

    How can I auto populate the Res

    Is it possible in query builder or do we need to use VBA coding

    Thank you

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    better if your provide some example data and the result required

    also clarify what this means

    if t1 ,p1,h1 = 0 then res= negative

    is the , supposed to be OR? AND? +? -? something else?

  3. #3
    stalk is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2014
    Posts
    143
    Sorry for not being clear.

    if t1 =0 and p1=0 and h1 = 0 then res= negative

    if t1 >0 and p1> 0 and h1 > 0 then res=positive
    if t1 =12.3 or p1=0 or h1 > 23.1 and test =1 then res= 'Repeat'
    if t1=0 or p1=15.6 or h1 > 20.1 and test= 2 then 'invalid'

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    OK, so where are you want to use this code? in a query? or vba?

    And what does repeat mean? the values wont have changed so what is the point of repeating? you example shows the values changing so do you mean go to next record, next array element or something similar?

    might be an idea if you explain exactly what you are trying to achieve in the real world

  5. #5
    stalk is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2014
    Posts
    143
    VBA or Query is fine, what ever calculates accurately. But prefer to use this in query.

    Repeat is just for my records to know I need to re test that specific sample. 80% of the results are either positive or negative.

    hand full of samples are repeats/ invalid (res= 'Repeat' or res= 'invalid') , having hard time to manually track.
    res= 'invalid' will allow me to not process with any further testing with that sample


    Thank you so much for your help

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    still not clear what you mean by 'repeat'.

    provide some example data and the result you require from that example data - provide perhaps 20 or 30 rows so you can cover all eventualities

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You seem to have 3 query fields thus you could write a function in a standard module and pass all 3 to it and return the result in a calculated field in that query. The calculated field could be
    Res:Result(fld1,fld2,fld3) where fld1 etc. are the query field names, and the function could look like

    Code:
    Public Function Result(db1 As Double, dbl2 As Double, dbl3 As Double) As String
      If t1 = 0 And p1 = 0 And h1 = 0 Then Result = "negative"
      If t1 > 0 And p1 > 0 And h1 > 0 Then Result = "positive"
      If t1 = 12.3 Or p1 = 0 Or h1 > 23.1 And test = 1 Then Result = "Repeat"
      If t1 = 0 Or p1 = 15.6 Or h1 > 20.1 And test = 2 Then Result "invalid"
    End Function
    That's just a rudimentary approach - it contains no error handler and that was written with the assumption that your values are numbers and that there are no null values or empty strings in any records of those 3 fields. I can't see where you ever said exactly what you want as the end result. That code will only calculate the result, which you could view in a form if that's what you want. What it won't do is update your table if that's the end goal.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    stalk is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2014
    Posts
    143
    All these fields are in temp table and I need to update all these 3 fields Plus Results field in Main table.
    Is there any other way to calculate and save these results?

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    First things first. Follow the suggestion in post 7. If the query performs as needed, then we can worry about how to get it into some table. If it doesn't there's no point in attempting that.

    EDIT - Any other way besides what? Is there something not right about what I suggested?

  10. #10
    stalk is offline Competent Performer
    Windows 8 Access 2003
    Join Date
    May 2014
    Posts
    143
    User defined Function worked perfectly.
    Can I ask you one more question : Based on Result I want to check final (check field) as final or all Result except Repeat.

  11. #11
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I think the query that contains that calculated field will not be updatable because of that field, so try creating a new Update query that contains the table and that query and updates your table based on the calculation.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 07-06-2018, 01:30 PM
  2. Replies: 7
    Last Post: 08-19-2016, 08:16 AM
  3. Replies: 1
    Last Post: 06-02-2015, 10:07 AM
  4. Auto Populate Txt Field
    By Andyjones in forum Forms
    Replies: 1
    Last Post: 03-27-2012, 05:53 PM
  5. Replies: 3
    Last Post: 10-03-2011, 02:33 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums