Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2003
    Posts
    16

    Unanswered: query criteria question

    How do I get a stored query to only follow the WHERE statement if there is criteria specified for it. For instance, if I don't specify a date on my form, how do I get the query to look up all dates?

  2. #2
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    Either use a filter that you code on the form -

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "FormName"

    If Nz(Me![DateFieldOnForm]) = "" Then
    stLinkCriteria = ""
    Else
    stLinkCriteria = "[DateFieldInQuery]=" & "#" & Me![DateFieldOnForm] & "#"
    End If
    DoCmd.OpenForm stDocName, , , stLinkCriteria


    or check the form field value in the query

    WHERE (((QueryName.DateFieldInQuery)=IIf(Nz([Forms]![FormName]![DateFieldOnForm])="","",[Forms]![FormName]![DateFieldOnForm])));



    Matt

  3. #3
    Join Date
    Apr 2003
    Posts
    16
    If I use the filter on the form, what would I enter the code under? My Date controls on the form are combo boxes, if that makes a difference. Also, I need it to look up a range of dates. I have 2 date fields, the starting date and ending date, named Date1 and Date2 respectively. How would I incorporate that into the code? I'd rather use the code on the form, since I have quite a few different queries that I run off that form, and I think it would be easier than editing all my queries.

  4. #4
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    Use a comparison operator against you date fields.

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "FormName"

    stLinkCriteria = "[DateFieldInQuery] Between #" & Me![Data1] & _
    "# AND #" & Me![Data2] & "#"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

    Place the code in the Click event of the button that you are using to fire off the second form.

    How are you opening the form - macro or did you use the Wizard?

    Matt

  5. #5
    Join Date
    Apr 2003
    Posts
    16
    Actually, it is for a report. The reports are based on the queries I've already created. I have listbox that is used for selecting the report. So I used:

    Dim stDocName As String
    stDocName = Forms![frmReportsPD]![Reports]
    DoCmd.OpenReport stDocName, AcNormal
    Last edited by jasons78; 04-10-03 at 17:18.

  6. #6
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    Dim strReport As String
    strReport = "ReportName"

    ' Filter String
    Dim strFilter As String
    strFilter = "[DateFieldInQuery] Between " & "#" & Me![Data1] & _
    "# AND " & "#" & Me![Data2] & "#"

    DoCmd.OpenReport strReport, acPreview, , strFilter

  7. #7
    Join Date
    Apr 2003
    Posts
    16
    I tried the code you just gave me and its giveing me an error that says:

    Syntax error in date in query expression '(((a_audit.Date) Between #Forms![frmReportsPD]![Date1] # And #Forms![frmReportsPD]![Date2]#))'

  8. #8
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    You need to handle the blank date fields in the mannor appropriate for your application. Below I set the filter string to "" if either of the date fields are not populated. You may want to default the ending date to Now() if it is blank but the beginning date is populated.

    Also add a format value to the textbox to ensure a workable date value vs. 10/222/03 or @#$%^.

    The code below works for me assuming the following:
    Report Name: rpt_a_audit
    Form Name: frmReportsPD
    Recordsource of Report: a_audit

    Dim strReport As String
    Dim strFilter As String

    strReport = "rpt_a_audit"

    If Nz(Forms![frmReportsPD]![Date1]) = "" Or Nz(Forms![frmReportsPD]![Date2]) = "" Then
    strFilter = ""
    Else
    strFilter = "[a_audit].[Date] Between " & "#" & Forms![frmReportsPD]![Date1] & _
    "# AND " & "#" & Forms![frmReportsPD]![Date2] & "#"
    End If


    DoCmd.OpenReport strReport, acPreview, , strFilter


    Matt

  9. #9
    Join Date
    Apr 2003
    Posts
    16
    Ok, it works fine if there is no value for the dates, but when I put in date values I get an error that says:

    Runtime error 3079
    the specified field [a_audit].[Date] could refer to more than one table listed in the FROM clause of your SQL statement.

    My FROM clause is:
    FROM tbl_a_cust_svc INNER JOIN a_audit ON [tbl_a_cust_svc].[Claim Number]=[a_audit].[Claim Number]

    Jason

  10. #10
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    Can you post the rest of your code/query.

    Side-note - you can use either the 'Where' or 'Filter' parameter to limit or return a subset of the data your looking at. Read up to determine what best fits your needs. Both will produce the same results.

  11. #11
    Join Date
    Apr 2003
    Posts
    16
    SELECT DISTINCT ROUND(Sum(IIf([coverage verified]=1,1,0))/Count([coverage verified]),4) AS YesCV, ROUND(Sum(IIf([coverage verified]=2,1,0))/Count([coverage verified]),4) AS NoCV, ROUND(Sum(IIf([coverage verified]=3,1,0))/Count([coverage verified]),4) AS NACV, ROUND(Sum(IIf([First Contact]=1,1,0))/Count([First Contact]),4) AS YesfC, ROUND(Sum(IIf([First Contact]=2,1,0))/Count([First Contact]),4) AS NofC, ROUND(Sum(IIf([First Contact]=3,1,0))/Count([First Contact]),4) AS NAfC, ROUND(Sum(IIf([Claimants Contacted]=1,1,0))/Count([Claimants Contacted]),4) AS YesCC, ROUND(Sum(IIf([Claimants Contacted]=2,1,0))/Count([Claimants Contacted]),4) AS NoCC, ROUND(Sum(IIf([Claimants Contacted]=3,1,0))/Count([Claimants Contacted]),4) AS NACC, ROUND(Sum(IIf([9 Step Insured]=1,1,0))/Count([9 Step Insured]),4) AS Yes9SI, ROUND(Sum(IIf([9 Step Insured]=2,1,0))/Count([9 Step Insured]),4) AS No9SI, ROUND(Sum(IIf([9 Step Insured]=3,1,0))/Count([9 Step Insured]),4) AS NA9SI, ROUND(Sum(IIf([9 Step Claimant]=1,1,0))/Count([9 Step Claimant]),4) AS Yes9SC, ROUND(Sum(IIf([9 Step Claimant]=2,1,0))/Count([9 Step Claimant]),4) AS No9SC, ROUND(Sum(IIf([9 Step Claimant]=3,1,0))/Count([9 Step Claimant]),4) AS NA9SC, ROUND(Sum(IIf([Proper Communication]=1,1,0))/Count([Proper Communication]),4) AS YesPC, ROUND(Sum(IIf([Proper Communication]=2,1,0))/Count([Proper Communication]),4) AS NoPC, ROUND(Sum(IIf([Proper Communication]=3,1,0))/Count([Proper Communication]),4) AS NAPC, ROUND(Sum(IIf([Appraiser Contact Insured]=1,1,0))/Count([Appraiser Contact Insured]),4) AS YesACI, ROUND(Sum(IIf([Appraiser Contact Insured]=2,1,0))/Count([Appraiser Contact Insured]),4) AS NoACI, ROUND(Sum(IIf([Appraiser Contact Insured]=3,1,0))/Count([Appraiser Contact Insured]),4) AS NAACI, ROUND(Sum(IIf([Appraiser Contact Claimant]=1,1,0))/Count([Appraiser Contact Claimant]),4) AS YesACC, ROUND(Sum(IIf([Appraiser Contact Claimant]=2,1,0))/Count([Appraiser Contact Claimant]),4) AS NoACC, ROUND(Sum(IIf([Appraiser Contact Claimant]=3,1,0))/Count([Appraiser Contact Claimant]),4) AS NAACC, ROUND(Sum(IIf([Timely Inspections]=1,1,0))/Count([Timely Inspections]),4) AS YesTI, ROUND(Sum(IIf([Timely Inspections]=2,1,0))/Count([Timely Inspections]),4) AS NoTI, ROUND(Sum(IIf([Timely Inspections]=3,1,0))/Count([Timely Inspections]),4) AS NATI, ROUND(Sum(IIf([Appraisal completed 4 days]=1,1,0))/Count([Appraisal completed 4 days]),4) AS YesAC4D, ROUND(Sum(IIf([Appraisal completed 4 days]=2,1,0))/Count([Appraisal completed 4 days]),4) AS NoAC4D, ROUND(Sum(IIf([Appraisal completed 4 days]=3,1,0))/Count([Appraisal completed 4 days]),4) AS NAAC4D, [a_audit].[Date]
    FROM tbl_a_cust_svc INNER JOIN a_audit ON [tbl_a_cust_svc].[Claim Number]=[a_audit].[Claim Number]
    WHERE ((([a_audit].[Regional Office])=[Forms]![frmSearchPD]![regional office]));

    This is a database we are going to use for auditing files. Table a_audit hold the general information about a file, and tbl_a_cust_svc, that this query is based on, contains the specific customer service related questions for the file. I have 4 other tables and 4 other queries based on those tables, but the tables are all linked to table a_audit with one-to-one relationships. Basically this query is used to calculate the percentage of yes, no , and n/a answers for each field. I then have reports for each query that lists the question, and the corresponding percentage of each answer for that question.

    Also, you said you can use either the Where or filter parameter, but can you use both? Or is that why I'm getting this error, because I'm using both a filter and Where parameter? If I can only use one or the other I'll need to use the WHERE parameter, I think, because I use this query that I posted above for different "levels" of reports. For isntance, the above query is for the Regional Office level, but I also have one for Supervisor level, and Adjuster level. I just change the Where clause to

    WHERE ((([a_audit].[Supervisor])=[Forms]![frmSearchPD]![Supervisor]));, or

    WHERE ((([a_audit].[Adjuster])=[Forms]![frmSearchPD]![Adjuster]));

    so basically I have three stored queries for each table. So maybe it would be easier to use the WHERE parameter in this case, but I'm not sure. What would you suggest?


    Thanks,
    Jason

  12. #12
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    Not sure on your Runtime error 3079.

    I'll try to explain what I know about filtering the report - it is somewhat confusing - so someone else may have to jump in and correct me or better clarify.

    There are several ways that you can filter a report (relatively speaking):


    Having a Where clause in the underlying Query that the recordset is base on - as you currently do. This is not actually filtering the report but rather the recordset. Or you could dynamically build/change the Recordsource of the report in the reports Open event. Again, this is not actually filtering the report but rather changing the underlying datasource.


    To truely filter the report you can use the filtername and/or the wherecondition.

    DoCmd.OpenReport reportname[, view][, filtername][, wherecondition]

    filtername: A string expression that's the valid name of a query in the current database

    wherecondition: A string expression that's a valid SQL WHERE clause without the word WHERE.

    example:
    Dim strReport As String
    Dim strQuery as String

    strReport = "ReportName"
    strQuery = "NameOfQuery",

    ' Where String
    Dim strWhere As String
    strWhere = "[Field] = " & variable

    'Open the report with a filter querey and a where condition
    DoCmd.OpenReport strReport, acPreview, strQuery, strWhere


    You can set or clear the Filter property(Where condition) of the report when it is open using code like this
    strSQL = "[Field] = " & variable
    Reports![ReportName].Filter = strSQL
    Reports![ReportName].FilterOn = True

    or to clear: Reports![ReportName].Filter = ""

    This will only work if the filtername parameter is blank - despite the fact that if you use both a filtername and wherecondition they are strung together as the Reports![ReportName].Filter with an AND
    i.e. Reports![ReportName].Filter = filtername AND wherecondition

    can't clear or set filter with this
    DoCmd.OpenReport "ReportName", acPreview, strQuery,""
    DoCmd.OpenReport "ReportName", acPreview, strQuery,strWhere

    but ok to clear or set filter in this example
    DoCmd.OpenReport "ReportName", acPreview, ,strWhere

    I would use the Where condition - as coded in the previous examples (even though the variable was written as strFilter - my bad..)
    And yes you can tack on the Wherecondition even if you query has a where condition of its own.

    I'll try to have a better look at your query but I'm not sure why the error message popped up on you.

    Matt

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •