Results 1 to 6 of 6

Data type mismatch - handling null values

  1. #1
    shank is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    68

    Data type mismatch - handling null values

    I have to extract numbers as currency from a text string.

    Sample String: [item: 2 Yr Zero Hassle Protection] [description: Protection Plan to be mailed/emailed within 10 day] [unit_price: 12.34] [ext_price: 12.34]

    See below code. As long as the string is present, no problem. But if the 12.34 (numbers) is not present I get a data mismatch error in the report. The query runs without error, but the report gives the error. The report fields are Currency. Simply, if there's no value or a null value, how do I get the report to run without error?

    Code:
    UnitPriceEx: IIf(
    Nz([PODetail]![Descrip3],"")<>"",
    Format(
    Trim(
    Mid(
    Nz([PODetail]![Descrip3],""),InStr(Nz([PODetail]![Descrip3],""),"[unit_price: ")+13,InStr(InStr(Nz([PODetail]![Descrip3],""),"[unit_price: ")+13,Nz([PODetail]![Descrip3],""),"]")-(InStr(Nz([PODetail]![Descrip3],""),"[unit_price: ")+13)
    )
    )
    )
    ,"Standard")
    Thanks!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    46,888
    Advise not to format in query. Use textbox Format property.

    Won't always be sample string? Won't always be unit_price element?

    Let x represent your field. Consider:

    Val(Mid(Nz(x,""), InStr(Nz(x,""), "[unit_price: ") + 13))
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    shank is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    68
    This works great and sure cuts down my bloated formula. However, I have one more challenge. Researching the Val Function I found it returns a zero if no numeric value is found. I need a blank input so nothing shows on the report.

    Val(Mid(Nz(x,""), InStr(Nz(x,""), "[unit_price: ") + 13))

    I tried the below formula but I'm back to data mismatch error.

    IIF(Val(Mid(Nz(x,""), InStr(Nz(x,""), "[unit_price: ") + 13))=0,"",Val(Mid(Nz(x,""), InStr(Nz(x,""), "[unit_price: ") + 13)))

    So, if I cannot use "" for a currency value, I guess I need to research a function that will hide the field if it equals 0.

    Thanks!

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,202
    Something to think about:
    I just looked up "Format Property - Number and Currency Data Types" (in help) and set the format for a control in a report to
    Code:
    0;(0);"";"Null"
    The zero in the report was not displayed.

    Look at the "Custom Formats" section........
    Would that work for you?
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    5,513
    Or instead of that expression tome, consider a function called by your report query:
    Code:
    Function extractionTest(strIn As String, fldName As String) As Variant
    Dim n As Integer
    
    If fldName = "unit_price:" Then 
      n = 12
    Else
       n = 11
    End if
    extractionTest = Val(Mid(strIn, InStr(strIn, fldName) + n))
    If extractionTest = 0 Then extractionTest = ""
    'debug.Print extractionTest
    
    End Function
    Immediate window calls and results:
    ?extractiontest("[item: 2 Yr Zero Hassle Protection] [description: Protection Plan to be mailed/emailed within 10 day] [unit_price: 12.34] [ext_price: 12.34]", "unit_price")
    12.34

    ?extractiontest("[item: 2 Yr Zero Hassle Protection] [description: Protection Plan to be mailed/emailed within 10 day] [unit_price: ] [ext_price: 12.34]", "unit_price")
    ""[empty string - no, I don't actually see quotes]

    ?extractiontest("[item: 2 Yr Zero Hassle Protection] [description: Protection Plan to be mailed/emailed within 10 day] [unit_price: 12.34] [ext_price: 22.34]", "ext_price")
    22.34

    I'm guessing that in your query unit price calculated field you'd put UnitPrice: extractionTest([Descrip3],"unit_price")
    Last edited by Micron; 05-13-2019 at 07:22 PM. Reason: code corrections
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    Sometimes I talk to myself - like when I need expert advice.

  6. #6
    shank is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    68
    This was a handy tip: 0;(0);"";"Null"
    And this as well: Val(Mid(Nz(x,""), InStr(Nz(x,""), "[unit_price: ") + 13))

    I have to do more scenario testing, but it appears to resolve my issue.

    Much appreciated!

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

Similar Threads

  1. keep getting data type mismatch
    By tagteam in forum Access
    Replies: 3
    Last Post: 04-05-2018, 01:26 PM
  2. Replies: 2
    Last Post: 04-17-2017, 12:40 PM
  3. data type mismatch
    By ottoc in forum Queries
    Replies: 1
    Last Post: 12-02-2014, 08:42 AM
  4. Data Type Mismatch
    By timmy in forum Programming
    Replies: 9
    Last Post: 04-12-2011, 02:48 AM
  5. data type mismatch
    By jgelpi16 in forum Programming
    Replies: 5
    Last Post: 08-02-2010, 03:15 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
  •  
Tech Forums: Microsoft Office Forums