Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2020
    Posts
    4

    Unhappy Evaluate the number in a text field prefixed by "<" or ">"

    Hi anf thanks for any help you can offer
    I have subreport that has the results of laboratory analyses. Field name is [Result] this is a text field as it may contain a number (e.g. 200, 1.3, <3 or >1,000) or text (e.g. No Growth or Not Detected).
    My problem only exists when I am evaluatng the [Result] field when it has a '< or '>' sign beforethe number. I also have on the report the acceptale limit, if it applies in a field called [SNRV]

    'On Format' forthe Details section coding is my dilema, I need to put ticks or crosses at the start of the line, that is, before the [Result] if it is a pass or fail

    I have no trouble doing that when I have a number as the result.

    But let's say the [Result] is <1 and the aceptable limit is <3 that is, the [SNRV] is 3. In this case I need to test if the characters after the '<' sign are less than the [SNRV] so I can mark tis as a pass.

    Even worse, when the [Result] is <10 and the acceptable limit is <3. In this case I need to determine that I can not evaluate the result and not put a tick or cross against this result.

    I tried

    If Me![Result] like "<*) then ' this line works fine
    If Mid(Me![Result],2) <= Me![SNRV] then ' -->>>>But this doesn't work
    Me![Tick].Visible = True
    Me![Cross].Visible = False
    Else
    Me![Tick].Visible = False
    Me![Cross].Visible = False
    End If
    End If



    All my other programing regarding'<' and '>' results can be done once I determine how to evaluate the number in a field after a "<' or '>' sign and compare it to a numeric field result.

    Any ideas you gurus of Access programming? Please help I have run out of ideas.

    Cheers
    Glen - Simple Scientist from Australia

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Glen,

    Can you show some sample date for the Results field with </> ? If the data only has the operators and the values then it should be fairly easy to isolate the numeric value using some expression similar to:

    NumericValue:IIF(Instr([Result],">")>0, Replace("[Result],">",""),IIf(Instr([Result],"<")>0, Replace("[Result],"<",""),[Result]))

    Similarly you would get the operator:
    Operator:IIF(Instr([Result],">")>0, ">",IIf(Instr([Result],"<")>0,"<","N/A"))

    And now you would be able to get your comparison against the [SNRV] field. I suggest you add these calculated fields in the query (the reports's record source) and also add the Pass/Fail calculated field(s) there too.

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    Join Date
    Dec 2020
    Posts
    4
    Hi Vlad

    For example a food sample is tested for E. coli for the bacteria E. coli where the acceptable limit is less than 3 bacteria per gram of food. That is the [SNRV] = 3

    The results of the test (performed by our standard method where 3 is the lower limit of detection) 'cfu per gram' could be

    "27"
    "3"
    "<3"
    ">3,000"
    or "<10" (if the analysis is performed by a method where 10 is the lower limit of detection)

    In each case on the report, issued to the customer, I must show the actual result as above - "27", "3", "<3", ">3,000" or "<10". But in the Details On Format I need to turn on or off the Tick and/or Cross field at the start of the line where the result appears.
    e.g.

    √ E. coli: <3 cfu/gram Acceptable Limit = <3
    X E. coli: 27 cfu/gram Acceptable Limit = <3
    X E. coli: 3 cfu/gram Acceptable Limit = <3
    X E. coli: >3,000 cfu/gram Acceptable Limit = <3
    E. coli: <10 cfu/gram Acceptable Limit = <3

    I'm not concerned with ">" I just need a way to work out that <10 is not a result that I can interpret because the acceptable limit is <3.

    Thanks

    Glen

  4. #4
    Join Date
    Dec 2020
    Posts
    4
    Hi Vlad

    My code If Me![RESULT] Like "<*" Then already evaluates that the field begins with a "<"

    If it does start with a "<", I need is a way to read the rest of the [Result] field and determine if the number after the "<" is greater than the acceptable limit [SNRV]

    that is - that a [Result] of "<3" IS NOT LARGER than an acceptable limit of 3 [SNRV] and therefore I can put a √ at the start of the result line
    But a [Result] of "<10" IS LARGER than an acceptable limit of 3 [SNRV] and therefore I CANNOT put a √ at the start of the result line as the real result could be less than 10 but still greater than 3.

    Thanks

    Glen

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Glen,

    Use the Replace function to get the numeric value then compare it to SNVR:

    Code:
    If If Me![RESULT] Like "<*" then
       If CDBL(Replace([Result],"<",""))>[SNVR] then Exit Sub 'do nothing as in <10 example
       'rest of your code to turn on/off the tick/cross
    
    
    End Id
    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  6. #6
    Join Date
    Dec 2020
    Posts
    4
    You NAILED IT Vlad

    So many thanks

    Glen

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You're very welcome Glen!

    Happy holidays!

    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  8. #8
    Join Date
    Apr 2017
    Posts
    1,673
    Much easier and more efficient would be have a numeric field for result, and a smallint field e.g. ResultType in your table. The smallint field will have e.g. values 1 for "equals", 2 for "<" and 3 for ">", and in form it is linked to combo control.

    Your code will then be
    Code:
    If Me![ResultType] = 2 then 
         ' the tesult when "<"
    ElseIf Me![ResultType] = 1 then 
         ' the result when "="
    ElseIf Me![ResultType] = 3 then 
         ' the result when ">"
    Else
         "the result when none above applies"
    End If
    

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

Similar Threads

  1. Replies: 4
    Last Post: 11-14-2019, 11:30 AM
  2. Simple table relationships ("faces" to "spaces" to "chairs")
    By skydivetom in forum Database Design
    Replies: 36
    Last Post: 07-20-2019, 01:49 PM
  3. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  4. Replies: 3
    Last Post: 04-22-2013, 06:08 AM
  5. Export "Query or Report" to a "Delimited Text File"
    By hawzmolly in forum Import/Export Data
    Replies: 3
    Last Post: 08-31-2012, 08:00 AM

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