Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2003
    Location
    Houston
    Posts
    4

    Unanswered: type mismatch in expression (rudimentary help needed)

    In MS Access 2000, I am trying to create either a relationship or a query join on a text and a number field (invoice number is a number and invoice id is text)

    select a.invoice_number, b.invoice_id
    from a inner join b
    on CStr(a.invoice_number) = b.invoice_id

    fails

    select a.invoice_number, b.invoice_id
    from a inner join b
    on a.invoice_number = CDbl(b.invoice_id)

    also fails


    Your help is greatly appreciated.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1

    Re: type mismatch in expression (rudimentary help needed)

    Originally posted by Scuba Barbie
    In MS Access 2000, I am trying to create either a relationship or a query join on a text and a number field (invoice number is a number and invoice id is text)

    select a.invoice_number, b.invoice_id
    from a inner join b
    on CStr(a.invoice_number) = b.invoice_id

    fails

    select a.invoice_number, b.invoice_id
    from a inner join b
    on a.invoice_number = CDbl(b.invoice_id)

    also fails


    Your help is greatly appreciated.
    uh.. why not convert the string to a number to begin with?

  3. #3
    Join Date
    Dec 2003
    Posts
    268
    YOu can only join same datatype in your relationships. Ideally you would change your datatypes in your tables. However, sometimes that is not always possible.

    You have said that invoice number is a number. Is that an integer or a double. If it is the former change the line of code where you join and convert invoice id to a double.

    on a.invoice_number = CInt(b.invoice_id)

    But before you put forth too much effort on changing the informaiton in the SQL String I would try and change the datatype in the Tables themselves.

  4. #4
    Join Date
    Dec 2003
    Location
    Houston
    Posts
    4

    Teddy

    Teddy - sorry, I am such a novice I don't understand your question.

    Oh would one go about converting to a string to begin with?

  5. #5
    Join Date
    Dec 2003
    Location
    Houston
    Posts
    4

    mj weyland

    Thanks for the suggestion. The CInt returns

    ODBC call failed ORA-01722

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1

    Re: mj weyland

    Originally posted by Scuba Barbie
    Thanks for the suggestion. The CInt returns

    ODBC call failed ORA-01722
    You are using more then just access then? Oracle perhaps? There is no possible way to generate that error in access alone.

    Also, what I meant by "converting it in the first place" was what mjweyland elaborated on. If they're both int compliant fields, why not make them ints at the table level to begin with?

  7. #7
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238

    Re: Teddy

    Originally posted by Scuba Barbie
    Teddy - sorry, I am such a novice I don't understand your question.

    Oh would one go about converting to a string to begin with?
    Barbie,

    You would use CStr() just like you did... however you would do this BEFORE creating the query to join the two recordsets...

    Without me getting into teaching you subqueries, I would suggest that you create a query in Design Mode and add table A... Select the * for all fields... Then make a calculated field... NewInvoiceNum: CStr(invoice_number) ... Save the query... say as qryA...

    Then create your join query using qryA and table B... Like this...

    select qryA.NewInvoiceNum, B.invoice_id
    from qryA inner join b
    on qryA.NewInvoiceNum = b.invoice_id

    HTH

  8. #8
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238

    Re: Teddy

    Originally posted by Scuba Barbie
    Teddy - sorry, I am such a novice I don't understand your question.

    Oh would one go about converting to a string to begin with?
    Oh... Just wanted to add that Teddy and Mjweyland are definitely right... You should try and ensure that keys that are going to be compared to one another are the same data types to begin with (in the tables)...

    BUT in my case I get data from numerous sources ... and it's not always possible to import the data as the types I'd like... so sometimes it's necessary to convert after the fact... After all, that's why we have those functions right? CStr, CDbl, CInt, CDat, etc...

    Have a great night!

  9. #9
    Join Date
    Dec 2003
    Location
    Houston
    Posts
    4

    Oracle

    Thanks all, yes I think the programmers should have coordinated and created the tables with the same data type.

    Unfortunately, I am stuck creating reports on this existing Oracle database.

    The query idea is one I have been playing with the last hour, but so far no luck.

    Nice to know I am shooting in the right direction.

    Thanks all.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •