SQL statement gives different results in Access Query and VBA

[bsm2th]

I have an SQL statement that after processing becomes (the only processing is to insert the month and year values from a form.)

SELECT DISTINCT Households.zip 
FROM Households, visits 
WHERE Households.ID=Visits.household_id 
AND Visits.visit_month=6 
AND Visits.visit_year=2019 
AND Visits.monthly=-1 
AND Visits.weekofmonthly>0 
ORDER BY households.zip

In VBA this returns all rows in the table.

In an SQL query it works correctly using only values that match the query and giving the 5 correct zip codes.

I also tried the following with the same results

SELECT DISTINCT Households.zip 
FROM Households 
INNER JOIN Visits 
ON Households.ID=Visits.household_id 
WHERE Visits.visit_month=6 
AND Visits.visit_year=2019 
AND Visits.monthly=-1 
AND Visits.weekofmonthly>0 
ORDER BY households.zip

[NeoPa]

Hi. It's great that you've posted the actual SQL used rather than the VBA code that should create it as that's the right way to go about getting appropriate help.

In this case though, you say the results you get in VBA are different from those you get when copying this same SQL into a QueryDef and running that to get the data.

That is certainly unexpected behaviour so I suggest the next step here is first to show us the code you've used and how you've determined that the SQL used in the VBA is returning the full data set.

[bsm2th]

Good point. Here it is.. I assume that this problem is because the access SQL editor has the sense to correct my SQL, but I don't see how.

I've looked at the data used, and this is returning rows that don't match the monthly item for this date.

    strReport = "Monthly Report"
    strQuery = "SELECT DISTINCT Households.zip " _
             & "FROM Households, visits WHERE Households.ID=Visits.household_id " _
             & "AND Visits.visit_month=" & [Forms]![Index]![datemonth] & " AND Visits.visit_year=" & [Forms]![Index]![dateyear] & " AND Visits.monthly=-1 " _
             & "AND visits.weekofmonthly>0 ORDER BY households.zip"
    Set db = CurrentDb()
    Set rs = db.OpenRecordset(strQuery)
    'Close the report if already open: otherwise it won't filter properly.
    If CurrentProject.AllReports(strReport).IsLoaded Then
        DoCmd.Close acReport, strReport
    End If
    'Open the report.
    DoCmd.OpenReport strReport, acViewPreview   'Use acViewNormal to print instead of preview.
 

[twinnyfo]

You need to modify your VBA to include that INNER JOIN properly:

    strReport = "Monthly Report"
    strQuery = "SELECT DISTINCT Households.zip " _
             & "FROM Households " _
             & "INNER JOIN Visits " _
             & "ON Households.ID=Visits.Household_ID " _
             & "WHERE Visits.Visit_Month=" & [Forms]![Index]![datemonth] _
            & " AND Visits.visit_year=" & [Forms]![Index]![dateyear] _
            & " AND Visits.monthly=-1 " _
             & "AND visits.weekofmonthly>0 " _
             & "ORDER BY households.zip"
    Set db = CurrentDb()
    Set rs = db.OpenRecordset(strQuery)
    'Close the report if already open: otherwise it won't filter properly.
    If CurrentProject.AllReports(strReport).IsLoaded Then
        DoCmd.Close acReport, strReport
    End If
    'Open the report.
    DoCmd.OpenReport strReport, acViewPreview   'Use acViewNormal to print instead of preview.

But, one more challenge you have with the code above is that you are not saving the SQL Statement as a QueryDef (I am assuming the Report uses the created QueryDef as its Record Source? In which case:

    Dim strSQL As String
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
 
    strSQL = _
        "SELECT DISTINCT Households.zip " _
      & "FROM Households " _
      & "INNER JOIN Visits " _
      & "ON Households.ID=Visits.Household_ID " _
      & "WHERE Visits.Visit_Month=" & [Forms]![Index]![datemonth] _
     & " AND Visits.visit_year=" & [Forms]![Index]![dateyear] _
     & " AND Visits.monthly=-1 " _
      & "AND visits.weekofmonthly>0 " _
      & "ORDER BY households.zip"
    Set db = CurrentDB()
    Set qdf = db.CreateQueryDef("YOURQueryDefName", strSQL)
    Set qdf = Nothing
    Set db = Nothing
 
    If CurrentProject.AllReports(strReport).IsLoaded Then
        DoCmd.Close acReport, strReport
    End If
    'Open the report.
    DoCmd.OpenReport strReport, acViewPreview   'Use acViewNormal to print instead of preview.

Hope this hepps!

[NeoPa]

@Bsm2th.
You have a Recordset in your code and then you open a Report. I assume the Report is showing you the information that you expect to be adjusted by the Recordset which is how you know it isn't working. Unfortunately, there is nothing in your VBA that changes how the Report works so the Report will continue to give the same results until you do.

This is quite an easy thing to do when you know how. You simply set up a value for the WhereCondition parameter that includes the WHERE part of the SQL string you prepared earlier. To help I'll include some code for you. It's different from your. A little tidier and clearer. I will exclude the parts that are always set as that can be incorporated into the Report itself (or the QueryDef the Report uses as a RecordSource. As Twinny has already intimated, use the version where the two tables are joined using an INNER JOIN for preference. Both versions should work but that one is by far the preferred option for performance and readability.

I've also assumed that this code is run from the Form's associated module and as such supports references to the current Form simply as Me..

Dim strWhere As String
 
With Me
    strWhere = "([Visits].[visit_month]=%VM) AND " _
             & "([Visits].[visit_year]=%VY) AND "
    strWhere = Replace(strWhere, "%VM", .datemonth)
    strWhere = Replace(strWhere, "%VY", .dateyear)
End With
If CurrentProject.AllReports(strReport).IsLoaded Then _
    Call DoCmd.Close(acReport, strReport)
Call DoCmd.OpenReport(ReportName:=strReport _
                    , View:=acViewPreview _
                    , WhereCondition:=strWhere)

Closing the Report, if open, is probably unnecessary. If open it should just change it to the new settings. On the other hand it's probably good practive.
BTW you'll see in my example code I only use one line (albeit extended onto two for readability) for that If statement. Some like to use multi-line If statements in all circumstances but I prefer to use the simpler form where possible. You can change it back if you prefer.

[NeoPa]

Just to clarify from Twinny's post :

1. I certainly support that the version with the INNER JOIN would be a better way to proceed. Having checked both SQL strings visually I can confirm both should end up with the same data, but the INNER JOIN one would definitely be the one to choose for both performance and readability reasons.
2. It is also possible to update either the QueryDef used by the Report, or even the Report itself, to change the underlying SQL used, and if you ever feel the need to do this then Twinny's provided some code that will help.
   I don't like that approach myself as it seems overkill when you already have a WhereCondition parameter available that is for changing the filtering for this instance only. It leaves you with a changed Report object and that seems wrong to me. The last point is that, should you ever get to signing your projects, and that is still possible even today but far less common and more complicated than it used to be in earlier versions, then any such change to an object in the database done by code woul cause the signature of the project to be discarded as the design has been changed since it was last saved with the signature. As I say, this last consideration is getting increasingly less important but I feel the other reasons are still strong.
   That said, a good friend of mine and Access MVP, Armen Stein of J Street Technology, does seem to preach this approach, and has various code snippets available on his site to enable it (As it's a commercial site I'm precluded from linking to it from here but it's not hard to find and you can learn a great deal from it). Not to mention one of our own very good experts Twinny obviously sees it as a viable approach too.

I think, between the two of us, we've left you more than enough information (and even code snippets) to get this working for you. If you need more help though, just holler :-)

[bsm2th]

Still having trouble. At the openreport call, not having the strWhere causes a report with all rows of the database. With strWhere causes me to be prompted for month, year, weekofmonthly, and monthly. Then it uses all fields anyway. I verified that strWhere was correct. If it matters, the report only uses the zip field. Also, is there any purpose for the recordset here? openreport seems to be doing all the work(or trying :>)
Here is my code now.

Private Sub domonthlyreport()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strReport As String, strQuery As String, strWhere As String
 
    strQuery = "SELECT DISTINCT Households.zip " _
             & "FROM Households " _
             & "INNER JOIN Visits " _
             & "ON Households.ID=Visits.Household_ID " _
             & "ORDER by Households.zip"
 
    strWhere = "([Visits].[visit_month]=%VM) AND " _
             & "([Visits].[visit_year]=%VY) AND " _
             & "([Visits].[weekofmonthly]>0) AND " _
             & "([Visits].[monthly]=-1)"
    strWhere = Replace(strWhere, "%VM", Me.datemonth)
    strWhere = Replace(strWhere, "%VY", Me.dateyear)
 
    strReport = "Monthly Report"
    Set db = CurrentDb()
    Set rs = db.OpenRecordset(strQuery, dbOpenSnapshot)
    If CurrentProject.AllReports(strReport).IsLoaded Then DoCmd.Close acReport, strReport
    DoCmd.OpenReport strReport, acViewPreview, strQuery, strWhere 'Use acViewNormal to print instead of preview.
End Sub
 

[NeoPa]

Indeed not. You seem to be struggling to follow the instructions from two separate sources at the same time. Focus on one for now.

As you say, there is no need for the Recordset at all. That was from your original code and was based on a misunderstanding.

The code in my post #5 includes all you need. I already stripped out the Recordset code as unnecessary. I also showed how to call the OpenReport() method properly. In your latest version you have four parameters, which is a little random. You've tried to use both strQuery and strWhere, which makes no sense of course. There's no way to guess which you intended for which parameter as you've used poitional parameters instead of the named ones as suggested. The beauty of the strWhere approach (One of them at least.) is that you only need to specify the filtering, rather than build the whole SQL string.

Try that and, if it doesn't work then post the value of strWhere and we'll see if we can see why not. In this situation it's obvious why but if you do it properly then it should work.

[NeoPa]

Let me see if I can explain this more fully - at least some of the background concepts. Hopefully that will make it clearer why we're taking the steps we are.

To start with Reports are objects that run on some type of record source. It may be a simple table; it may be a predefined QueryDef; it may be a SQL string. Whatever it is is the RecordSource of the Report.

In your case it looks like the basic RecordSource of your Report should be equivalent to (Either SQL string or saved QueryDef - it doesn't much matter.) :

SELECT DISTINCT
       [Households].[Zip]
FROM   [Households]
       INNER JOIN
       [Visits]
  ON   [Households].[ID]=[Visits].[Household_ID]
WHERE  ([Visits].[weekofmonthly]>0)
  AND  ([Visits].[monthly])

Notice that the parts of the filtering (WHERE clause) that do not depend on any selection from your Form can be included in here. Notice also that ordering is irrelevant for Reports as they have their own way of handling sorting / ordering.

Remember, this is all included as part of the report and doesn't need to be specified again elsewhere. All we need to do is specify some further filtering, which we do using strWhere and the WhereCondition argument, to ensure the data on the Form is reflected in what's shown in the Report.

Now I look more carefully at the SQL itself it seems that simply returning unique Zip values doesn't seem like what you'd want in a report, so I suspect maybe something of what you've shared with us doesn't actually add up. It looks much more like the sort of SQL you'd find associated with a ComboBox the operator needs to select a value from. We'll have to wait for your response before we get a proper understanding I expect.

[bsm2th]

It looks odd, but a distinct list of zip codes for the month and year given is what I want at this point.

I am doing a monthly report for a foodbank I volunteer at. This report is for the state agency that we get most of our food from and this report documents who got food and is used to decide how much we get.

At this point I need a distinct list of zip codes where people got food. In the report, I break things down much further, First, given a zip code, a loop check each family for income source, assistance programs, etc. Then another loop grabs members from each family, adds a total number of people and breaks them down by age.

For some strange reason, this all works, I'm just stuck at the start getting that zip code list. On the report, each different zip code is a different line with all the info from the loops.

I've written this app before with Linux and Ruby on Rails. Access will be much better for the new version

One question I had from reading your reply. You say not to use strQuery and strWhere together, but then you seem to say that both are needed. What I called strQuery became the RecordSource, and strWhere was still needed. Am I declaring them in the wrong places ?

[bsm2th]

Here is what I have so far.

Private Sub domonthlyreport()
    Dim strReport As String, strQuery As String, strWhere As String
 
    strQuery = "SELECT DISTINCT Households.zip " _
             & "FROM Households " _
             & "INNER JOIN Visits " _
             & "ON Households.ID=Visits.Household_ID " _
             & "WHERE Visits.weekofmonthly>0 AND " _
             & "Visits.monthly=-1 " _
             & "ORDER by Households.zip"
 
    strWhere = "([Visits].[visit_month]=%VM) AND " _
             & "([Visits].[visit_year]=%VY)"
 
    strWhere = Replace(strWhere, "%VM", Me.datemonth)
    strWhere = Replace(strWhere, "%VY", Me.dateyear)
 
    strReport = "Monthly Report"
    If CurrentProject.AllReports(strReport).IsLoaded Then DoCmd.Close acReport, strReport
    DoCmd.OpenReport strReport, acViewPreview, strQuery, strWhere 'Use acViewNormal to print instead of preview.
End Sub
 

This is what I get for strQuery

SELECT DISTINCT Households.zip FROM Households INNER JOIN Visits ON Households.ID=Visits.Household_ID WHERE Visits.weekofmonthly>0 AND Visits.monthly=-1 ORDER by Households.zip

and strWhere

([Visits].[visit_month]=6) AND ([Visits].[visit_year]=2019)

[bsm2th]

Here is the report I'm trying to make

[NeoPa]

Bsm2th:
One question I had from reading your reply. You say not to use strQuery and strWhere together, but then you seem to say that both are needed. What I called strQuery became the RecordSource, and strWhere was still needed. Am I declaring them in the wrong places?

If that's your understanding then you may need to read my post again. strQuery is not needed. Something is required in the Report's RecordSource property for sure, but that something is stored as part of the Report object and shouldn't be set from your code. Think about it and you'll see that wouldn't make sense.

No. First design the Report as a saved object in your database. How you set the RecordSource is up to you but the result should be as explained in my earlier post. When you test it directly (IE. without running your code but just opening it.) you should see the data as unfiltered. This is fine. The Form is what provides the filter and you're bypassing that to test the basic Report object.

When you open the Report from the Form you specify no basic query but just the filter (WhereCondition argument = strWhere in this code).

[bsm2th]

Now this is getting clearer.

From what I understand (not much at the moment), the beginning SQL statement should be set as the RecordSource in the report and sqlWhere should be used in the openreport call.

What i read on the internet looked like the recordsource and strWhere both came from the openreport call.

I set the recordsource property in the report and am now getting prompted for month and year values, even though the strWhere string is correct. Here is my current code:

Private Sub domonthlyreport()
    Dim strReport As String, strWhere As String
 
    strWhere = "([Visits].[visit_month]=%VM) AND " _
             & "([Visits].[visit_year]=%VY)"
    strWhere = Replace(strWhere, "%VM", Me.datemonth)
    strWhere = Replace(strWhere, "%VY", Me.dateyear)
 
    strReport = "Monthly Report"
    Call DoCmd.OpenReport(ReportName:=strReport _
                        , View:=acViewPreview _
                        , WhereCondition:=strWhere) 
End Sub
 

The value of StrWhere is '([Visits].[visit_month]=6) AND ([Visits].[visit_year]=2019)'


The value of RecordSource is 'SELECT DISTINCT [Households].[zip] FROM Households INNER JOIN Visits ON [Households].[ID]=[Visits].[Household_ID] WHERE Visits.weekofmonthly>0 And Visits.monthly;'

[NeoPa]

Are you being prompted for [visit_month] & [visit_year] values? If so then I can only surmise that neither field is numeric.

If that is the case then they would probably be Text Fields, in which case the code needs a small alteration (See Quotes (') and Double-Quotes (") - Where and When to use them) :

strWhere = "([Visits].[visit_month]='%VM') AND " _
         & "([Visits].[visit_year]='%VY')"
strWhere = Replace(strWhere, "%VM", Me.datemonth)
strWhere = Replace(strWhere, "%VY", Me.dateyear)

If that isn't your problem then we will need to explore further as what you've shown seems perfect.

[Rabbit]

If the record source of the report only returns zip, then the where clause on that record source won't have the other fields on which to filter.

[NeoPa]

Doh!!

Good spot Rabbit. I missed that :-(

@Bsm2th.
That means the RecordSource for the Report should be changed to something like the following :

SELECT DISTINCT
       [Households].[zip]
     , [Visits].[visit_month]
     , [Visits].[visit_year]
FROM   [Households]
       INNER JOIN
       [Visits]
  ON   [Households].[ID]=[Visits].[Household_ID]
WHERE  ([Visits].[weekofmonthly]>0)
  AND  ([Visits].[monthly])

[bsm2th]

It worked!! Thanks to everyone who helped me!

BTW, the fields were numeric. Adding them to the SQL query fixed the problem.

[NeoPa]

Glad to hear you've got it all clear now.

Even more glad to see that you've developed as part of the process.

Possibly most glad that Rabbit picked up that the Fields weren't in the original SQL because I wasn't seeing it for looking.

[bsm2th]

Just in case people are interested, here is the code at this point, working correctly.

First, the RecordSource for 'Monthly Report'

SELECT DISTINCT [Households].[zip], [Visits].[visit_month], [Visits].[visit_year] FROM Households INNER JOIN Visits ON [Households].[ID]=[Visits].[Household_ID] WHERE Visits.weekofmonthly>0 And Visits.monthly; 

now the function

Private Sub doreportwithparams()
    Dim strReport, strQuery, strWhere As String
 
    strWhere = "([Visits].[visit_month]=%VM) AND " _
             & "([Visits].[visit_year]=%VY)"
 
    strWhere = Replace(strWhere, "%VM", Me.datemonth)
    strWhere = Replace(strWhere, "%VY", Me.dateyear)
 
    Select Case reports
        Case 5
                strWhere = strWhere & " AND ([Visits].[weekofmonthly]=%VW)"
                strWhere = Replace(strWhere, "%VW", Me.Thisweekbox)
                strReport = "Weekly Report"
                strQuery = "Weekly ZIPs"
        Case 6
                strReport = "Monthly Report"
    End Select
 
    'Close the report if already open: otherwise it won't filter properly.
    If CurrentProject.AllReports(strReport).IsLoaded Then
        DoCmd.Close acReport, strReport
    End If
    'Open the report.
    On Error Resume Next
    Select Case reports
        Case 5
            Call DoCmd.OpenReport(ReportName:=strReport _
                        , View:=acViewPreview _
                        , FilterName:=strQuery _
                        , WhereCondition:=strWhere)    'Use acViewNormal to print instead of preview.
        Case 6
            Call DoCmd.OpenReport(ReportName:=strReport _
                        , View:=acViewPreview _
                        , WhereCondition:=strWhere) 'Use acViewNormal to print instead of preview.
 
    End Select
    If Err = 2501 Then Err.Clear
End Sub
 

report is 5 for the Monthly Report

Thanks to everyone for all the help
Bob

[NeoPa]

Hi Bob.

Starting on line #28 you open a Report using the FilterName parameter. Can you explain what that is and why you're using it. I've never felt the need to use that as the WhereCondition parameter seems to handle everything I need for filtering. Seeing both in a single call is a surprise to me but I don't know why so maybe there's sense there that I simply don't see.

Obviously, my suspicion is that it is extraneous and unhelpful, but as I say, I can't be sure without knowing more of the details. It's the first time I've seen it in the thread so seems very suspicious.