Results 1 to 9 of 9
  1. #1
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Data Type Error in Query (2002)

    I created a query where in the field cell is the expression
    You know it's time to diet when you push away from the table and the table moves.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data Type Error in Query (2002)

    If you view the first query in datasheet view, are there any records that display #Error in the 'name' column?

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Data Type Error in Query (2002)

    Omega

    You have confused me. can you explain

    You state that you are sorting it by the first 14 characters of the last name but you search for Allen which is only 5 letters in length. May this error be created by the Left function.....maybe I am missing something?
    Jerry

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Data Type Error in Query (2002)

    You'll get an error if you try to use a string function like Left$ on a field that is null. Could that be part of your problem?
    Charlotte

  5. #5
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Data Type Error in Query (2002)

    When I run the first query without criteria I get good data. When I run the first query again with a name in the criteria cell I get one row with every field in it displaying #name?. The field in the table I am using the Left$ function on is defined as text.
    You know it's time to diet when you push away from the table and the table moves.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data Type Error in Query (2002)

    Ah, I should have seen that. Use Left instead of Left$. Left can handle null values, Left$ can't - it can only handle strings.

  7. #7
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Data Type Error in Query (2002)

    I didn't know Left could handle nulls. Great. I'll give it a try on Monday. Thanks for all your help. Have a great weekend.
    You know it's time to diet when you push away from the table and the table moves.

  8. #8
    3 Star Lounger Omega3's Avatar
    Join Date
    Jan 2004
    Location
    Los Angeles, California, USA
    Posts
    343
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Data Type Error in Query (2002)

    Yes that was it. It was the very last record that didn't have a last name in it. I fixed it and it worked.

    Now I am having trouble with the second query. I use Name and a criteria of not null and a second column with Name and I use the Left$ function and a criteria on the same row. I know I could have put the criteria in the first "name" column but I wanted to check for data type errors. I would have thought the first "name" column with criteria of not null would have taking care of the same problem I had in the first query. Is that correct? I wonder if Nz would help in this instance. I'll have to leave this for Monday.

    Thanks for the help on the first query. I got nulled again.
    You know it's time to diet when you push away from the table and the table moves.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Data Type Error in Query (2002)

    Functions such as Left, Mid, Right and Trim result in a Variant. The result can be a string or Null.
    Their counterparts Left$, Mid$, Right$ and Trim$ result in a String, hence they baulk at null values.
    If you are absolutely sure that you won't have null values, the latter versions are faster and more efficient, since they don't have the overhead of checking for null values. You won't notice the difference unless you're performing huge numbers of calculations, though.

Posting Permissions

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