I spent the last 10 hours straight trying to make this work, believe me I've tried to understand the CASE function but I'm still confused, there was no way I was going to post a question again as I know I've reached the end of my shelf life, but then again I thought, vbaInet spent his time helping me, the least outcome should be a sense of satisfaction that you helped someone sincerely, so I thought I ask you one more time, PLEASE bear with me.
For general knowledge, I open my shop daily from 9 am straight to 11pm (but sometimes I stay past midnight until 1AM, but never past 2AM.)
First the code you wrote for me...
Code:
Dim strWhere As String
Select Case TimeValue(Now())
Case "00:00:00" To "02:00:00"
strWhere = "[billdate] BETWEEN CDate(Date() - 1 & ' 03:00:00') AND CDate(Date() & ' 02:00:00')"
DoCmd.OpenReport "Today's Sale", acViewPreview, , strWhere
Case Else
strWhere = "[billdate] BETWEEN CDate(Date() & ' 08:00:00') AND CDate(Date() & ' 23:59:59')"
DoCmd.OpenReport "Today's Sale", acViewPreview, , strWhere
End Select
Here is what my understanding is from the above code...
current system time and day: 1st September 2012 1:00AM
Case 1 ...
If the time NOW is between 12 midnight and 2AM, then open the Today's Sale report where the BillDate field is between Yesterday’s date from 3am until today's first 2 hours. Meaning from: 31 Aug 2012 3:00AM until: 1st Sep 2012 2:00AM
(Note: if I choose 00:00 like previously thought, then it would display the date for the previous night as well, so that's why I chose the new day to be from 3AM onwards.)
Case 2 …
If NOT, meaning system time now is 31 Aug 2012 11:30PM, then open the Today's Sale report where the BillDate field is between the CURRENT day from 8AM until 11:59PM.
The PROBLEM:
Well no matter what I do, I can not get the first part nor the second part to function, I’ve attached a VERY simple DB with the relevant table and report and the module for your observation.
But if you have had it with me, then I understand and THANK you very much for staying up to this point.
Don't worry, as long as your question is related to the post you can post back to the thread.
I've just had a look at your db and can see that you have separate fields for date and time. Is this how it is setup in your original db, i.e. two fields BillDate and BillTime, date and time fields respectively? I don't see how you would think that you can query only the date field with the time part??
I might as well post the amended code based on the db you sent rather than await a response, so here it is:
Code:
Dim strWhere As String
Select Case Time()
Case "00:00:00" To "02:00:00"
strWhere = "([BillDate] = Date() - 1 AND [BillTime] >= #03:00:00#) OR " & _
"([BillDate] = Date() AND [BillTime] BETWEEN #00:00:00# AND #02:00:00#)"
DoCmd.OpenReport "Today's Sale", acViewPreview, , strWhere
Case Else
strWhere = "[BillDate] = Date() AND " & _
"[BillTime] BETWEEN #01:03:00# AND #23:59:59#"
DoCmd.OpenReport "Today's Sale", acViewPreview, , strWhere
End Select
... test it out and remember to adjust the dates in your table (if you're in GMT + 1 that is)
I was so anxious to see weather you replied or not, I checked the post on the bus this morning and when I saw your kind reply and saw the code, I just knew it would work this time. I was very very eager.
I did mention in my post number 11 that I am using separate date and time fields, but I did not bring it up again, as earlier when I read a link on how access stores date and time fields, I read that if in the table's design view, the field's datatype is a DATE/TIME datatype with general date, then Access stores the Date/Time data type as a double-precision number, so no matter the formatting, both date AND time are recorded, hence that's why I let it go.
There was again a small teething problem as it did not work again. I stripped the middle sandwich part and just used a simple Msgbox command after the Case "00:00:00" To "02:00:00", but still it did not fire at all, I still haven't grasped the CASE concept nicely, then I used snippets from the various examples you provided in this thread and what finally worked beautifully is the code below, it's functioning under all different conditions EXACTLY as I want it.
I wish I could make it up to you somehow but we live on the opposite sides of the planet, but I'm building a shrine in your honor thank you so much again.
Bobak.
Code:
Dim strWhere As String
If Time > TimeValue("00:00:00") And Time < TimeValue("03:00:00") Then
strWhere = "([BillDate] = Date() - 1 AND [BillTime] >= #07:00:00#) OR " & _
"([BillDate] = Date() AND [BillTime] BETWEEN #00:00:00# AND #03:00:00#)"
DoCmd.OpenReport "Today's Sale", acViewPreview, , strWhere
Else
strWhere = "[BillDate] = Date() AND " & _
"[BillTime] BETWEEN #07:00:00# AND #23:59:59#"
DoCmd.OpenReport "Today's Sale", acViewPreview, , strWhere
End If
OK, the code works perfectly, now the final problem I have is that this report has two subreports based on the same query which just separates the CASH sales from The RETURNED and Credit Card sales. When I opened the main form, it was fine showing the correct time frames, however the two subreports showed the old time patterns which was the simple DATE().
I thought about how to implement the code you made for me to accommodate the other two subreports too but I did not manage to make it work. You see the code you made for me had to be called from the outside of the report, either from a macro or a button someplace, so then I changed it to this...
Code:
Dim strWhere As String
If Time > TimeValue("00:00:00") And Time < TimeValue("03:00:00") Then
strWhere = "([BillDate] = Date() - 1 AND [BillTime] >= #07:00:00#) OR " & _
"([BillDate] = Date() AND [BillTime] BETWEEN #00:00:00# AND #03:00:00#) and " & "[cash?] = true and [returned] = false"
Me.Filter = strWhere
Else
strWhere = "[BillDate] = Date() AND " & _
"[BillTime] BETWEEN #07:00:00# AND #23:59:59# and " & "[cash?] = true and [returned] = false"
Me.Filter = strWhere
End If
This way I can just put this in the ON OPEN EVENT of the main report and it worked perfectly, opened up just like with the macro. Then I proceeded to do the same thing with the ON OPEN EVENT of the other two subreports. NOW, when I open the subreports on their own this works fine and they both open correctly, but when i open the main report i get the following error message:
Run-Time error 2101:
The setting you entered isn't valid for this property
... and when I click on the DEBUG the following line is highlighted in yellow...
Well I worked on it all day, read hundreds of posts, tried so many different way's and got soooo confused that had a complete brain freeze. I got it working perfectly now just the way I want it but in the most inelegant way possible. I will describe it here, not for experts obviously, but for poor schmucks like myself who are completely bewildered by all these codes.
First I made three different query's:
One for cash sale before midnight
one for visa sale before midnight
one for Return sale before midnight
All with these criteria on the Date and Time fields...
Date() Between #7:00:00 AM# And #11:59:59 PM#
Then I made another three query's:
One for cash sale After midnight
one for visa sale After midnight
one for Return sale After midnight
All with these criteria on the Date and Time fields...
Date()-1 >=#7:00:00 AM#
Date() Between #12:00:00 AM# And #3:00:00 AM#
Then on the ON OPEN event of my sub reports I put this code...
Code:
If Time > TimeValue("00:00:00") And Time < TimeValue("03:00:00") Then
Me.RecordSource = "Qry Today's Sale After Midnight Returned"
Else
Me.RecordSource = "Qry Today's Sale before Midnight Returned"
End If
And subsequently for all the other variants of the report. So all in all, lot's of DB objects for just one report, but as long as it's working and I have no other alternative, I'll take it.
Thank you again to all especially vbaInet for sticking with me till the end.
Very good effort Bobak. It's a pain trying to set the Filter of a subreport, actually it just isn't possible so yes changing the RecordSource, amending the querydef in code or creating textboxes to hold the values and pointing to them in the query are the only possible ways. The latter won't work in your case because of the criteria you're building.
Yes, that was a much more elegant way of doing it, at first I did not quiet understand what you were doing, but upon reading up on some of the function you used, I incorporated those methods instead, now it's much more cleaner. Well that's it for me, I'm putting away my vba punching bags for now
thank you so much again.
