Unanswered: MS Access 2003 - Date Function in SQL Code
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.
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.
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 " & _
'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?", _
'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