Access SQL Date Format

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



Leave a reply



Submit