1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Parameters for a query

Discussion in 'Business Applications' started by nj12nets, Aug 4, 2006.

Thread Status:
Not open for further replies.
Advertisement
  1. nj12nets

    nj12nets Thread Starter

    Joined:
    Feb 8, 2005
    Messages:
    202
    Right now my task is to create a form that will allow a user to specify a date paarameter and also specify a 'feeder'.

    I created a form with the area to input the parameters.
    I also created a query that will have all the information I will need in my report so that once i get th query finished I can base the report off of it.

    The code for the query is :
    SELECT tblFeederInspectionSheet.[Feeder ID], tblFeederInspectionSheet.Pole, tblFeederInspectionSheet.DateIssued, tblFeederInspectionSheet.Deficiency1ID, tblFeederInspectionSheet.Deficiency2ID, tblFeederInspectionSheet.Deficiency3ID, tblFeederInspectionSheet.Deficiency4ID, tblFeederInspectionSheet.DateCompleted
    FROM (((tblFeederInspectionSheet LEFT JOIN tblDeficiencies ON tblFeederInspectionSheet.Deficiency1ID = tblDeficiencies.ID) LEFT JOIN tblDeficiencies AS tblDeficiencies_1 ON tblFeederInspectionSheet.Deficiency2ID = tblDeficiencies_1.ID) LEFT JOIN tblDeficiencies AS tblDeficiencies_2 ON tblFeederInspectionSheet.Deficiency3ID = tblDeficiencies_2.ID) LEFT JOIN tblDeficiencies AS tblDeficiencies_3 ON tblFeederInspectionSheet.Deficiency4ID = tblDeficiencies_3.ID;




    I need to somehow make the query able search by feeder and display all results from the feeder selected as well as filter out by date parameters. Is this difficult to do? I'm learning access as i go along adn am picking it up but have very little experience in vba and minor exprience in access. I can follow instructions and can pick it up quickly. any suggesstions or ideas are appreciated.
     
  2. OBP

    OBP Temporarily Banned

    Joined:
    Mar 8, 2005
    Messages:
    19,889
    As you already have the query it is relatively easy to do what you want. You can modify your SQL with VBA with a "Where" clause to filter by the form's date, but this is harder to do if you are not that familiar with VBA.
    The way that I do it is to create a table with one or two fields depending on whether you want a single date or a date range. Enter a date in the tables date field.
    I then create a form for new search date table and set it's "Allow Additions property to NO, so it only ever updates the one record.
    Now in your queries criteria row enter
    forms![formname]![fieldname]
    where formname and fieldname ae the names of your form and it's date field.
    Add a button to your form to run your report, job doen.
     
  3. nj12nets

    nj12nets Thread Starter

    Joined:
    Feb 8, 2005
    Messages:
    202
    I want a date range. It twill most likely be 01/01/2006-12/31/2006 but for next year oif they want quarterly it would help to have date range. I don't know vba. I already created the form that will enter the parameters. it has a combo box and two text boxes set to short date. Also, which field do i put the criteria into? does it matter?
     
  4. OBP

    OBP Temporarily Banned

    Joined:
    Mar 8, 2005
    Messages:
    19,889
    The problem with text boxes is that they may not work with the method outlined above, you may get a "Too complicated to resolve" error when they are not bound fields.
    It is up to you which field you use, you could give the user an option of whch one to search by.
    But you will need to change the citeria to
    between forms![formname]![fieldname1] and forms![formname]![fieldname2]
     
  5. nj12nets

    nj12nets Thread Starter

    Joined:
    Feb 8, 2005
    Messages:
    202
    I dont get an error but I don't get any results. is it a .before fieldname or! Someone told me its a . but im not sure. not sure why im not gaining any results when i should be
     
  6. OBP

    OBP Temporarily Banned

    Joined:
    Mar 8, 2005
    Messages:
    19,889
    It is definitley a"!".
    Any chance of posting it as an attachment?
    Try putting the dates that or on your form directly in to the query to ensure that the criteria works.
    Replace the forms![formname]![Fieldnames] with the actual dates.
     
  7. nj12nets

    nj12nets Thread Starter

    Joined:
    Feb 8, 2005
    Messages:
    202
    ok i took most of the data out though for company reasons. ill pm u the pw
     

    Attached Files:

    • dba.zip
      File size:
      110.2 KB
      Views:
      8
  8. OBP

    OBP Temporarily Banned

    Joined:
    Mar 8, 2005
    Messages:
    19,889
    Ok, your current problem is the combo on the report generator form.
    It has the first column as the FeederID which is number 1 to 157, the query is looking for the Feeder Inspection Sheet Table's FeederID which is in fact the second column of the combo, the feeder #.
    So if you switch around the two columns the query will work OK for the combo.
     
  9. OBP

    OBP Temporarily Banned

    Joined:
    Mar 8, 2005
    Messages:
    19,889
    Here is an updated version with the combo and dates working in the query.
     

    Attached Files:

    • dba.zip
      File size:
      47.2 KB
      Views:
      6
  10. nj12nets

    nj12nets Thread Starter

    Joined:
    Feb 8, 2005
    Messages:
    202
    thank you its working now, im gonna go figure out how ot make it generate a report once i hit the button to run the query. any tips on that?
     
  11. nj12nets

    nj12nets Thread Starter

    Joined:
    Feb 8, 2005
    Messages:
    202
    the code im keeping to use with it right now is this:
    SELECT tblFeederInspectionSheet.[Feeder ID], tblFeederInspectionSheet.Pole, tblFeederInspectionSheet.DateCompleted, tblFeederInspectionSheet.Deficiency1ID, tblFeederInspectionSheet.Deficiency2ID, tblFeederInspectionSheet.Deficiency3ID, tblFeederInspectionSheet.Deficiency4ID, tblFeederInspectionSheet.PatrolledBy, tblFeederInspectionSheet.OtherComments
    FROM ((((tblFeederInspectionSheet LEFT JOIN tblDeficiencies ON tblFeederInspectionSheet.Deficiency1ID = tblDeficiencies.ID) LEFT JOIN tblDeficiencies AS tblDeficiencies_1 ON tblFeederInspectionSheet.Deficiency2ID = tblDeficiencies_1.ID) LEFT JOIN tblDeficiencies AS tblDeficiencies_2 ON tblFeederInspectionSheet.Deficiency3ID = tblDeficiencies_2.ID) LEFT JOIN tblDeficiencies AS tblDeficiencies_3 ON tblFeederInspectionSheet.Deficiency4ID = tblDeficiencies_3.ID) LEFT JOIN tblFeederlist ON tblFeederInspectionSheet.[Feeder ID] = tblFeederlist.[Feeder #]
    WHERE (((tblFeederInspectionSheet.DateCompleted) Between [Forms]![frmReportGenerate].[boxDateTo] And [Forms]![frmReportGenerate].[boxDateFrom]) AND ((tblFeederlist.ID)=[forms]![frmReportGenerate]![Combo6]));


    now my problem is i have to get the form to open a report im desigining
    but use the data entered in the form to run the query which the report is based on.

    Without VBA knowledge it makes this more difficult.

    any quick ways to do this?

    Im hoping the coding behind this task is not difficult but im pretty sure it needs vb code.

    any ideas?
     
  12. OBP

    OBP Temporarily Banned

    Joined:
    Mar 8, 2005
    Messages:
    19,889
    Don't have the command button run the query. Base your report on the query and have the Command button run the report.
     
  13. nj12nets

    nj12nets Thread Starter

    Joined:
    Feb 8, 2005
    Messages:
    202
    is that all? lol if it is then i feel stupid
     
  14. nj12nets

    nj12nets Thread Starter

    Joined:
    Feb 8, 2005
    Messages:
    202
    wow im an idiot it was that simple. i had been reading a tutorial to do this w/ the paramteres stuff but it said i needed vba to do it.
     
  15. nj12nets

    nj12nets Thread Starter

    Joined:
    Feb 8, 2005
    Messages:
    202
    dam, its displaying the deficiency ID and not the name, lemme see if i can change that. and the query doesnt seem to get the feeder # from the form because im getting a parameter entry box asking for the feeder. it actually runs the right report but only to the parameter you enter not whats entered in the form
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/489389

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice