Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2013
    Posts
    4

    Red face Unanswered: Parameters causing no records to show when running a query - help!

    Hello

    I'm using Access 2003.

    When I run the following query, it returns no results regardless of the data I input, however if I leave the parameter windows blank, all the data is shown. I can't seem to crack this.

    Please see below the following SQL:

    SELECT DISTINCTROW Table_Department.Department, Table_Facilitators.Shift, Table_Briefings.[Week Commencing], Table_Briefings.Month, Table_Briefings.Year, First(([Table_Briefings].[Performance]+[Table_Briefings].[News]+[Table_Briefings].[People])/3000*100) AS Average_Content, First(([Table_Briefings].[Preparation]+[Table_Briefings].[Body Language]+[Table_Briefings].[Voice]+[Table_Briefings].[Questions]+[Table_Briefings].[Facilitation])/5000*100) AS Average_Delivery, First(([Table_Briefings].[Interaction]+[Table_Briefings].[Attentive Audience]+[Table_Briefings].[Colleague Feedback])/3000*100) AS Average_Engagement, Avg(Table_Briefings.[Total Time]) AS [Avg Of Total Time], Avg(Table_Briefings.[Colleagues Briefed]) AS [Avg Of Colleagues Briefed]
    FROM Table_Facilitators INNER JOIN (Table_Department INNER JOIN Table_Briefings ON Table_Department.[Department ID] = Table_Briefings.[Department ID]) ON Table_Facilitators.[Facilitator ID] = Table_Briefings.[Facilitator ID]
    GROUP BY Table_Department.Department, Table_Facilitators.Shift, Table_Briefings.[Week Commencing], Table_Briefings.Month, Table_Briefings.Year, Table_Briefings.[Department ID], [Please enter Date - Part 1 of 3], [Please enter Date - Part 2 of 3], [Please enter Date - Part 3 of 3], [Please enter a Department]
    HAVING ((([Please enter Date - Part 1 of 3]) Is Null) AND (([Please enter Date - Part 2 of 3]) Is Null) AND (([Please enter Date - Part 3 of 3]) Is Null) AND (([Please enter a Department]) Is Null)) OR (((Table_Department.Department)=[Please enter a Department]) AND ((Table_Briefings.[Week Commencing])=[Please enter Date - Part 1 of 3]) AND ((Table_Briefings.Month)=[Please enter Date - Part 2 of 3]) AND ((Table_Briefings.Year)=[Please enter Date - Part 3 of 3]) AND (([Please enter Date - Part 1 of 3]) Is Not Null) AND (([Please enter Date - Part 2 of 3]) Is Not Null) AND (([Please enter Date - Part 3 of 3]) Is Not Null) AND (([Please enter a Department]) Is Not Null));

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    In my opinion, you should remove the following from the GROUP clause:
    [Please enter Date - Part 1 of 3], [Please enter Date - Part 2 of 3], [Please enter Date - Part 3 of 3], [Please enter a Department]
    Also, maybe you should use a WHERE clause instead of a HAVING clause. You do want to filter the data, don't you?

    Also, why do you need the user to input the month? It's already included in the week (1 to 52).

    One more thing. The way you have the HAVING clause parenthesized, you only allow for 1) having all NULL inputs, or 2) having all NON-NULL inputs. Is this your intent?

    Sam

  3. #3
    Join Date
    Jan 2013
    Posts
    4
    Yes I do want to filter the data. So should I replace the HAVING clause with the WHERE clause? It was my intent to be able to enter some NULL and some NON-NULL inputs.

    I require the user to put in the month because the week commencing is the 'day' in that month not the week number.

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    As far as I remember, Access doesn't allow for null inputs. If you don't input you get an error message saying something like "Expecting X parameters..."

    Uncheck the 'Display' checkbox in the query for the 4 fields I mentioned in my first post, change 'GROUP BY' to WHERE for those fields, and remove the HAVING. If you're not designing the query in Access' query designer, maybe you should be; it's very smart. I'm deliberately not being too clear or wordy because of this.

    I don't understand your answer regarding the week and month, but if you're confident about it, go ahead.

    BTW, I'm not at all sure that these measures will fix the query, but it might make the SQL a bit more efficient.

    Sam

  5. #5
    Join Date
    Jan 2013
    Posts
    4
    The annoying thing is, I actually have other queries setup in a similar way with the NULL and NON-NULL setup where this works.

    However for this query and a few others it doesn't seem to be playing ball. I'm not an SQL expert and I don't pretend to be, I'm using the Access Query Builder -- so when I enter the same setup in each query, I'm getting a bit confused why they are behaving differently.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Personally Im not a fan of asking for user input in queries IN the query. I'd rather use a form to get the parameters, validate them (to make certain they are sane and appropriate) and then supply them to the query OR as part of the reports filter arguments.

    this is especially a problem with dates which a user can easily screw up, it also means that the same set of parameters can be used for multiple reports, and or the user can refien the parameters and re run the report without havign to enter everything. With careful design it also means that the reports/querties can be run in batch mode, say overnight, without requiring user input.

    you can use the values on a form by pushing values from form to form/report:-
    say to a report called MrReport from MyForm
    Reports!MyReport!MyVariable = MyControl.value
    you can pull values from a form/report
    say from MyForm to a report called MrReport
    MyVariable = forms!MyForm!MyVariableInThisForm

    Or you can set the parameters as aprt of the openreport macro
    either as a 'where condition', minus the word WHERE
    or aws openArgs and process those arguments in the report
    OpenReport Method*[Access 2007 Developer Reference]
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Personally Im not a fan of asking for user input in queries IN the query. I'd rather use a form to get the parameters, validate them (to make certain they are sane and appropriate) and then supply them to the query OR as part of the reports filter arguments.

    this is especially a problem with dates which a user can easily screw up
    Amen, brother. In fact, what I usually do is to present the user with combo/list boxes with which to provide the needed parameters, and I set their LimitToList property to 'Yes.' That way, it takes a rocket scientist to deliberately crash the program.

    Sam

Posting Permissions

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