By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,930 Members | 1,492 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,930 IT Pros & Developers. It's quick & easy.

Date comparing Problem

P: 94
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?

Expand|Select|Wrap|Line Numbers
  1. Dim rstoPeriodReport As DAO.Recordset
  2. Dim rsfromKaizenDB As DAO.Recordset
  3. Dim rsoriginators As DAO.Recordset
  4. Dim rsReportID As DAO.Recordset
  5. Dim todaysdate As Date
  6. todaysdate = Date
  8. Dim NumKASApproved As Integer
  9. Dim NumKASRejected As Integer
  10. Dim NumKASCompleted As Integer
  11. Dim NumKASOverdue As Integer
  12. Dim KASDepartment As String
  13. Dim KASShift As String
  14. Dim KASOriginatorName As String
  16. Dim DateTo As Date
  17. Dim DateFrom As Date
  19. DateTo = Me.TboxDateTo 
  20. DateFrom = Me.TboxDateFrom 
  23. Dim db As Database
  24. Set db = CurrentDb()
  26. Set rstoPeriodReport = db.OpenRecordset("tblPeriodReportKASByDepartment")
  27. Set rsReportID = db.OpenRecordset("tblReportsID")
  28. rsReportID.AddNew
  29. rsReportID("DateRequested").Value = todaysdate
  30. rsReportID("User").Value = Me.TboxUsername
  31. rsReportID("DateFrom").Value = Me.TboxDateFrom
  32. rsReportID("DateTo").Value = Me.TboxDateTo
  33. rsReportID.Update
  35. Dim reportIDmax As Integer
  36. reportIDmax = DMax("[ID]", "TblReportsID", "[User]='" & Me.TboxUsername & "'")
  38. Me.TboxIDNumber.Value = reportIDmax
  40. Dim strsqlstilloriginator As String
  41. strsqlstilloriginator = "Select [ID],[OriginatorName],[ShiftName],[Department] From TblOriginators WHERE [NoLongerOriginator]=0"
  42. Set rsoriginators = db.OpenRecordset(strsqlstilloriginator)
  43. Dim getnumoriginator As Integer
  44. getnumoriginator = DCount("*", "tbloriginators", "[NolongerOriginator]=0")
  46. Dim Originators As Variant
  47. Originators = rsoriginators.GetRows(getnumoriginator)
  49. Dim ioriginator As Integer
  50. Dim ioriginatorend As Integer
  51. ioriginatorend = getnumoriginator - 1
  53. For ioriginator = 0 To ioriginatorend
  55. NumKASApproved = DCount("*", "TblKaizenInfo", "[KASOriginator]=" & Originators(0, ioriginator) & " AND [DateSubmitted] > #" & DateFrom & "# AND [DateSubmitted] < #" & DateTo & "# AND [KASApproved]='Approved'")
  56. NumKASRejected = DCount("*", "TblKaizenInfo", "[KASOriginator]=" & Originators(0, ioriginator) & " AND [DateSubmitted] > #" & DateFrom & "# AND [DateSubmitted] < #" & DateTo & "# AND [KASApproved]='Rejected'")
  57. NumKASCompleted = DCount("*", "TblKaizenInfo", "[KASOriginator]=" & Originators(0, ioriginator) & " AND [DateSubmitted] > #" & DateFrom & "# AND [DateSubmitted] < #" & DateTo & "# AND [KASComplete]='Completed'")
  58. NumKASOverdue = DCount("*", "TblKaizenInfo", "[KASOriginator]=" & Originators(0, ioriginator) & " AND [DateSubmitted] > #" & DateFrom & "# AND [DateSubmitted] < #" & DateTo & "# AND [Overdue]=-1")
  59. KASDepartment = Nz(DLookup("[DepartmentName]", "tblDepartments", "[ID]=" & Originators(3, ioriginator)), "Not Found")
  60. KASShift = Nz(DLookup("[ShiftName]", "TblShifts", "[ID]=" & Originators(2, ioriginator)), "Not found")
  61. KASOriginatorName = Originators(1, ioriginator)
  64. rstoPeriodReport.AddNew
  65. rstoPeriodReport("KASOriginator").Value = KASOriginatorName
  66. rstoPeriodReport("KASApproved").Value = NumKASApproved
  67. rstoPeriodReport("KASRejected").Value = NumKASRejected
  68. rstoPeriodReport("KASCompleted").Value = NumKASCompleted
  69. rstoPeriodReport("KASOVErdue").Value = NumKASOverdue
  70. rstoPeriodReport("KASDepartment").Value = KASDepartment
  71. rstoPeriodReport("KASSHift").Value = KASShift
  72. rstoPeriodReport("ReportID").Value = reportIDmax
  73. rstoPeriodReport.Update
  75. Next ioriginator
Oct 16 '12 #1
Share this Question
Share on Google+
9 Replies

Expert Mod 10K+
P: 12,366
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?
Oct 16 '12 #2

Seth Schrock
Expert 2.5K+
P: 2,941
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.
Oct 16 '12 #3

Expert Mod 5K+
P: 5,397
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.)

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.
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):
Expand|Select|Wrap|Line Numbers
  1. NumKASApproved = DCount
  2.  ("*", "TblKaizenInfo", 
  3.     "[KASOriginator]=" & Originators(0, ioriginator) & " 
  4.       AND [DateSubmitted] > #" & DateFrom & "# 
  5.       AND [DateSubmitted] < #" & DateTo & "# 
  6.       AND [KASApproved]='Approved'")
Mine: (note I've cut this appart, Direct cut and paste will not work):
Expand|Select|Wrap|Line Numbers
  1. zcondition = "[KASOriginator]= " & _ 
  2.    Originators(0, ioriginator) & _
  3.    " AND [DateSubmitted] > #" & DateFrom & _
  4.    "# AND [DateSubmitted] < #" & DateTo & _
  5.    "# AND [KASApproved]='Approved'"
  6. '
  7. '<<<Insert a STOP and <ctrl+g>?zcondition
  8. '<<<or Debug.Print zcondition here
  9. '<<<to see how the string is evaluating
  10. '
  11. Dcount("*","TblKaizenInfo",zcondition)
usually it is a string mis-build that causes these issues.
Oct 16 '12 #4

Expert Mod 2.5K+
P: 3,284

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....
Oct 17 '12 #5

Expert Mod 15k+
P: 31,492
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.
Oct 18 '12 #6

Expert Mod 100+
P: 2,321
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.
Oct 22 '12 #7

Expert Mod 2.5K+
P: 3,284
@ 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....
Oct 22 '12 #8

Expert Mod 100+
P: 2,321
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
Oct 22 '12 #9

Expert Mod 15k+
P: 31,492
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.
Oct 22 '12 #10

Post your reply

Sign in to post your reply or Sign up for a free account.