Results 1 to 3 of 3
  1. #1
    LonghronJ is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    150

    Formatting Dates


    I'm having an issue with a join in my query where the join are between two date fields. One of the table's date is formatted as mm/dd/yyyy. The second one is something like mm/dd/yyyy hh:mm:ss. How do I convert the second one in a query so that it matches the format of the first one? I thought format([MyDate],"mm/dd/yyyy") would work, but when I make the join, I get the error message of "Type mismatch in expression".

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    are both date fields in DATE/TIME type?
    You may have to make 2 queries to convert the dates
    Q1=
    format(table1.[MyDate],"mm/dd/yyyy") as Date1

    Q2=
    format(table2.[MyDate],"mm/dd/yyyy") as Date2

    THEN in Q3 join Q1 to Q2 on these new date fields

  3. #3
    LonghronJ is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    150
    Quote Originally Posted by ranman256 View Post
    are both date fields in DATE/TIME type?
    You may have to make 2 queries to convert the dates
    Q1=
    format(table1.[MyDate],"mm/dd/yyyy") as Date1
    Q2= format(table2.[MyDate],"mm/dd/yyyy") as Date2

    THEN in Q3 join Q1 to Q2 on these new date fields

    Yes, they are both Date/Time data type. That's a brilliant idea about making another query and it works. I just thought perhaps there was a simple formatting I could have done with one of the queries so that it would match the other.

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

Similar Threads

  1. Formatting Dates in a Query
    By CP611 in forum Access
    Replies: 3
    Last Post: 11-27-2017, 11:23 AM
  2. Conditional Formatting with dates
    By drewdrew in forum Access
    Replies: 3
    Last Post: 01-06-2016, 04:48 AM
  3. Formatting dates problem
    By ruthib4 in forum Programming
    Replies: 16
    Last Post: 02-25-2012, 12:49 AM
  4. Using Conditional Formatting & Dates
    By djclntn in forum Forms
    Replies: 14
    Last Post: 04-12-2011, 07:04 PM
  5. Formatting dates
    By John Southern in forum Programming
    Replies: 2
    Last Post: 05-21-2010, 11:19 PM

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