Can anyone see whats wrong with my code please? (1 Viewer)

[shabbaranks]

Hi,

Trying to export values from an Access table no matter how I format the date it doesn't export properly (the export works but the date specified is ignored)

     strFile = "C:\" & Format(Date, "ddmmyyyy") & ".csv"
    strDateEntry1 = Format(InputBox("Please Enter From Date, Example: 10/09/2015", "Restricted Form"), "dd-mm-yyyy")
    strDateEntry2 = Format(InputBox("Please Enter To Date, Example: 10/09/2015", "Restricted Form"), "dd-mm-yyyy")
    
    If Len(Dir(strFile)) > 0 Then
        Kill strFile
    End If
        
    strSQL = "SELECT T_Table.ID, T_Table.Employee, T_Table.[Logon Account], T_Table.[User Department], T_Table.[Project Number], T_Table.Activity, Format((T_Table.[Task Date]), 'dd-mm-yyyy') as [Task Date], T_Table.Hours, T_Table.Detail, T_Table.[Time Of Submission] " & vbCrLf & _
    "FROM T_Table " & vbCrLf & _
    "WHERE (((T_Table.[Task Date])>= #" & strDateEntry1 & "# And (T_Table.[Task Date])<= #" & strDateEntry2 & "#))"
    Set qdf = CurrentDb.CreateQueryDef("TempQuery", strSQL)
    DoCmd.TransferText acExportDelim, , "TempQuery", strFile, True
    CurrentDb.QueryDefs.Delete "TempQuery"

Thanks as always

 

[arnelgp]

Dim d1 As Variant
Dim d2 As Variant

strDateEntry1 = InputBox("Please Enter From Date, Example: 10/09/2015 (mm/dd/yyyy)", "Restricted Form")
strDateEntry2 = InputBox("Please Enter To Date, Example: 10/09/2015 (mm/dd/yyyy)", "Restricted Form")

If Len(Dir(strFile)) > 0 Then
Kill strFile
End If

d1 = sbCheckValidDate(strDateEntry1)
d2 = sbCheckValidDate(strDateEntry2)

if not isnull(d1) and not isnull(d2) then
strSQL = "SELECT T_Table.ID, T_Table.Employee, T_Table.[Logon Account], T_Table.[User Department], T_Table.[Project Number], T_Table.Activity, Format((T_Table.[Task Date]), 'dd-mm-yyyy') as [Task Date], T_Table.Hours, T_Table.Detail, T_Table.[Time Of Submission] " & vbCrLf & _
"FROM T_Table " & vbCrLf & _
"WHERE (((T_Table.[Task Date])>= #" & Format(d1,"mm/dd/yyyy") & "# And (T_Table.[Task Date])<= #" & Format(d2,"mm/dd/yyyy") & "#))"
Set qdf = CurrentDb.CreateQueryDef("TempQuery", strSQL)
DoCmd.TransferText acExportDelim, , "TempQuery", strFile, True
CurrentDb.QueryDefs.Delete "TempQuery"
end if


Private Function sbCheckValidDate(s As String) As Variant

Dim intCount As Integer
Dim intStringLen As Integer
Dim intNumSlash As Integer
Dim arr() As String

intStringLen = Len(s)

For intCount = 1 To intStringLen
If Mid(s, intCount, 1) = "/" Then intNumSlash = intNumSlash + 1
Next

If intNumSlash = 2 Then

arr = Split(s, "/")

sbCheckValidDate = DateSerial(Val(arr(2)), Val(arr(0)), Val(arr(1)))
If Not IsDate(sbCheckValidDate) Then
sbCheckValidDate = Null
End If
Else
sbCheckValidDate = Null
End If
End Function

 

[shabbaranks]

Thanks, so based on your example you seem to have mm/dd/yyyy am I not able to get the user to input dd/mm/yyyy?

I understand that access see's the date as mm/dd/yyyy but I think this will just confuse people.

Thanks

 

[arnelgp]

ofcourse you can get user input dd/mm/yyyy, if you do please modify the function, if you will ever use it, to:


sbCheckValidDate = DateSerial(Val(arr(2)), Val(arr(1)), Val(arr(0)))

 

[shabbaranks]

I still cant get it to work, if I debug.print it the date ranges are correct but it seems to only filter on the second value not the first. So I get all results up to the entry put in the second control box.

 

[arnelgp]

ok, add another debug.print:

debug.print "day:" & day(d1)
debug.print "month:" & month(d1)
debug.print "year:" & year(d1)

debug.print "day:" & day(d2)
debug.print "month:" & month(d2)
debug.print "year:" & year(d2)

if you suspect that the month is interchanged with day, then your data entry is not correct or you have to adjust the program logic.