The Amazing Disappearing Report

[pullingteeth]

I'm baffled by a bug which is happening; I have a report which is invoked by two different options on a form:

        Case 3
            If IsDate(Nz(Text21.value)) Then
                DoCmd.OpenReport "detailed-summaryofevents", acViewPreview, , "thedate =#" _
                    & Text21.value & "#"
            Else
                MsgBox "Please enter a valid date", vbCritical
            End If
        Case 4
            If IsDate(Nz(Text31.value)) Then
                MsgBox "Calculating report. This will take a few seconds..."
                DoCmd.OpenReport "detailed-summaryofevents", acViewPreview, , "thedate between #" _
                    & (Text31.value - 7) & "# and #" & Text31.value & "#"
            Else
                MsgBox "Please enter a valid date", vbCritical
            End If

* If I select option #3, it works. If I select option #4, it doesn't work.

* I tried putting a "msgbox 'hi'" statement in the Report_Open() and Report_Activate() events; they don't show up in option #4 choices.

* If I run option #3 AFTER a failed option #4, the report is in the #4 format (i.e. it has a range of dates, although not exactly the range of dates that it should have.) Running option #3 again after this generates a normal #3 report.

* It is not the case that there are no results: a) I'm catching and dealing with the "empty report" error; b) if option #3's report has data for a particular date, then option #4's report should also have data.

Any ideas?

Thanks!

 

[dhookom]

What do you mean by "it doesn't work"? Have you tried to put a breakpoint in the code to step through the lines one at a time? Do you realize the report will not open until the msgbox is closed?

You should rename Text31 to something that makes sense. I would probably just assign s where clause in the Case options and then open the report later.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[pullingteeth]

By "it doesn't work" I mean that:
a) It pops up "calculating report..." [I added that to check that it steps into that clause]; i.e. Text31.value is definitely a date.
b) It doesn't pop up any of the msgboxes which I added to the report open and activate subs for testing purposes (which DO pop up using the "3" option)
c) The report doesn't appear, the system isn't busy, etc. Nothing happens.

I'll try debugging it later on line by line and will report back.

 

[dhookom]

I assume you click "ok" on the initial msgbox and the report doesn't appear. Is the current form opened in dialog mode? Does the report run directly from the database window?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[pullingteeth]

I ran the debugger. The line:


DoCmd.OpenReport "detailed-summaryofevents", acViewPreview, , "thedate between #" _
& (Text31.value - 7) & "# and #" & Text31.value & "#"


is stepped into, but produces no action *unless* the report is empty, in which case it will run the "report empty" sub routine in the report. Otherwise, the line apparently does nothing, and the debugger continues to the next line.

The form isn't opened in dialog mode. The report works fine both from the database window, and if "option 3" is selected, as per my first post. Very strange, no?

 

[dhookom]

How about:

DoCmd.OpenReport "detailed-summaryofevents", acViewPreview, , "thedate between #" _
& Format(DateAdd("d",-7, Text31),"mm/dd/yyyy") & "# and #" & Format(Text31,"mm/dd/yyyy") & "#"

or hard-coded dates

DoCmd.OpenReport "detailed-summaryofevents", acViewPreview, , "thedate between #6/1/2004# and #6/30/2004#"

BTW: is the report closed when this code is run?



Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[pullingteeth]

Ahhhh! Of course doofusness is part of the problem here. I had an "On Error Goto..." at the start of my sub, and I wasn't escalating the error if it didn't match my specific criteria.

Now that I've fixed that problem, I'm getting a type mismatch on Text31.value, when I try using either DateAdd (per your syntax) or -7 (per my syntax). The value, when I mouseover it, is "09/12/2004". Is this not an accepted date format to convert? (When I test it with IsDate(), it returns true.)

Thanks

 

[dhookom]

Try separate out the where clause:

Dim strWhere as String
strWhere = "thedate between #" _
& Format(DateAdd("d",-7, Text31),"mm/dd/yyyy") & "# and #" & Format(Text31,"mm/dd/yyyy") & "#"
Debug.Print strWhere
DoCmd.OpenReport "detailed-summaryofevents", acViewPreview, ,strWhere

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[pullingteeth]

Right there ahead of you. I tried that and it breaks on the date in the same way that I described in my immediately previous post.

 

[dhookom]

Is "thDate" field a date field? If you look at TheDate in the datasheet of the report's record source, is it left or right aligned?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.