none
CInt and Val Functions RRS feed

  • Question

  • Hello... I am new to developing and coding. I have to join two tables... Table A has column System_No (ie 1), and in Table B I am trying to get the System_No from a field that holds the data in a text field (ie 1-CAN).  I want in the MS Access 2010 I used Parse to get the System_no from the text field and created the join no problem.  However in 2016, the Parse function does not seem to work.  

    I have tried VAL() which seems to give me the correct info and I have tried CInt(Instr(Table B.Text),1) and the data looks good in the first query, however when I try to Join the query with TABLE A, I get an Data Mismatch error.

    Does anyone know a replacement for the PARSE function that works with 2016??

    Thanks,

    Nicole

    Tuesday, August 8, 2017 10:27 PM

All replies

  • Hi Nicole, I could be wrong but I am not aware of a PARSE function in Access. Are you sure it's not a custom function you need to import from your old database? Just my 2 cents...
    Tuesday, August 8, 2017 10:51 PM
  • Hello,

    I think there is no Parse function as built-in funtion in Access.

    VAL function could get correct result. You could use

    SELECT * from TableB left join TableA on Val(TableB.Text) =TableA.System_No

    CInt(Instr(Table B.Text),1) would return wrong number of arguments. But if you are using CInt(Instr(TableB.Text,1)) , it would get the records which TableB.Text=1.  InStr function could not get the number in a string. Please visit InStr Function 

    So i suggest you use Val function.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, August 9, 2017 6:55 AM
    Moderator
  • What does the text string look like?  Post some example data (does not need to be real) and say if the position of the numeral data the same every time in the string?

    Build a little, test a little

    Wednesday, August 9, 2017 11:00 PM