I am writing information to a database with dates on and from what i can see they are going into the database fine.
The problem i seem to be having is running queries against this date, when i run the query for some reason it puts the format as mm/dd instead of dd/mm, i am in the UK.
So i have writing a parameter query and for some reason am having to switch the month and the day around to make it run properly, which is no good to me as you will see why in my following code.
Private Sub Form_Load()
Dim SQL As String
Dim Rcs As DAO.Recordset
Dim Frm As String
Dim DateEndNew As Date
Frm = "Switchboard"
DateEndNew = (DateAdd("m", 1, Date)) 'Adding one month on does exactly that, it add 1 calendar month on, not day?
SQL = "SELECT [Quote Header].QRep, [Quote Header].QDateEnd, * " & _
"From [Quote Header] INNER JOIN [Customer] ON [Quote Header].QCus " & _
" = [Customer].CusID " & _
"Where [Quote Header].QRep = " & Rep & _
" And [Quote Header].[QDateEnd]< #" & DateEndNew & "#;"
Set Rcs = CurrentDb.OpenRecordset(SQ
L, dbOpenDynaset)
t = SQL 'Outputting to a text box to get the exact SQL query and it parameters to put into a query builder to test it
If Not Rcs.EOF Then
RecordSource = SQL
Else
DoCmd.Close
DoCmd.OpenForm Frm
End If
End Sub
Result from the SQL query -
SELECT [Quote Header].QRep, [Quote Header].QDateEnd, * From [Quote Header] INNER JOIN [Customer] ON [Quote Header].QCus = [Customer].CusID Where [Quote Header].QRep = 2 And [Quote Header].[QDateEnd]< #04/09/2005#;
As you can see i add a month on fine to the new date but it isnt querying against the new date at all, it interprets the new date as mm/dd/yyyy, i have checked all my regional settings and they are fine? I had this problem the other day when writing a query in VB.Net, when i queried the database with a date it didnt return anything for the exact same reason, this computer is brand new and have never done a date query on it until the other day, what is wrong with the settings?
