Results 1 to 11 of 11
  1. #1
    accessmatt is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    188

    data type mismatch error

    Hello,



    I have a 2 fields/2 tables/2 data types. Using FormatNumber(FIELD) I've converted the fields to a number type in my query however I'm getting a type mismatch error. Any ideas?

    Thanks,

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,205
    Format() and FormatNumber() actually result in a string value, not a true number. Why do you need to format in query? Apply formatting with property in textbox on form or report.
    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
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,727
    If you need to do math using a field, you could/should change the field type to a Number (Integer, Long Integer, Single,...)

    An alternative is to use one of the type conversion functions: CInt(Field), CLng(field), etc. See Access Help.
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  4. #4
    accessmatt is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    188
    June7, I need to convert both to number in order to do math against the two fields and I don't want to make a change to the table because I'm using the saved import feature which will break my changes everytime I refresh.

  5. #5
    accessmatt is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    188
    Thank you Steve. I will try using those conversion functions.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,205
    Then use conversion functions as shown by ssanfu. However, be aware that number conversion functions will error on Null, alpha or empty string. The null can be dealt with:

    CLng(Nz([field],0))

    Of course, if you don't want the records with null to be aggregated (average, count), will defeat that because Nz() in this case will return 0.
    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.

  7. #7
    accessmatt is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    188
    June7, thanks for that. I'm just curious what to do in the case where there are actually alpha values mixed into the column. Is there a way to ignore them when I convert everything else to a number?

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,205
    The pitfalls of bad data.

    Would the alphas be part of the value? Val() function will pull numbers from the value until it hits a non-number character except period.

    Val(Nz([field],0))
    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.

  9. #9
    accessmatt is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    188
    June7, I empathize with your emoticon. Just my luck to have this type of data! No the alphas are mostly "N/A" I think. Any ideas?

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,205
    The Val() function would return a 0 for "N/A".

    If those are the ONLY alphas, could run an UPDATE sql action to remove the "N/A" values and replace with Null or 0.
    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.

  11. #11
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,727
    A possible alternative would be to write an UDF to return the numerical part of the field.....
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

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

Similar Threads

  1. Data type mismatch error
    By Tomfernandez1 in forum Programming
    Replies: 5
    Last Post: 10-05-2012, 07:27 AM
  2. Data type mismatch error
    By nigelbloomy in forum Programming
    Replies: 1
    Last Post: 08-01-2012, 09:19 AM
  3. Replies: 1
    Last Post: 05-11-2012, 10:59 AM
  4. Data type mismatch error
    By AccessUser123 in forum Queries
    Replies: 1
    Last Post: 05-22-2011, 07:48 PM
  5. Data type mismatch error on all of my queries!
    By MarkGLyons in forum Queries
    Replies: 3
    Last Post: 12-27-2010, 01:27 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 - Senior Forums