Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Posts
    67

    Unanswered: Comparing Numbers to Text Characters

    I have the below statement in a SQL statement:

    "WHERE Left([Field1],1)= " & i & ";"

    This gives me an error because the first character of "Field1" can be either a number or a letter. The "i" that "Left([Field1],1)" is being tested against is an integer.

    This gives me a "data mismatch" error for obvious reasons. What is not so obvious to me is how I can get around this data mismatch.

    Any suggestions for comparing numbers to text characters would be greatly appreciated.

    Jim

  2. #2
    Join Date
    Jan 2004
    Location
    Kennesaw, Ga
    Posts
    20
    I know this sounds too simple but how about the Cstr() function?

  3. #3
    Join Date
    Feb 2004
    Location
    AZ
    Posts
    12
    Try one of these:

    "WHERE Left([Field1],1)= '" & i & "';"

    or

    "WHERE Left([Field1],1)= " & chr(34) & i & chr(34) & ";"

  4. #4
    Join Date
    Feb 2004
    Posts
    67
    Originally posted by agduga
    I know this sounds too simple but how about the Cstr() function?
    agduga,

    Simple solutions are always the best. Parden my ignorance but yes it looks like Cstr() will work fine.

    Thanks very much.

    Jim

  5. #5
    Join Date
    Feb 2004
    Posts
    67
    Originally posted by jthammond
    Try one of these:

    "WHERE Left([Field1],1)= '" & i & "';"

    or

    "WHERE Left([Field1],1)= " & chr(34) & i & chr(34) & ";"
    jthammond,

    I may be missing something (probably am), but these would seem to work only if "i" were a constant. Unfortunately, i is a variable.

    agduga's suggestion of using the Cstr() works fine.

    Thank you for your suggestion.

    Jim

  6. #6
    Join Date
    Feb 2004
    Location
    AZ
    Posts
    12
    not to beat a dead horse..but:

    The code should work with i being a variable.

    If i was = 5 and Field1 was equal to "HELLO" your where clause would have looked like:

    WHERE Left([Field1],1)= " & chr(34) & i & chr(34) & ";"

    WHERE H="5";

    Or something like that.

  7. #7
    Join Date
    Feb 2004
    Posts
    67
    Originally posted by jthammond
    not to beat a dead horse..but:

    The code should work with i being a variable.

    If i was = 5 and Field1 was equal to "HELLO" your where clause would have looked like:

    WHERE Left([Field1],1)= " & chr(34) & i & chr(34) & ";"

    WHERE H="5";

    Or something like that.

    Oh, I see. chr(34) is an apostrophe (").

    Very creative. Thank you. I am sure that I can use this. In fact, Access seems to like to convert string characters back to numbers when creating a Query with VBA. This should help keep Access honest.

    Jim

  8. #8
    Join Date
    Feb 2004
    Location
    AZ
    Posts
    12
    A double-quote actually :-) My first line had a single quote (apostrophe) in there and the second line had the double-quote. Sorry for confusion. Some people like to use the chr(34) in the where clauses in case fields have single or double quotes in them for their values. Glad I could help to some extent.

    Have fun,

    Jeff

Posting Permissions

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