Date comparing Problem

[James Bowyer]

I'm having a problem where two dates that (I think) should be able to be compared against each other (I'm trying to narrow down a set of results by a date to/from). However, it doesn't seem to work.

To add confusion, if I make the input text box an Unbound box, with a data input mask instead, it works.

Which is all very well, but the calendar that pops up when you click on a Date box is rather essential in this case, and is what is used in the input of the dates in the first place.

Any ideas what I'm doing wrong?

Dim rstoPeriodReport As DAO.Recordset
Dim rsfromKaizenDB As DAO.Recordset
Dim rsoriginators As DAO.Recordset
Dim rsReportID As DAO.Recordset
Dim todaysdate As Date
todaysdate = Date

Dim NumKASApproved As Integer
Dim NumKASRejected As Integer
Dim NumKASCompleted As Integer
Dim NumKASOverdue As Integer
Dim KASDepartment As String
Dim KASShift As String
Dim KASOriginatorName As String

Dim DateTo As Date
Dim DateFrom As Date

DateTo = Me.TboxDateTo 
DateFrom = Me.TboxDateFrom 


Dim db As Database
Set db = CurrentDb()

Set rstoPeriodReport = db.OpenRecordset("tblPeriodReportKASByDepartment")
Set rsReportID = db.OpenRecordset("tblReportsID")
rsReportID.AddNew
rsReportID("DateRequested").Value = todaysdate
rsReportID("User").Value = Me.TboxUsername
rsReportID("DateFrom").Value = Me.TboxDateFrom
rsReportID("DateTo").Value = Me.TboxDateTo
rsReportID.Update

Dim reportIDmax As Integer
reportIDmax = DMax("[ID]", "TblReportsID", "[User]='" & Me.TboxUsername & "'")

Me.TboxIDNumber.Value = reportIDmax

Dim strsqlstilloriginator As String
strsqlstilloriginator = "Select [ID],[OriginatorName],[ShiftName],[Department] From TblOriginators WHERE [NoLongerOriginator]=0"
Set rsoriginators = db.OpenRecordset(strsqlstilloriginator)
Dim getnumoriginator As Integer
getnumoriginator = DCount("*", "tbloriginators", "[NolongerOriginator]=0")

Dim Originators As Variant
Originators = rsoriginators.GetRows(getnumoriginator)

Dim ioriginator As Integer
Dim ioriginatorend As Integer
ioriginatorend = getnumoriginator - 1

For ioriginator = 0 To ioriginatorend

NumKASApproved = DCount("*", "TblKaizenInfo", "[KASOriginator]=" & Originators(0, ioriginator) & " AND [DateSubmitted] > #" & DateFrom & "# AND [DateSubmitted] < #" & DateTo & "# AND [KASApproved]='Approved'")
NumKASRejected = DCount("*", "TblKaizenInfo", "[KASOriginator]=" & Originators(0, ioriginator) & " AND [DateSubmitted] > #" & DateFrom & "# AND [DateSubmitted] < #" & DateTo & "# AND [KASApproved]='Rejected'")
NumKASCompleted = DCount("*", "TblKaizenInfo", "[KASOriginator]=" & Originators(0, ioriginator) & " AND [DateSubmitted] > #" & DateFrom & "# AND [DateSubmitted] < #" & DateTo & "# AND [KASComplete]='Completed'")
NumKASOverdue = DCount("*", "TblKaizenInfo", "[KASOriginator]=" & Originators(0, ioriginator) & " AND [DateSubmitted] > #" & DateFrom & "# AND [DateSubmitted] < #" & DateTo & "# AND [Overdue]=-1")
KASDepartment = Nz(DLookup("[DepartmentName]", "tblDepartments", "[ID]=" & Originators(3, ioriginator)), "Not Found")
KASShift = Nz(DLookup("[ShiftName]", "TblShifts", "[ID]=" & Originators(2, ioriginator)), "Not found")
KASOriginatorName = Originators(1, ioriginator)


rstoPeriodReport.AddNew
rstoPeriodReport("KASOriginator").Value = KASOriginatorName
rstoPeriodReport("KASApproved").Value = NumKASApproved
rstoPeriodReport("KASRejected").Value = NumKASRejected
rstoPeriodReport("KASCompleted").Value = NumKASCompleted
rstoPeriodReport("KASOVErdue").Value = NumKASOverdue
rstoPeriodReport("KASDepartment").Value = KASDepartment
rstoPeriodReport("KASSHift").Value = KASShift
rstoPeriodReport("ReportID").Value = reportIDmax
rstoPeriodReport.Update

Next ioriginator

[Rabbit]

It would help to know what's not working. Which line of code are you referring to? What is it supposed to do? What is it doing instead? Are you getting error messages? What are those messages?

[Seth Schrock]

Try formatting your date text boxes as dates instead of using input masks. You do this through the control properties, Format tab, top property. The property is Format which allows you to pick the format of the control and several of the formats have to do with dates: both long and short date among them. That might fix the problem with your code as well since Access will be seeing the data as a date. Otherwise your code looks good to me at first glance.

[zmbd]

In TABLE design mode check the following fields are set for date and not text. (If you're not erroring on lines 31 and 32 with a wrong datatype message I suspect these are set to text.)
[tblReportsID]![DateFrom]
[tblReportsID]![Dateto]

Follow Seth's advice on the form control properties.

Curious... lines 19 and 20 set variables with date datatype why are you not using these in lines 31 and 32?

You may also need to use the explicit date deliminaters "#" around the date once you verify that the table structure is correct.

Starting at line 55 you start building your conditions within the domain function... I sugest you do not do so. Instead, build the conditional string first and then insert the variable into the function.
Why?
Because you can build the string,
insert a stop
and then "?thestring " in the immediates window to get a look at how the string is resolving. As you have it now... there is no easy way to look at the conditional you've built.

I don't think I've said that very well, very long day in the lab ?)

Your line 55 (note I've cut this appart, Direct cut and paste will not work):

NumKASApproved = DCount
 ("*", "TblKaizenInfo", 
    "[KASOriginator]=" & Originators(0, ioriginator) & " 
      AND [DateSubmitted] > #" & DateFrom & "# 
      AND [DateSubmitted] < #" & DateTo & "# 
      AND [KASApproved]='Approved'")

Mine: (note I've cut this appart, Direct cut and paste will not work):

zcondition = "[KASOriginator]= " & _ 
   Originators(0, ioriginator) & _
   " AND [DateSubmitted] > #" & DateFrom & _
   "# AND [DateSubmitted] < #" & DateTo & _
   "# AND [KASApproved]='Approved'"
'
'<<<Insert a STOP and <ctrl+g>?zcondition
'<<<or Debug.Print zcondition here
'<<<to see how the string is evaluating
'
Dcount("*","TblKaizenInfo",zcondition)

usually it is a string mis-build that causes these issues.

[twinnyfo]

James,

My initial thought was the same as Z's recommendation to use Date delimiters ("#"). This is a common coding error in VBA. Why MS doesn't allow direct date comparison is beyond me....

[NeoPa]

I'm curious Twinny. In what way does MS not allow direct date comparison? My understanding is that date work in both Access and Jet is as fully featured as you could hope for. I can't think of anything one would want to do with dates in either that isn't well supported.

[TheSmileyCoder]

@Twinny
If you treat the information as a date, I.e. store it in date type fields, and use date type variables then it is possible to do standard date comparisons. If you treat it as strings, and store it as strings, Access will treat it as strings.

[twinnyfo]

@ NeoPa and @ Smiley,

It just seems to me that so many times when I would try to work with dates in VBA code (all values declared and stored as dates) I could not get any comparisons in my SQL strings to work, and in order to get it to work, I had to use "#" around the date in question.

To me, this has always been frustrating, because deep down, I know that a "date" is really a "number" but stored in a different format. Not sure what I have been doing wrong. But, since I have been able to figure this out, I haven't had any problems.

Also, not sure this thread should address my challenges with dates....

[TheSmileyCoder]

The # is a way to indicate that a date starts here and ends there.

Otherwise 2012-10-22 would be considered as 2012 minus 10 minus 22=1980

We as humans see and recognize that its a date, but to be honest we don't KNOW its a date. It is similar to the way we enclose strings in single or double quotes to distinguish string literals from variable/function names

[NeoPa]

I think Smiley has expressed that well Twinny. Remember that as far as the VBA is concerned it has simply been asked to express that date value in a human readable form. It doesn't even know, within that particular VBA process, that the results of its work are to be assigned to a SQL string.

SQL strings, on the other hand, are no more VBA related than any other string. VBA has no way of knowing that it's even a SQL format string, let alone that it's a SQL destined specifically for the Jet SQL engine. The idea of Jet and Access being closely coupled is one that you should let go of as far as the programming of either is concerned. As a package they are, but SQL (All SQLs.) works exclusively from command strings and has no knowledge of what else may be going on in VBA etc.

This gets us on to how SQL recognises what is being requested of it by the provider of the command string. Clearly there must be protocol rules as to what means what. Some of these cover how literal values (NB. Not all date references are literal values by any means - which tends to confuse people horribly so make a good mental note of that.) are recognised and interpreted. For a set of characters to be recognised as a date they must (in all SQLs) be enclosed in hash (#) characters.

See Literal DateTimes and Their Delimiters (#) and the related links (found therein) for more on these issues.