Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2013
    Posts
    3

    Unanswered: Query Not Returning Expected Results

    Hello!

    I have a little problem. I am in my Access database trying to run a query on a table of customers. I am trying to select all customers from the list who became customers in 2012.

    I have a field named DateEntered to hold the date I entered them into the database. The data type is set to short text.

    The format varies between customers as far as if there are slashes, dashes, or just numbers. It can either be MMDDYYYY or MM/DD/YYYY or MM-DD-YYYY

    I tried:
    Code:
    SELECT *
    FROM customers
    WHERE customers.DateEntered < '*2013'
    I also tried:
    Code:
    SELECT *
    FROM customers
    WHERE customers.DateEntered < '01012013'
    and also tried:
    Code:
    SELECT *
    FROM customers
    WHERE customers.DateEntered < '01/01/2013'
    Would anyone be as kind to help a fellow out and point out what I'm doing wrong.

    ALL of these queries led to empty results. Nothing shows up at all. I'm wondering if it is not the correct syntax for Access.

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by danjamins View Post

    ...The data type is set to short text...
    First off, we need to know what you were trying to say here!

    Datatypes are Date/Time, Text, Number, etc. There is no Datatype named 'short text.' There is a Format named Short Date, but none named 'short text.'

    So, is this Field defined as a Date/Time Field with a Format of Short Date, or something else?

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Also, the date delimiting character in Access is "#". Access' CDate format is quite good at recognising strings in various formats as dates, so try
    Code:
     SELECT *
    FROM customers
    WHERE Year(CDate(DateEntered)) = 2013;
    For data cleansing, which it sounds that you need LIKE WHOA, you can probably find the really screwy values with
    Code:
    SELECT *
    FROM customers
    WHERE Not IsDate(DateEntered);
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  4. #4
    Join Date
    Feb 2013
    Posts
    3
    Quote Originally Posted by Missinglinq View Post
    First off, we need to know what you were trying to say here!

    Datatypes are Date/Time, Text, Number, etc. There is no Datatype named 'short text.' There is a Format named Short Date, but none named 'short text.'

    So, is this Field defined as a Date/Time Field with a Format of Short Date, or something else?

    Linq ;0)>
    There is short text. It's in Access 2013, it's new. Sorry, should have stated what version I am using.

    The data has to be in this format for an application I load it into. It doesn't accept anything but text data types, it's really annoying but it is a great application once you get used to it. Just causes problems in the Access databases.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    not you can always format text on the way out of the system
    if you are capturing these dates then store them as dates.
    OR store thenm as text but in ISO date format YYYYMMDD
    but to be honest until you store your dates as dates you will be pissing into the wind if you think you can do searches or limit rows returned if you store as text.

    you may be able to get round your current nightmare be coercing the text value into a date (using the cdate function) and then using the relevant date time functions but it will be a kludge, will not be very responsive and will be a perfomance bottleneck. you wont be able to use indexes

    so fix the storage issue, change the datatype from text to datetime
    fix your data
    use datetime functions to extract what you want
    change your export procedure.
    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
  •