Simple Report whose Date() extends past midnight !!!

[fatboy123]

Hello and thank you in advance for your time.

I have a simple report whose underlying query is set to Today's sale table, so in the Date field, the criteria is simply Date() and everything is fine.

However, we have a peculiar problem in my city in that our shop hours are from 11am till 1pm and then from 8pm until 2am. And here is where the problem lies, I need a way to tell Access that our day ends, let's say at 4am, not 12 midnight. So the sale report should include all of today's sale PLUS the first 2 hours of the next day.

How can I do this? the only way I manage now is to set my PC time back by 3 hours which is creating problems elsewhere when trying to find accurate information.
Can I customize the range of the Date() command?

Thank you.

Bobak.

 

[spikepl]

No.

Assuming that your sales are stored with date and time, then what you can customize are the searches. A search for data for a given interval would usually be:

WHERE SalesDateTime BETWEEN SomeDate AND SomeOtherDate + 1

but in your case it would be:

WHERE SalesDateTime BETWEEN DateAdd("h",SomeDate, 11) AND DateAdd( "h", SomeOtherDate, 26)

 

[fatboy123]

Thank you so much Spikepl for your very quick reply, please bear with me for a little bit more as I'M a newbie I know.

Your suggestion is starting to make sense to me, I tried what you said in my query criteria but I think some small thing is wrong with the syntax, here is a screen shot...

hp://i.imgur.com/ZxorA.jpg?1
(it seems I'm not allowed to post pictures yet, can you please put a "tt" between the "hp" above.

This is what I came up with:

Between Date() And DateAdd("h",2,Date())

Explanation of what my thoughts on the above line are:

I'm telling Access to display all of today's sale BETWEEN (all of today) AND adding 2 hours to the end of today.

So please advice me again.

Bobak.

 

[spikepl]

How to post pix is described in the sticky at the top of this forum.

Between Date() And DateAdd("h",2,Date())

will give sales between 00:00 and 02:00 on the Date, and that is not what you want.



Between Date() And DateAdd("h",26,Date())

or, same thing,

Between Date() And DateAdd("h",2,Date()+1)

will give sales between 00:00 on the Date and 02:00 of the following day.

Read about how date and time are stored in Access here: http://support.microsoft.com/kb/210276

 

[fatboy123]

THAT'S IT !!!

It worked like a charm, However now one other problem surfaced, when I view the report on THIS day, it displays all of today's sale PLUS 2 hours into the next day like expected, but the employees usually print this report at around 2am which then it only displays the records created from 12:00 midnight until 2am.

I have no clue what to do about this ! I think I first have to tell Access that if the time now is between 12:00 midnight and 2am, then display yesterdays bills plus these two hours.

oh and the reason I can not post links or pictures is because my post count is less then ten.

Thanks again.

Bobak.

 

[spikepl]

1. I am aware of your post count. That's why: READ THE STICKY at the top of this forum

2. Study the link and the SQL bit I provided - there are enough clues there to deduce how to ask for data for a date one day prior to the one given by Date()

 

[fatboy123]

Yes, my mistake, I read it now and I feel ashamed.

I will do my best to come up with something tomorrow with the link you were kind enough to provide.

I would like to deeply thank you for spending your time with me today.

God Bless.

Bobak.

 

[fatboy123]

Hi again Spikepl,

I studied the link you sent me, even though it was hard to understand, and here's what I came up with.

First I like to really be honest and thank you for pointing me in the right direction, now if this is still wrong, I don't expect you to do the work for me ofcource, just point me in the right direction with function names I need to look up and understand.

So here's what I came up with:

IIf(DatePart("h", Now()) Between 0 and 2, DateAdd("h",26,Date()-1), Date())

And this is what I want to do:

At the moment of print, access should first see if the time NOW is between midnight and 2am, if so, then go back a day and display the sale for yesterday and the first 2 hours of the next day. Else just print today's sale.


Sincerely,

Bobak.

 

[spikepl]

I am not sure what the question here is.

You have listed all the required ingredients.

Do everything in small steps:

1. Make a query that works, and gives you "yesterday and the first 2 hours of the next day"
2. Make one query that works, and gives you "today's sale"
3. Once both of the above work as desired, but not before, combine the conditions (IIF or some combination of AND and OR)

 

[fatboy123]

Yes I realize I was not clear, as you said, work in small steps...

1-Make a query that works, and gives you "yesterday and the first 2 hours of the next day"

I can make a query that gives yesterdays results with: Date()-1 and it works.

I can make a query that gives today's sale with the next 2 hours from the next day with:
DateAdd("h",26,Date() and it works.

But I can not make this work:

DateAdd("h",26,Date()-1)

Namely, display yesterdays results WITH the first 2 hours of today's results. A simple database attached for clarification.

 

[fatboy123]

I know that I need to learn more about this myself, I've been pulling my hair now for weeks and now I'm even MORE confused then when I started, to make matters worse I think I've upset Spikepl a bit, English is not my 1st language so forgive me if I blunder.

I have now realized that what I want to do can not be carried out as a criteria, it has to be in VBA code on the reports ON OPEN command, I will try to troughly explain what I what to happen, please oh please guide me to what I need to know.


The Problem...

I have a Sale Report with 2 sub-reports, Visa and Returned sale, all having DATE() as record-source on the DATE field of the table. (I have separate DATE and TIME field in that table, but the report is set only on the DATE field.


upon report open I want this to happen:

Determine if time now is between 0 and 2am, if true then display yesterdays sale from 3am to the first 2 hours of today, so from 3am yesterday until 2am the next day.

If false then just display records of today starting from 3am.

On the ON OPEN event of my sale report I have tried the below code to no success...

Me![BillDate] = IIf(DatePart("h", Now()) Between 0 And 2, DateAdd("h",26,Date()-1), Date() Between 3 And 23:59)

ANY comment or help HIGHLY appreciated, God Bless.

Bobak.

 

[vbaInet]

Determine if time now is between 0 and 2am, if true then display yesterdays sale from 3am to the first 2 hours of today, so from 3am yesterday until 2am the next day.

If false then just display records of today starting from 3am.

So Bobak, looking at the above, I can see that there's a Sales slot not accounted for, i.e. between 2.01 a.m. and 2.59 a.m. Don't you want to display those sales too?

 

[fatboy123]

Thanks for your answer, No I don't, paraphrasing myself from the first post...

we have a peculiar problem in my city in that our shop hours are from 11am till 1pm and then from 8pm until 2am. And here is where the problem lies, I need a way to tell Access that our day ends, let's say at 2am, not 12 midnight. So the sale report should include all of today's sale PLUS the first 2 hours of the next day. so therefore we won't be having any sale from 2am until 11am of any day.

Hope I've made myself understandable.

Thanks again.

 

[vbaInet]

That's fine.

Since the reports will be based on just that one query you can do something like this:

Dim strWhere as string

Select Case TimeValue(Now())
    Case "00:00:00" And "02:00:00"
        strWhere = "TimeValue([Date/TimeField]) BETWEEN '00:00:00' AND '02:00:00'"
    Case Else
        ..... construct your strWhere here for 3 am and above...
End Select

' Pass the strWhere to the OpenReport method
DoCmd.OpenReport "ReportName", acViewPreview, strWhere

That's some aircode for you.

 

[fatboy123]

vbaInet, thank you in advance for your aircode, i sat this morning trying to work out your code to fit my application, first the simple things, My report had a criteria on the BillDate field which was "Date()" so that it would open up on today's date.

Well no matter what I did, I could not even perform this simple task first in VBA so I could start from there and implement your code, I felt so ashamed that I tried PM'ing you first but couldn't find a way.

So in all embarresments, how do I open the report to today's date with the "Date()" function in code? I searched a lot and as you can see from the simple DB attached, I tried many different code's to no success :banghead:

Bobak.

P.s I hope you can stay with me for just 3 or 4 more posts and I'll get there.

 

[vbaInet]

First of all drop the whole idea of doing anything in the Open event. You don't need it.

Here's an explanation of how to filter your report:

http://baldyweb.com/wherecondition.htm

And here's an example specific to your query:

DoCmd.OpenReport "ReportName"[COLOR=Red],[/COLOR] acViewPreview[COLOR=Red], , [/COLOR]"[COLOR=Blue][DateField] = Date()[/COLOR]"

 

[fatboy123]

vbaInet, I hope you'll be paitient with me a little bit more,

I played around and found the following to be working except that I don't know why the first IF is not returning yesterdays sale + the first 2 hours of the next day? But I'm very close to what I want now.

    If Time > TimeValue("00:00:00") And Time < TimeValue("02:00:00") Then
        
    DoCmd.OpenReport "Today's Sale", acViewPreview, , "[BillDate] = DateAdd(""h"",26,Date()-1)"
        
    Else
    
    DoCmd.OpenReport "Today's Sale", acViewPreview, , "[BillDate] = Date()"
    
    End If

 

[vbaInet]

See when we write code we expect that you use that as a benchmark and change the needful. Please look at my code and in the previous page and you will see what I did to [BillDate]

 

[fatboy123]

Well the reason was I could not really understand it and it was always giving me Case Error Mismatch, I finally got it to stop giving the error message by removing the AND and replacing it with a comma. But I still can not get the first part to fire up under any circumstance...

Dim strWhere As String
  Select Case TimeValue(Now())
    
        Case "00:00:00", "02:00:00"
        strWhere = "TimeValue([BillDate/BillTime]) BETWEEN '00:00:00' AND '02:00:00'"
        MsgBox "Between 0 and 2"
    
  Case Else
        
        MsgBox "After 3"

I have two columns in my table, one BillDate and one BillTime, am I suppose to put both in that string? I thought I'm thinning out my welcome so that's why I thought I try something else then to bother you again I got that TimeValue idea from your code. This what seemed to me a simple problem is driving me up the wall.

 

[fatboy123]

Studying your code again I think I've explained myself wrong in that you think I want the sales FROM 0 to 2am ONLY.

You see, sometimes we close before midnight and sometimes after midnight, what I want to do is first determine if NOW is after midnight, if so then print yesterdays report plus the first 2 hours of today, if not then just print today's report. I'm trying your code in small steps as below...

Dim strWhere As String
Select Case TimeValue(Now())
    
    Case "00:00:00" To "02:00:00"
    
    strWhere = "[billdate] = DateAdd(""h"", 26,Date()-1)"
    DoCmd.OpenReport "Today's Sale", acViewPreview, , strWhere
    
    Case Else

    strWhere = "[billdate] = date()" 
    DoCmd.OpenReport "Today's Sale", acViewPreview, , strWhere

End Select

My ONLY problem now is that I can not get the first part to work no matter how I try, that's all that's stopping me now. How do I get the DateAdd function to open my report with yesterdays data PLUS the first 2 hours of today's data.

This has been my question for all this two pages but I've managed to aggravate a few members because I could not make myself understood properly, hoping for forgiveness.

Bobak.