Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2013
    Posts
    81

    Question Unanswered: MS Access 2003 - Date Function in SQL Code

    Hi All

    I've been working on a piece of SQL coding (in the middle of a fair amount of VBA coding - linked to the on click event) to INSERT data and then UPDATE existing records. The UPDATE code works beautifully but I'm having some issues with the INSERT. The INSERT query is designed to create a record of the alteration you have just made to the open record. I have asked Access to record the date (ADate) you say you carried out the action (to the physical equipment) as well as the date (LDate) you actually record the alteration in the database.

    For some mad reason the date the user enters I've Dim'ed as a string (not entirely sure why, only just realised I had done) but the date you enter the record I've Dim'ed as Date. Now I've managed to get my syntax right (with the #) the query works, but I end up with the ADate being shown in the table in the UK format (dd/mm/yy) but the LDate being shown in the table in the US format (mm/dd/yy), which is quite confusing to read.

    From reading around it would seem that access only really copes with dates being in the US format. So my question is will converting the date to UK (as I believe you can with a function) confuse access. Alternatively will treating the dates as strings cause issues. As things currently stand these dates are for records only, the date itself wont be linked to anything else. However, I will have a date (from elsewhere) which I intend to use in a calculation to calculate a due date.

    I would really appreciate any light you can shed on this issue for me.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Store dates as Date/Time data type. You cannot perform any kind of sorting, or easily use Date/Time functions (DateAdd, DateDiff, etc.) otherwise.
    2. Access can only deal with US (mm/dd/yyyy) and ISO (yyyy-mm-dd) date formats. This does not change with the international settings in the control pannel of Windows.
    3. Internally, Access stores a date (and time) as a floating point numeric value where the integer part represents the number of days from a fixed starting date and the decimal part represents the number of seconds from midnight. Nothing prevents you from applying a Format property, either in the table definition or in the properties of a control. This will not alter the way the date is stored internally.
    Have a nice day!

  3. #3
    Join Date
    Jun 2013
    Posts
    81
    Many thanks for the quick response, I really appreciate it, it really helps me to understand whats going on. However, I've realised through playing around with the formatting in the table that something a tad strange is going on. When I switch the format to dd/mm/yyyy it still shows 12/03/2013 (I'm fairly sure it should be 03/12/2013). So I tried switching to dd/mmmm/yyyy (or long date) and it shows 12th march 2013. I've also tried mmmm/dd/yyyy and it shows March 12 2013.

    I can't understand why LDate provides the correct date in UK format when it is used as part of strInputReturn (see code below) but when I use it as part of the query to enter todays date into the HistoryDateRecord field, it switches it to show March.

    The VBA code in question (on the section relating to LDate - which doesn't seem to be working properly) is:

    Dim LDate As Date
    Dim IResponse As Integer
    Dim strInputReturn As String
    Dim strSetID As String
    Dim SQLResponseC As Integer
    Dim strActionReturn As String

    'confirm the set is ready to be returned to service

    If Me.SetOutOfService = False Then

    IResponse = MsgBox("Please confirm set " & Me.setID & " has been re-calibrated before being returned to " & _
    "service", vbYesNo)

    'if yes uncheck the out of service box

    If IResponse = vbYes Then
    Me.SetOutOfService = False
    LDate = Date

    'prompt the user to enter information

    strInputReturn = InputBox("Enter the date set " & Me.setID & " was returned to service", "Set " & _
    "Returned to Service", LDate)

    'bit in here which cancels the application if no details entered for strInputReturn

    'warn users that this action will update records which cannot be reversed with the undo command and give option to cancel

    SQLResponseC = MsgBox("This action will update several records. Once you click yes you cannot " & _
    "use the undo command to reverse the changes. Are you sure you want to update these records?", _
    vbYesNo)

    'if the user is happy to update the records

    If SQLResponseC = vbYes Then
    'enter details into history table
    'first set strSetID, strActionReturn,
    strSetID = Me.setID
    strActionReturn = "Return to Service (Set Out of Service)"

    'run INSERT query to enter details in history table

    CurrentDb.Execute _
    "INSERT INTO tblHistory (SetID, HistoryAction, HistoryDate, " & _
    "HistoryDateRecord) " & _
    "VALUES (' " & strSetID & " ', ' " & strActionReturn & " ', " & _
    "' " & strInputReturn & " ', # " & LDate & " #)"

    'UPDATE query goes here

    Exit Sub


    Any assisstance would be greatly appreciated.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    In the SQL query, try using:
    Code:
        "VALUES ('" & strSetID & "', '" & strActionReturn & "', " & _
        "'" & strInputReturn & "', '" & Format(LDate, "yyyy-mm-dd") & "');"
    Have a nice day!

  5. #5
    Join Date
    Jun 2013
    Posts
    81
    Fantastic, that seems to have fixed it. Thank you so much for your help, I really appreciate it.

  6. #6
    Join Date
    Dec 2013
    Posts
    16

    Cool Sql

    I don't really understand your problem but to search for the date you can try code as such;

    WHERE (datefield) = 2012

    Thanks

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •