Results 1 to 8 of 8
  1. #1
    murleyj is offline Novice
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    16

    Table field displays date but is formatted as Short Text - Cannot use to define date criteria in que

    We use an ERP program that is built on Access database. The table that contains employee attendance has a date field that displays the attendance date as mm/dd/yy format but in the actual settings it is set to DATA TYPE short text. The program will not allow changing the DATA TYPE to anything else. And not sure I would want to because it might cause the program we use to not function correctly.

    I am trying build a query that I could run every Saturday to display the previous week employee attendance hours for payroll.



    However in my query I try criteria such as "between today() and today()-6" ...but it does nothing because what looks like a date is formatted as short text.

    Any ideas how I could make this work?

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Try using CDate() to coerce it to a date?

    However dates should be stored as dates. How you want them to look is just a format.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Today is an Excel function, no? You mean "BETWEEN Date() AND Date()" (or BETWEEN Date and Date)??
    Might work if you use

    BETWEEN Format(Date,"mm/dd/yy") AND Format(Date,"mm/dd/yy")-6
    If doing this in VBA I think you would require string delimiters as well.

    The Format function converts Dates to strings. The opposite is CDate, which formats strings to dates. Either requires that they look like dates to Access.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    murleyj is offline Novice
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    16
    Thank you......I don't know how to do that

  5. #5
    murleyj is offline Novice
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    16
    Thanks for the response....I don't know how to do that

  6. #6
    murleyj is offline Novice
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    16
    "Format" was the wrong choice of words....I meant Data Type....sorry

  7. #7
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Put your datefield into the cdate() function and use date as micron indicated and not today.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    @murleyj
    Post the line(s) of code/sql that is involved so readers can see your issue in context.

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

Similar Threads

  1. Replies: 4
    Last Post: 03-29-2020, 07:08 AM
  2. Changing short text field to date/time field
    By fishhead in forum Access
    Replies: 5
    Last Post: 03-25-2020, 01:17 AM
  3. Replies: 2
    Last Post: 10-30-2017, 02:33 PM
  4. Replies: 7
    Last Post: 04-07-2017, 02:08 AM
  5. Datepicker set to short date: displays time?
    By sprovoyeur in forum Forms
    Replies: 3
    Last Post: 07-19-2011, 06:24 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