Results 1 to 6 of 6
  1. #1
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253

    modify parameter query


    I couldn't get code to paste here so I attached it. I am trying to change the first one from enter start and stop date to entering a specific month and year to get the results for a whole month. I almost have it but I am getting the enter parameter value box. I am not seeing what I have wrong in second set of code.
    Attached Files Attached Files

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by NightWalker View Post
    I am not seeing what I have wrong in second set of code.
    Quote Originally Posted by NightWalker View Post
    I have changed to this but when I run it I get an "Enter parameter value" box that says qry_343sTested_Step1
    Code:
    SELECT 
    tbl_ProductionItem.ProductionItem, 
    tbl_ProductionItem.ProductionItemPartNumber, 
    qryTotalMetricsUnion.Total, 
    qryTotalMetricsUnion.Failed
    
    FROM tbl_ProductionItem INNER JOIN qryTotalMetricsUnion ON tbl_ProductionItem.ProductionItemPartNumber = qryTotalMetricsUnion.PN
    
    WHERE [qry_343sTested_Step1].[TestedDate] Between DateSerial(Year(Date()),1,1) And DateSerial(Year(Date()),12,31))
     AND Month([qry_343sTested_Step1].[TestedDate]) = [Enter month]
    
    ORDER BY tbl_ProductionItem.ProductionItem, tbl_ProductionItem.ProductionItemPartNumber, qryTotalMetricsUnion.Date;
    "qry_343sTested_Step1" is in the WHERE clause but NOT in the FROM clause....therefore you cannot use "qry_343sTested_Step1" in the WHERE clause.

    "DATE" is a reserved word in Access and shouldn't be used as an object name. Also "Date" is an Access built in function.



    ----------------------------
    First query, maybe........ (Untested!!)
    Code:
    SELECT 
    qry_SelDailyTested_Step1.TestedDate AS [Date], 
    qry_SelDailyTested_Step1.ProductionItemPartNumber AS PN, 
    qry_SelDailyTested_Step1.[Total Tested] AS Total, 
    IIf(IsNull([CountOfNumber of Fail]),0,[CountOfNumber of Fail]) AS Failed
    FROM qry_SelDailyTested_Step1 LEFT JOIN qry_SelDailyTested_Step3 ON (qry_SelDailyTested_Step1.ProductionItemPartNumber = qry_SelDailyTested_Step3.ProductionItemPartNumber) AND (qry_SelDailyTested_Step1.TestedDate = qry_SelDailyTested_Step3.TestedDate)
    WHERE Month(qry_SelDailyTested_Step1.TestedDate) = [Enter start Month (1 -12)] And Year(qry_SelDailyTested_Step1.TestedDate) = [Enter Year]
    ORDER BY qry_SelDailyTested_Step1.ProductionItemPartNumber 
    
    UNION ALL 
    
    SELECT qry_343sTested_Step1.TestedDate AS [Date], 
    qry_343sTested_Step1.ProductionItemPartNumber AS PN, 
    qry_343sTested_Step1.[Total Tested] AS Total, 
    IIf(IsNull([CountOfPassFail]),0,[CountOfPassFail]) AS Failed
    FROM qry_343sTested_Step1 LEFT JOIN qry_343sTested_Step3 ON (qry_343sTested_Step1.ProductionItemPartNumber = qry_343sTested_Step3.ProductionItemPartNumber) AND (qry_343sTested_Step1.TestedDate = qry_343sTested_Step3.TestedDate)
    WHERE Month(qry_343sTested_Step1.TestedDate) = Enter start Month (1 -12)] And Year(qry_343sTested_Step1.TestedDate) = [Enter Year]
    ORDER BY date;
    I don't like parameter queries. Much better to have two text boxes on a form to get the month and year - easier to validate..

    Again, shouldn't use "DATE" as a field/column name. Reserved word and an Access built in function.
    Plus "Date" is not very descriptive - date of what???

  3. #3
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    Thanks Steve. I didn't catch that it wasn't in the FROM section. It works perfectly. The "DATE" you are talking about is the name I gave to part of the query early in my learning and hadn't run into again until now. I changed those to "TestDate".

    I don't like parameter queries. Much better to have two text boxes on a form to get the month and year - easier to validate..
    This is for a report that provides the test metrics for a whole month. What would you suggest changing for me to not have parameter queries? Would you have a form that has input boxes that would then run the report automatically from the input of those boxes?

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by NightWalker View Post
    This is for a report that provides the test metrics for a whole month. What would you suggest changing for me to not have parameter queries? Would you have a form that has input boxes that would then run the report automatically from the input of those boxes?
    Yes.
    It allows better validation of the input data and you can have a better explaination of the required data/input. I added the info (1-12) to the input request, but what would happen if you were asking for the full month name "August" and someone entered "Aug"? The query would bomb. With a form and controls, you can validate the input data and ask for the correct data or change "Aug" to "August", then run the query/report.
    Using a form, the query would look to the form & control to get the data.

    I can't ever remember using an input box......maybe because I have such a short memory!

  5. #5
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    Thank you for that insight. I never thought of that as an option. (Part of still being new to all this) I think I will start processing all of my reports that way. Just like in my query in this post I was wanting to only have to put the month if I want this current year. By using your method I could make the current year a default and only have to enter a month. This is great Thank you very much for your help with this.

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You could also have the current month a default and they can change the month if they want to.

    --------------
    I use a combo box with a row source type as a value list of month names; the list of months don't change so a value list works.
    Using a combo box reduces the chance of someone "fat fingering" the month name and reduces typing.

    You can also set "Auto expand" to "yes" , so if you type an "a", the combo box automatically limits the month list to months that begin with an "a".

    The row source could be: "1";"January";"2";"February";"3";"March";"4";"Apri l";....;"12";"December"
    Col count = 2
    bound column =1
    Column width = 0

    This allows months to be picked by name, but return a month number.


    Anyway, good luck with your project........

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 07-08-2016, 08:01 AM
  2. How to modify query criteria for ranges?
    By cykchanaa in forum Queries
    Replies: 1
    Last Post: 08-06-2015, 09:03 PM
  3. Replies: 3
    Last Post: 04-18-2015, 12:01 PM
  4. Replies: 5
    Last Post: 09-17-2014, 07:28 PM
  5. Modify a query
    By saray in forum Queries
    Replies: 2
    Last Post: 06-08-2013, 02:05 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums