The code gives wrong result.

[aman]

Hi Guys

The following code runs but doesn't give me right result. Both i and J values come up wrong. I think there is some problem in Date criteria. Can anybody please have a look and help me to solve my problem.

Private Sub Command4_Click()
Dim i As Integer
Dim j As Integer
Dim sum As Integer
i = DCount("username", "tbldata", "not username is null and [Date GoneAway Received]>=#" & Format(Combo0, "dd/mm/yyyy") & "# and [Date GoneAway Received]<=#" & Format(Combo2, "dd/mm/yyyy") & "#")
j = DCount("username", "tblmain", "not username is null and [Date GoneAway Received]>=#" & Format(Combo0, "dd/mm/yyyy") & "# and [Date GoneAway Received]<=#" & Format(Combo2, "dd/mm/yyyy") & "#")
sum = i + j
Label8.Caption = sum
End Sub

Thanks

 

[NickHa]

What is the value returned from Combo0? If you're not sure, put a breakpoint at the first DCount statement and inspect the value of the crieria string. Alternatively, display the combo box value in a msgbox. What I'm thinking is that the Format statement is trying to work on a value it doen't recognise as a date.
What is the locale setting of your machine? Query dates are mm/dd/yyyy irrespective of locale, so it may be that you need to reverse the dd/mm to mm/dd.

 

[aman]

Thanks NickHA. I changed the Date format to mm/dd/yyyy and it started working fine.

Now one more question, at the moment it displays the total number of cases processed between the two dates from both tables i.e tbldata,tblmain. But how can we display the total numbe of cases processed by each date between the date Range.

e.g if the combo=01/02/2012
combo2=05/02/2012

then it should display result like as below:

Date Number of cases processed

01/02/2012 100
02/02/2012 50
03/02/2012 120
04/02/2012 0
05/02/2012 20

Thanks

 

[NickHa]

To do this, you need a loop. Something like this:

Dim datStart As Date, datEnd As Date
Dim i As Integer, j As Integer
datStart = Combo0
DatEnd = Combo2
Do While datStart > datEnd
  i = DCount("username", "tbldata", "not username is null and [Date GoneAway Received] = #" & Format(datStart,"mm/dd/yyyy") & "#")
  j = DCount("username", "tblmain", "not username is null and [Date GoneAway Received] = #" & Format(datStart,"mm/dd/yyyy") & "#")
  Label8.Caption = i + j
  datStart = datStart + 1
Loop

Bearing in mind that as coded, this will only show the final loop value in Label8 as there's no pause between iterations
I haven't tested this - just given you the basis of a possible solution.

 

[NickHa]

... And of course you can tell it's untested - my comparison for dates in the loop is the wrong way around ...
Try Do While datStart < datEnd

 

[aman]

NikhHa

Is there any way so that we can store the result in Listbox rather than in a label.
so the listbox should store the Date and total number of cases processed for each date between the date range.

Thanks

 

[NickHa]

If you put a listbox on your form and name it lsbDates, then change the loop to fill it as follows:

Dim datStart As Date, datEnd As Date
Dim i As Integer, j As Integer
datStart = Combo0
DatEnd = Combo2
Rem remove any existing entries from listbox
Do While Me.lsbDates.ListCount > 0
  Me.lsbDates.RemoveItem Me.lsbDates.ListCount - 1
Loop
Rem populate the listbox with sequence number, date and sum
Do While datStart < datEnd
  i = DCount("username", "tbldata", "not username is null and [Date GoneAway Received] = #" & Format(datStart,"mm/dd/yyyy") & "#")
  j = DCount("username", "tblmain", "not username is null and [Date GoneAway Received] = #" & Format(datStart,"mm/dd/yyyy") & "#")
  Me.lsbDates.AddItem Me.lsbDates.ListCount + 1 & " Date=" & Format(datStart,"dd/mm/yyyy") & " Sum=" & i+j
  datStart = datStart + 1
Loop

 

[boblarson]

Just a quick FYI -

Read this about Dates in Access:

http://allenbrowne.com/ser-36.html