Resolved (Again!): Modify Query (97 SR2)

Topic

New Reply

You can use the criteria…

Between [Enter Start Date:] And [Enter End Date:]

This will prompt them for both dates, and report only the data between them..

Is this what you’re looking for?

Reply | Quote

Replies

I don’t know where to put the Start Date and End Date variables.

The criteria for the date column is currently hard coded. Are you suggesting replacing the hard-coded date range with the Start/end date vars (prompted for when the report runs)?

Thanks for helping…

Reply | Quote

How is the criteria for the date hard-coded?

In your query design, just below the field designation, there’s an area for criteria. if you place the above in that field, it’ll prompt them for the criteria…

You might give it a try, if you’re asking for a programattical solution, you’re more of a wizard at that than I…

Reply | Quote

I got’cha. I tried it and it works fine. Just need to prompt with the default dates so user can press [Enter] to accept or type a new date…

I like that criteria syntax…

Reply | Quote

Hmmm….

So when you type “Between [Enter Start Date:] And [Enter End Date:]” you want your hard-coded defaults to be in the prompts?

Hmmm…

Reply | Quote

!!!

If you put your hard-coded date in the criteria field just below that, like this: #6/28/01# It will use that if nothing is entered in the box…

Does that work for you?

Reply | Quote

GifMeister,

Yes! Thanks. That’s ‘zactly what I need. And saves me lots of time trying to figure out how to do this via code. Perfect.

p.s. While I’m thinking about it: how about a smilie that shows a guy “dying of laughter” — crying, pounding his fists with a big smile…

Reply | Quote

Hi Kevin,
One small problem with that is that if your users do enter a date range then the query will return info that matches that range or your default range – i.e. you’ll always get the data that matches your defaults plus any that matches whatever they enter.

Reply | Quote

Uh oh. That’s not going to work then. I’m going to try posting this topic to the VBA forum an see if someone can help me get ahold of the query object to modify it.

Thanks for all your help.

Reply | Quote

Hi Kevin,
Sorry – I was in a bit of a rush when I posted that. I should have added a solution really! If you run the report from a form, you can add two text boxes to the form for your criteria (with default values equal to your required defaults) and then add =Forms!formnamehere!textboxnamehere as the criteria in your query.
If you can’t run it from a form, repost here and we can come up with some code to do it.
Hope that helps.

Reply | Quote

Rory, Drk,

I think I’ll leave off the default values for now and consider this one resolved. Thanks to you both for your help!

p.s. Rory, did I ever tell you, you look just like my old guitar and voice teacher?

Reply | Quote

50,000,000 Rory fans can’t be wrong!

Kevin, I know that you’re considering this resolved, but I’m just getting round to checking the threads from the past couple of days. Here’s my : the source for the report is a query, which is just a SQL statement. It sounds like the SELECT and FROM clauses of your SQL statement will always be the same, and the WHERE clause should default to your pre-specified criteria, but if the user wants to have other criteria, the WHERE clause should change accordingly. Sooo, perhaps when the report is launched, you ask the user to specify a date range, or just press . Behind the scenes, you build up your SQL statement in VBA setting the WHERE clause to your default if the user didn’t enter something, or to their date range if they did. Then you can set the record source of the report to that SQL statement.

Sound like something that you’d be interested in?

Reply | Quote

Shane,

>>Sound like something that you’d be interested in?

Quite frankly, YES! That’s the idea I was hoping to explore but I don’t know the Access objects well enough to modify the query’s where clause. And since Rory’s “criteria prompts” worked just fine, I figured it best to drop the subject. But…

If you wouldn’t mind pointing me in the right direction I would be unhesitatingly grateful.

Reply | Quote

Lemme see what I an gin up by way of demo, and I’ll post back.

Reply | Quote

See if this does the trick for you. You might consider a different mechanism for capturing user input, and you’d certainly want more robust error handling , but this should give you a push in the right direction for dynamically generating SQL statements to use as record sources for your reports.

I backsaved to Access 97, but haven’t had a chance to road test it in that version; works in Access 2000, SR2. Both versions are in the zip. HTH!

Reply | Quote

Shane,

Your work looks good. I’m still adapting your insights. The key piece I was ignorant of is me.recordsource = strQry.

You can tell I don’t hang out in the Access world. Anyway, that sets the query and I guess runs it too, right?

I noticed your query statement includes the brackets for the row fields as well as the table name:

>>SELECT [tbl_TestDates].[ID], [tbl_TestDates].[DateIn], [tbl_TestDates].[DateOut]”

The Access generated query that I wrote only puts the brackets on the table name:

“SELECT [Purchase Order Table].PurchaseOrderNumber, [Purchase Order Table].PurchaseOrderType, ”

Is there some mystery to this difference in syntax?

Reply | Quote

Great! I hope it works out for you.

You’re spot on regarding the record source: when the report opens it checks its record source, notices that it’s a query, executes the query, then displays the data.

As far as the syntax goes – . Maybe a syntactical change between 97 and 2000? At any rate, it appears that the query runs just fine with no brackets, brackets only around the table name, and brackets around both the table name and the field name. Anybody else have any ideas on this one?

Reply | Quote

[EDITED by Charlotte to cure horizontal scrolling

Shane,

I posted a revelation of how dumb I am and then quickly deleted it. Hope you didn’t see it…

Here’s the result of this should-have-been-simple-exercise-but-was-a-learning-experience-none-the-same adventure.

I’ve added some decent error code and figured out how to abort the report if the selected date range has no records.

Thanks for the push. You and Roy Rogers are right up there on my list of good guys.

Private Sub Report_Open(Cancel As Integer)
    Dim strStart As String
    Dim strEnd As String
    Dim strFiscalYear As String
    Dim strQry As String
    
    strQry = "SELECT [Purchase Order Table].PurchaseOrderNumber, " _
      & "[Purchase Order Table].PurchaseOrderType, "
    strQry = strQry & "[Purchase Order Table].PurchaseOrderDate, " _
      & "[Vendor Table].VendorName, [Purchase Order Detail Table].ExtendPrice "
    strQry = strQry & "FROM ([Vendor Table] INNER JOIN [Purchase Order Table] " _
      & "ON [Vendor Table].VendorID = [Purchase Order Table].VendorID) "
    strQry = strQry & "INNER JOIN [Purchase Order Detail Table] ON " _
     & "[Purchase Order Table].RecordID = [Purchase Order Detail Table].RecordID "
    
    strStart = IIf(Month(Now) < 7, CStr(Year(Now) - 1), CStr(Year(Now)))
    strEnd = IIf(Month(Now) < 7, CStr(Year(Now)), CStr(Year(Now) + 1))
    strFiscalYear = strStart & "/" & strEnd
    strStart = "07/01/" & strStart
    strEnd = "06/30/" & strEnd
    
    Do While True
        strStart = InputBox(Prompt:="Enter Start Date (mm/dd/yyyy): ", _
            Title:="Current Fiscal Year = " & strFiscalYear, Default:=strStart)
        If IsDate(strStart) Then
            Exit Do
        End If
    Loop
    
    Do While True
        strEnd = InputBox(Prompt:="Enter End Date (mm/dd/yyyy): ", _
            Title:="Current Fiscal Year = " & strFiscalYear, Default:=strEnd)
        If IsDate(strEnd) Then
            Exit Do
        End If
    Loop
        
    strQry = strQry & "WHERE ((([Purchase Order Table].PurchaseOrderDate) " _
          & "Between #" & strStart & "# And #" & strEnd & "# )) "
    strQry = strQry & "ORDER BY [Purchase Order Table].PurchaseOrderNumber;"

    Me.RecordSource = strQry
    
    If Me.HasData Then
        'Run the report
    Else
        MsgBox "There are no records to print in that date range. " _
             & "Sorry, try again",  vbCritical, "No records"
        Cancel = True
    End If
End Sub

Reply | Quote

Thank you Char. The lazy are often compensated for…

BTW, I spoke too soon about the me.hasdata property. Does not seem to do what the help makes it appear to seem to do.

Charlotte, chime in here, please. I know there are records for a given date range. If I remove the Hasdata test, the report runs just fine. But not matter what date range I enter, HasData always comes back 0 (zero).

Reply | Quote

Next time, you get instead!

See my answer to your other post. You can’t use HasData that way.

Reply | Quote

Something fires the query, but it’s not simply setting the recordsource property. When the open event ends, Access fires the query before displaying the report. The question is “How does one fire the query before the report is displayed so as to test whether there are any records in the recordset?

All this is so familiar using ADO, but the Jet database is a mystery to me…

Reply | Quote

Answer is, “you don’t”. What you do instead is use the NoData event of the report, which fires if there are no records in the recordset. Does that help?

I doubt that you can use the HasData property on the report itself. it’s intended to be used for subreports from the parent report and it is only available while printing or while in Print Preview.

Here’s a suggestion, though. put a break point in your code on the line that sets the recordsource. Then when it breaks, type the SQL string into the immediate window and look it over. Then copy the SQL and paste it into a new query and try to run it. That will show you exactly what is being returned by the recordset and may give you a clue as to what is going wrong.

Reply | Quote

As soon as I’m back in the office on Monday, I’ll try th OnNoData event in the report.

I’ve done the breakpoint test and when the me.recordset = strQry is executed, the immediate window reports (the contents of me.recordset as the query I just set it to. The query does not fire until after the open event ends. That must be the case.

So, I’ll try the OnNoData event which must fire after the Open but before the display of the report. That should do it. Thanks much!

Reply | Quote

Here’s the snippet:

Private Sub Report_NoData(Cancel As Integer)
    MsgBox "There are no records to print in that date range.", vbCritical, "No records"
    Cancel = True
End Sub

Works just like you said it would. The report is now bottled and ready for consumption. Thanks!

Reply | Quote

Kevin:

Charlotte is right on with the OnNoData event; I trust that all’s working smooth as silk now, eh?

Reply | Quote

Shane,

Just to get back to you: the NoData event works great!

Finis!

Reply | Quote

In the SQL, the only time it absolutely requires brackets is when there are spaces in the table or field name. The rest of the time they’re optional. The brackets do make it very clear whether the query engine is dealing with an object or an alias or a function, though.

Reply | Quote

Thanks, Charlotte! That’s why I come here — illumination.

Reply | Quote

Kevin,
I thought your pic looked disturbingly familiar!
Glad Shane got you sorted in the end.

Reply | Quote

I’ve written a query that contains default date criteria that I want the user to be able to override when they run th report that attaches to this query.

In the open event of the report I prompt for a start date and and an end date. Now what?

How do I modify the query so that the report uses their dates and not the defaults?

Reply | Quote