Hi. I am having some trouble with formatting dates in my database. Ideally, I want every date to be in the format of dd/mm/yyyy but there is one point in my database that it keeps flipping to mm/dd/yyyy and I can't figure out why. I've tried many variations of Format(date,"dd/mm/yyy") etc. so I have provided a bit more background about where the date variable comes from and how it is used elsewhere within the database, in case it is something more fundamental further back in the chain.
Initially, I have a table called OnSite which contains a RefDate field formatted as Date/Time. This is populated using an "Add" form, which is a bunch of textboxes with the contents saved via SQL in VBA (to allow error checking etc.). The relevant textbox (refDate) has Short Date as the format and an input mask of 00/00/0000;0;_ although I'm not sure if this is important. This works well, and the simplified (i.e. relevant) code is below
addSQL = "INSERT INTO OnSite (SerialNo, RefDate) VALUES (TxtSerial, TxtRefDate)"
DoCmd.RunSQL (addSQL)
Information from this table is dumped into a listbox on an "Inventory" form for the user to view using an SQL query set up in VBA to run on loading the form. This bit also works well. Simplified to just the relevant bits, this code is
'set sql statement
loadQuery = "SELECT OnSite.ID as [ID], OnSite.RefDate as [Reference Date] FROM OnSite;"
The user can select an entry within the listbox, then click a button to edit the details (without having to have access to the raw tables underneath). This involves showing a new form (Edit) which is prepopulated with the information in the OnSite table for the relevant entry. This displays the date from OnSite in the textbox in the right format (i.e. dd/mm/yyyy) and updates the table OnSite on completion using the following code. This is where things start to get complicated, as the date is saved in OnSite looking like "mm/dd/yyyy" instead of "dd/mm/yyyy" despite using the Format option.
'run sql
addSQL = "UPDATE OnSite SET SerialNo = '" & editserial & "', RefDate = " & IIf(Me!editRefDate & vbNullString = "", "Null", "Format(#" & Me!editRefDate & "#,'dd/mm/yyyy')") WHERE [ID] = " & ID & ";"
DoCmd.RunSQL (addSQL)
Weirdly, refreshing the listbox looks like it displays the date in the order of "mm/dd/yyyy" but treats it as "dd/mm/yyyy" in the calculated field.
To give an example, say my RefDate is 04/02/2016 (i.e. 4th February 2016). This is displayed as 04/02/2016 throughout (and works well with the calculated field) until I select that entry within the listbox and click my Edit button. This transfers 04/02/2016 to the relevant textbox on my Edit Form (with Short Date as the format) but when saved, it saves as 02/04/2016 back into OnSite (and subsequently displayed as 02/04/2016 within the lisbox). This means that the calculated field is working on the date 2nd April 2016 instead of 04th February 2016.
Sorry for the long post, but I wanted to provide as much information as possible, as I thought using Format etc. would solve this, and it evidently hasn't.
Cheers
Mark
Initially, I have a table called OnSite which contains a RefDate field formatted as Date/Time. This is populated using an "Add" form, which is a bunch of textboxes with the contents saved via SQL in VBA (to allow error checking etc.). The relevant textbox (refDate) has Short Date as the format and an input mask of 00/00/0000;0;_ although I'm not sure if this is important. This works well, and the simplified (i.e. relevant) code is below
addSQL = "INSERT INTO OnSite (SerialNo, RefDate) VALUES (TxtSerial, TxtRefDate)"
DoCmd.RunSQL (addSQL)
Information from this table is dumped into a listbox on an "Inventory" form for the user to view using an SQL query set up in VBA to run on loading the form. This bit also works well. Simplified to just the relevant bits, this code is
'set sql statement
loadQuery = "SELECT OnSite.ID as [ID], OnSite.RefDate as [Reference Date] FROM OnSite;"
The user can select an entry within the listbox, then click a button to edit the details (without having to have access to the raw tables underneath). This involves showing a new form (Edit) which is prepopulated with the information in the OnSite table for the relevant entry. This displays the date from OnSite in the textbox in the right format (i.e. dd/mm/yyyy) and updates the table OnSite on completion using the following code. This is where things start to get complicated, as the date is saved in OnSite looking like "mm/dd/yyyy" instead of "dd/mm/yyyy" despite using the Format option.
'run sql
addSQL = "UPDATE OnSite SET SerialNo = '" & editserial & "', RefDate = " & IIf(Me!editRefDate & vbNullString = "", "Null", "Format(#" & Me!editRefDate & "#,'dd/mm/yyyy')") WHERE [ID] = " & ID & ";"
DoCmd.RunSQL (addSQL)
Weirdly, refreshing the listbox looks like it displays the date in the order of "mm/dd/yyyy" but treats it as "dd/mm/yyyy" in the calculated field.
To give an example, say my RefDate is 04/02/2016 (i.e. 4th February 2016). This is displayed as 04/02/2016 throughout (and works well with the calculated field) until I select that entry within the listbox and click my Edit button. This transfers 04/02/2016 to the relevant textbox on my Edit Form (with Short Date as the format) but when saved, it saves as 02/04/2016 back into OnSite (and subsequently displayed as 02/04/2016 within the lisbox). This means that the calculated field is working on the date 2nd April 2016 instead of 04th February 2016.
Sorry for the long post, but I wanted to provide as much information as possible, as I thought using Format etc. would solve this, and it evidently hasn't.
Cheers
Mark
