Access vba sql query with date in format dd/mm/yyyy
The bast way to deal with International Dates in Access is to use Allen browne SQLDate Function Allen browne site .
the function
Function SQLDate(varDate As Variant) As String
'Purpose: Return a delimited string in the date format used natively by JET SQL.
'Argument: A date/time value.
'Note: Returns just the date format if the argument has no time component,
' or a date/time format if it does.
'Author: Allen Browne. allen@allenbrowne.com, June 2006.
If IsDate(varDate) Then
If DateValue(varDate) = varDate Then
SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
Else
SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
End If
End If
End Function
How to format date in a SQL query?
Use Format function.
SQL = "SELECT COUNT(Date)" & vbcr & _
"FROM [Orders]" & vbcr & _
"WHERE [Orders].[Date] BETWEEN #Format(" & StartDate & "], 'MM/dd/yyyy')# AND #Format(" & EndDate & ",'MM/dd/yyyy')#"
MS Access SELECT query not taking date format
You need to enclose dates in #
signs in Access SQL.
This should work:
select *
from data
where start_date = #7/12/2014#
Access VBA & SQL date formats
Is there no way to compare a date in Access SQL that is independent of
format?
Consider a query with a parameter in its WHERE
clause, similar to this ...
WHERE [Appointment Slots].Time=[Enter Appointment Time]
You can also add a PARAMETERS
clause at the beginning of your SQL statement, but it's not absolutely required ...
PARAMETERS [Enter Appointment Time] DateTime;
So when the query expects a Date/Time parameter, you just give it a Date/Time value.
A parameter query avoids date format issues, and also avoids the need for #
date delimiters in the SQL statement.
MS Access SQL INSERT INTO statement produces date in wrong format despite correct regional setting
Ok, the way this works?
You don't have to care, know, or think about the users regional format settings.
So, if you drop some control on a form? Just make sure that control is set to a date type format. Your done.
BUT ONE big whopper:
IN ANY AND ALL cases, your string based date format MUST be in USA format. Or you can use ISO date format.
dim MyDate as Date
MyDate = me.InvoiceDate
So, now we have a internal format date variable. How to insert into a table?
dim strSQL as string
strSQL = "INSERT INTO tblInvoice (InvoiceNum, InvoiceDate, InvoiceAmount " & _
"VALUES (1234, " & quDate(MyDate) & ",50)"
So, you ALWAYS format the date value into USA format.
You can type that format command over and over, but that fast becomes tiring.
so, I use a little helper function:
Public Function quDate(dt As Date) As String
quDate = "#" & Format(dt, "mm\/dd\/yyyy") & "#"
End Function
Public Function quDateT(dt As Date) As String
' return formatted date with time
quDateT = "#" & Format(dt, "mm\/dd\/yyyy HH:NN:SS") & "#"
End Function
So, you don't have to care about the date and regional format, but for a in-line SQL insert command that you build in code? Yes, you MUST convert to USA format of mm/dd/yyyy.
So, you can display dates in any format. For forms, for reports - not a problem.
However, the ONLY exception here is your code that builds a insert statement. That date string format must be #mm/dd/yyyy#.
Or, ISO:
#yyyy-mm-dd#
So, either format is fine, but it is a hard and fast rule that you must conform to.
So, from a text box on a form, if not data bound, then you want to ensure that the text box is set as a date type text box (fomrat date).
then in code:
dim strSQL as string
strSQL = "INSERT INTO tblFun (BirthDate) " & _
"VALUES (#" & format(txtDate,"mm/dd/yyyy") & "#)"
currentdb.Execute strSQL
Or, if you have that helper function, then this:
strSQL = "INSERT INTO tblFun (BirthDate) " & _
"VALUES (" & qudate(txtDate) & ")"
Access SQL appears to be treating date as dd/mm/yyyy?
That is because you don't play by the rules. Always handle dates as date values, not strings, not numbers, no exceptions.
So, when you use Nz and even add 1, the union query cannot figure out the datatype, thus it falls back to return the result as text. Then DateFrom becomes Text while DateTo is Date which makes filtering a wild guess.
*Edit - I have also amended the first part of the union query to ensure the current record follows on from the most recent archived record)
Correct as this:
SELECT
tblUnion.[Employee Number],
tblUnion.[Dept],
tblUnion.DateFrom,
tblUnion.DateTo
FROM
(SELECT
ts.[Employee Number],
ts.[Dept],
DateAdd("d", 1, Nz(ta2.MaxDateTo, #01/02/16#)) AS DateFrom,
Date() AS DateTo
FROM
tblStaff ts
LEFT JOIN
(SELECT
[Employee Number],
max(DateTo) AS MaxDateTo
FROM
tblArchiveStaff
GROUP BY
[Employee Number]) ta2
ON ts.[Employee Number] = ta2.[Employee Number]
UNION ALL
SELECT
ta.[Employee Number],
ta.[Dept],
ta.DateFrom,
ta.DateTo
FROM
tblArchiveStaff ta) AS tblUnion
WHERE
#7/1/2017# Between [DateFrom] And [DateTo];
and you will get the desired output:
Employee Number Dept DateFrom DateTo
205147 IT 2017-01-07 2017-07-05
205442 Accounts 2016-01-03 2017-07-05
MS Access - Converting Date Format for Short Text
I solved with with VBA
Private Sub dateFormattingConvert()
Dim rs As DAO.Recordset
Dim oldDate As String
Dim newDate As String
Dim year As String
Dim month As String
Dim day As String
Dim fullString As String
Set rs = CurrentDb.OpenRecordset("Door Activity Log")
If Not (rs.EOF And rs.BOF) Then
rs.MoveFirst
Do Until rs.EOF = True
Debug.Print (rs![Door Activity Log Date])
oldDate = rs![Door Activity Log Date]
year = Right(oldDate, 4)
month = Left(oldDate, 2)
day = Mid(oldDate, 4, 2)
fullString = day & "/" & month & "/" & year
rs.Edit
rs![Door Activity Log Date] = fullString
rs.Update
rs.MoveNext
Loop
Else
MsgBox "No more records."
End If
MsgBox "Finished"
rs.Close
Set rs = Nothing
End Sub
Related Topics
Elasticsearch Map Two SQL Tables with a Foreign Key
Nhibernate Count Distinct (Based on Multiple Columns)
Add 2 Months to Current Timestamp
Need Oracle SQL to Split Up Date/Time Range by Day
Run Multiple Commands in SQLite Manager
SQL Find Sets with Common Members (Relational Division)
SQL Selecting Dates with Maximum Sale for Each Department
Conditional Order by Depending on Column Values
How to Make String Auto Increment
Exclude Rows Based on Other Rows (Sql)
Mysql: Union of a Left Join with a Right Join
SQL Server Concatenate Group By
Sql, on Delete Cascade and on Update Cascade
SQL Where in (...) Sort by Order of the List