Results 1 to 13 of 13
  1. #1
    Join Date
    Jul 2007
    Posts
    16

    Unanswered: Data type mismatch in expression

    Hi
    I have 2 fields one of the fields contains currency data and in another data is stored as memo. From the memo field I have used the Left InStr function to remove a number from the left hand side of the column. Then I tried to multiply the 2 fields together an error message appeared which said “Data type mismatch in expression”. Can some one please tell me what I do to resolve it the problem??

    Any help is much appreciated.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    To resolve the problem you store numbers as numbers!
    You're asking your PC to multiply currency by text - what did you expect the outcome to be.

    Memo fields are for notes, and notes only. Any "useful" information should be stored in the appropriate manner.

    To other posters: please do not suggest a sticking plaster workaround!
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    expressly cast the text values into numbers using the conversion fucntions such as csng, cint, cdbl etc.

    eg
    <myresult> = csng(left$(<myMemoColumn>,blah) * <mynumericcolumn>
    or
    select csng(left$(<myMemoColumn>,blah) * <mynumericcolumn> as <myresult>

    doing it that way round should also get round the problem of having a non numeric or null in the memo column, as you can't divide by zero in most computer languges

    there shouldn't be a problem doing mathmatical operations on the currency datatype.

    however the very fact that you are having to do dumb things with a memo column indicates to me that you have a a serious design problem, as georgev says store numbers in number columns (integer, single, double or whatever), dates in datetime columns, and after that who cares......
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I thought I said no sticking plasters?
    George
    Home | Blog

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by georgev
    I thought I said no sticking plasters?
    since when did you join the thought police youngster?
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    George,
    Have you thought that maybe Weebot is trying to get the number out of the Memo field so as to put it into it's own numerical field? Sometimes we need the sticking plasters to "fix" what we inherited from someone else, or because we are learning on the job.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Perhaps, but such advice should not be given out until justification has been made If Weebot came back with a valid reason for this, then I would happily provide a solution.

    Apologies - I've had a lovely 3 hours headache and a long tedious day.
    Bleh
    George
    Home | Blog

  8. #8
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    I've never had a "lovely" headache. But I do know what you mean. Hope you feel much better tomorrow.

  9. #9
    Join Date
    Jul 2007
    Posts
    16
    From the field which I am separating data is a memo field as is received in that format

    e.g. 36 Month contract, BMW 330ci

    What is the best way to store this type of data so i can extract parts and use them in calculations

    Thanks for your help everyone
    Last edited by Weebot; 09-25-07 at 18:41.

  10. #10
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    I would assume you have a standard way of looking at the length of contracts. Is it months, years, weeks, etc.? I would assume months, so you can extract the number in front of the word "Months" and store that in a field named ContractLength with the stated policy that all contract lengths are stated in months. Then do you want the vechicle Make and Model in separate fields or one field? Generally best to put each into it's own field. So you get the BMW and put that into the Make field, and then retrieve the 330ci and put that into the Model field. As you put the 36 into the ContractLength field, best to use the convert function for the data type you have used to define the ContractLength field. Here let's assume it is Integer as I would not think you will have any contracts longer than 32,000 months. Therefore, I would suggest using the CInt() function to convert the 36 that will be a string data type into an Integer as you store it into the interger field in your database.

    Another thing that you will have to look at is just how to retrieve this data from the memo field. Is the data ALWAYS in the same format? "XX Month contract, MAKE MODEL" Are there any MAKEs that have two words? If so you can't just assume the first blank after start of the MAKe after the "Month contract" constant is where the MODEL will be starting.

  11. #11
    Join Date
    Jul 2007
    Posts
    16
    Thanks for your help everyone
    Now I just need to know how I can put the following together;

    CInt()

    Left ([Promo],InStr([Promo]," Month contract")) AS Monthct

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Come on Weebot, you can do that! Remeber that the functions work from "inside out" (the function wrapped on the outside will execute last).

    So after you've exracted your number you want to convert it to an integer. Any guesses?
    George
    Home | Blog

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by georgev
    Come on Weebot, you can do that! Remeber that the functions work from "inside out" (the function wrapped on the outside will execute last).

    So after you've exracted your number you want to convert it to an integer. Any guesses?
    ..anyone would have thought Weebot hasn't been reading the posts
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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