Results 1 to 3 of 3

SQL - What is it telling me?

  1. #1
    AJ_25 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Apr 2018
    Posts
    7

    SQL - What is it telling me?


    Hi,

    I am pretty new to access and have come across this query of which I need to explain in plain English what it is it's doing, can anyone help please?


    DateValue("01/" & Mid([Period],6,2) & "/" & Mid([Period],2,4)) AS [Date], [Usage_04].Period




  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    6,557
    does that even work?
    its chopping up the PERIOD field to make: 01/mm/yyyy

    this works too:
    cvDate("01/" & Mid([Period],6,2) & "/" & Mid([Period],2,4)) AS Date

  3. #3
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    1,215
    That sql phrase is taken out of context, so hard to see how it fits within the entire query.
    However, it is trying to extract and construct a valid date value out of Period and call it [Date].

    Period is probably a string representing a date in a nonstandard format.
    Mid is a function that extracts short strings from within longer strings. (google MID function)

    So [Date] is being calculated as a concatenation of "01/"
    plus the sixth and 7th characters of Period, and a "/"
    plus the 2nd,3rd,4th and 5 characters of period.

    DateValue is a function that tries to make a valid date of the above extracted pieces. (google datevalue function)

    Tip: the output of the calculation is being named Date. Date is a reserved word in Access, meaning Today's Date.
    Your code should not contain Date as a USER data name. Something better would be FirstDate, LastDate, InspectionDate or somesuch.

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

Similar Threads

  1. Replies: 2
    Last Post: 05-08-2013, 02:37 AM
  2. Telling forms where to go
    By kfinpgh in forum Forms
    Replies: 0
    Last Post: 10-10-2006, 11:51 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
  •  
Tech Forums: Microsoft Office Forums