Results 1 to 4 of 4
  1. #1
    adnancanada is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    121

    Question If date field in text format

    I have a huge table where POdate in text format (20160802). What is best to run the query. Should I use dates in quotes or without quotes? another question is that If it is text field then how come is sorting date according to query ?



    let see.

    Select PO, Podate, region, shiplocation from tblPO
    where POdate between 20150802 and 20160802

    or

    Select PO, Podate, region, shiplocation from tblPO
    where POdate between '20150802' and '20160802'

  2. #2
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Should I use dates in quotes or without quotes?
    Using text date type, the only comparison you can make in a query is =. To work with a range (>, < or between dates) your queries are going to need calculated fields to convert the text to a date value (in the query only, not converting the actual table data). You might want to consider having a new table field on which you run an update query to convert the text values to dates. I don't like the idea a whole lot, preferring to have the original data as dates, but I suspect you'd say that's not possible because of where it comes from.[/quote]

    If it is text field then how come is sorting date according to query ?
    This I don't understand.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    adnancanada is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    121
    Quote Originally Posted by Micron View Post
    Using text date type, the only comparison you can make in a query is =. To work with a range (>, < or between dates) your queries are going to need calculated fields to convert the text to a date value (in the query only, not converting the actual table data). You might want to consider having a new table field on which you run an update query to convert the text values to dates. I don't like the idea a whole lot, preferring to have the original data as dates, but I suspect you'd say that's not possible because of where it comes from.
    This I don't understand.[/QUOTE]

    Actually it is not table I am runnig from views. Date field is text.

  4. #4
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Actually it is not table I am runnig from views.
    Not sure if it matters - have never used views. It's my understanding that it's basically a form of a table that you can query against, so I suspect what I said still applies but I can't confirm. That would mean you'll need calculated fields in your query or on your form.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 06-26-2015, 08:55 PM
  2. Replies: 2
    Last Post: 07-09-2014, 06:41 PM
  3. Replies: 4
    Last Post: 03-12-2013, 06:49 PM
  4. format text string as date
    By rbrem in forum Access
    Replies: 2
    Last Post: 08-28-2012, 08:39 AM
  5. Replies: 1
    Last Post: 08-07-2011, 07:58 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums