• Parameters query subform using date greater than (Access 97)

    Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » Parameters query subform using date greater than (Access 97)

    Author
    Topic
    #378301

    Access 97
    I am building a form that will let managers make and edit a Report. I have the parameter in the main form working but
    need a parameter in the sub form to work which I cant seem to get to work.
    am adding an attachment…problem is at open frmopening, then click on report button, then click on make report button
    In the drop down box call up Browns1 click run query button you will get 288 records now go back and try to make
    it so you can put a date in Begin Date and see if you can get less than 288 records and then add a Des No with no Date…..What i need is if a Begin Date is place in the sub form then in the query tmdate I need those date greater than and if there is a second Des No with no date well for that des no., I need all records no matter what date is with the records

    Viewing 0 reply threads
    Author
    Replies
    • #625870

      We need a bit more detail in order to help you. Is Begin Date a parameter in a query, a field in the data displayed in the subform, or a control on the form? What does you table structure look like – I presume there must be some relationships for you to use a subform? What is Des No? What is Browns1? What does “adding an attachment” have to do with your problem?

      • #625903

        Thanks Wendell for reponding, This morning I was going to attach what I was working on thinking it would be easier
        than just explaning but because you ask I am going to try,

        I have made a form that is going to let managers name their own reports so they can edit it or keep it and or make
        more reports. What they do is give it a name which goes into a table, this field that they give a name shows up on the form
        as a combo box drop down so they can reselect it to edit it or leave it like it is thats what browns1 was if you could
        have gotten my attachment.

        Now after they have given a name and selected it, a sub form is activated which gives them a chance to select a Des no.
        Des no is just a number that is related to project information in the sub form this number is selective from a drop down also.
        Now I am working on a query base on this form to be able to run a report.
        The des no has information that sometime contains a date but not always. I have the query working when a des no is
        selected it will bring up all the records related to that des no, now here’s my problem in the sub form I have a field
        so a date can be enter….(keep in mind that more than one des no can be selected) and with this des no. I have a field
        so a date can be related to a des no. if the managers wants a date with it ….if they do then they will want all records with
        that des no. equal to and greater than the date ….trying to add in the date is when my query breaks down and does not
        work giving wrong information…..
        I have tried >[forms]![subformname]![begin Date] in the query of the field that contains the date

        I have two thoughts as to way my query is not working when a date is added one it might have
        something to do with a subform or I need some kind of IIF statement because of null information
        in the date fields dealing with the des no. but realize too that in this sub form some des no
        the managers wont put in a date and some they will put in a date so I think I also need an IIF statement for
        that too

        now do you have enought or is this still too confusing?

        Greg

        • #625907

          To add to the above let me say this about Begin Date…..its a field in the sub form to be a parameter in a query for
          a report.

        • #625927

          (Edited by charlotte on 22-Oct-02 19:46. Afterthoughts)

          You aren’t referencing the subform correctly for starters. You need the name of the parent form in there first:

          Forms!ParentFormName!SubformName!ControlName

          Oops! I just noticed the Access 97 version. For A97, you’ll need this syntax:

          Forms!ParentFormName!SubformName.Form!ControlName

          • #626008

            In my Access 97 SR2, the following expressions all work OK:

            Forms!ParentFormName!SubformName!ControlName
            Forms!ParentFormName!SubformName.Form!ControlName
            Forms!ParentFormName!SubformName.Form.ControlName

            They also work if you replace the bang ! between ParentFormName and SubFormName by a period .

            What doesn’t work in Access 97, is

            Forms!ParentFormName!SubformName.ControlName
            Forms!ParentFormName.SubformName.ControlName

            • #626036

              Alright !!!! thanks a bunch all! I like it one step at a time …..the following got working a Date parameter
              [Forms]![ParentFormName]![SubformName]![ControlName]

              Now on with the other problem when I place a Des no without a Date I get back a null answer if I
              try to use one with a date and one without a date, I still get a null answer

              Because I am trying to be able to use a parameter with a date and without a date I think this maybe a double IIF
              I am not sure how to do the double IIF let alone the single IIF …. I would think its going to be something like

              IIf ([forms]![frmreportnew]![GCDESIGN subform2]![Begin] = is Null,

            • #626047

              There is a strange trick you can use for this. It was discussed in some detail in the thread starting with post 171726. It involves adding a dummy column to the query to test for an empty date. See the attached picture. Of course, you will have to substitute the actual names you use.

              You will probably have to declare the date parameter explicitly.
              In the design view of the query, select Query/Parameters…
              Copy the date parameter exactly as it is in the query grid to the Parameters box in the first row.
              Select Date/Time in the Data Type box.
              Click OK.

            • #626119

              Hans Thank for posting that post His question seem to be exactly what I am trying to do but I must be doing things he wasnt like trying to get more than one des no, I guess it worked for him because he only went after one thing at a time…..I have read all of them and kept trying different ones but not getting the results that I want
              The best I can get it to show me is one des no., with a date or no date but if I have more than one des no., it doesnt work right
              So I am going to attach showing the tables in the query and the last thing I tried
              The table report is just to make a name to call up parameters…the table GCDESIGN was made so I could make the subform
              is a field that the manager can type in a Name…remember in the sub form Des No is a drop down….One other thing
              I also tried Hans, just like your showed by removing the GCDESIGN and report tables but that would bring in too many
              des no….most of the time when I was getting too many extra des no was coming from other test reports that I had made

            • #626120

              here is a look at the form realize that the run query button is just for me to test until I get the query worked out….
              It will be replace with a Preview report button……. also if someone has a better way for me to do this I am all ears

            • #626363

              Greg,

              I am confused by your setup.

              Is GCDESIGN a temporary table meant to be used only for the selection of a report? In that case, you should delete all records before making a new selection; otherwise, you will keep on accumulating the selections.

              Since Begin is a field in this table, the user will be able to enter a different date for each Des No. Is that want you intend, or do you want one begin date to combine with the Des no’s?

              I think you can omit the Report table from the query (of course, the Reportname field must be from the GCDESIGN table then).

            • #626414

              I think I see what you mean about the report table. Yes want the begin date to be different for each des no and
              some cases no date will be put in. I dont know how to answer your question about ” Is GCDESIGN a temporary table”
              it is meant to be able to hold certain des numbers for as long a manager wants and it also there so that more than
              one manager can have their des number and yes this table is only for the report nothing to do with data storing.

            • #626421

              In so far as I am able to understand your description, the selection criteria (from the subform) are stored in the GCDESIGN table.

              • You only want those [Des no] that have been entered in the subform (and therefore in the GCDESIGN table). In the query design, you have an left join from DESCRIP to GCDESIGN. This means that all records from DESCRIP will be returned, regardless of whether there is a matching [Des no] in GCDESIGN. I think you need an inner join. You will probably need to change other joins in the query to inner joins too.
              • I had misunderstood your setup in a previous reply. The criteria for tmdate in the query design should not refer to the subform, but to the Begin field in the GCDESIGN table. Replace [Forms]!
            • #627089

              Hans I am back on this now if It takes the rest of the week!

              Do all managers share the same GCDESIGN table,

              YES

              or does each manager have a private copy of this table? If there is one table, it will contain the accumulated selections made by all managers.

              How are you going to distinguish between the entries made by each, unless you delete the existing records before allowing a new selection?

              This is the reason I created the report Table and reportname field Not only does this give each managers their own report name the way they want it….but It should let them set up more than one report….Hence Browns1(des nos 8967533 and 9048484) ….Browns2(des no 900001 and 900002 and 900003)….Browns3(des no 8900001 and 8900002 and 8900003)

            • #627093

              OK, the criteria for reportname in your query should take care of this. Have you tried the suggestions in my previous reply?

            • #627155

              It took making a query then building another query on the first query and a table.

              Hans and all thanks got the query to work….I used[the table name] with Dum:[the table name] …Is Not Null Is Null but in the query
              I removed the report table that would give me every thing in the reports table and make the date critria work right called this query qrydesignercostreport2…..but to get the results I was looking for I then had to make a query with qrydesignercostreport2 query and the report table to get just one
              reportname

              sql of query:SELECT report.reportname, qrydesignercostreport2.Des, qrydesignercostreport2.route_number, qrydesignercostreport2.location, qrydesignercostreport2.fname, qrydesignercostreport2.mname, qrydesignercostreport2.lname, qrydesignercostreport2.tmdate, qrydesignercostreport2.HrRate, qrydesignercostreport2.regtime, qrydesignercostreport2.overtime, qrydesignercostreport2.dataid
              FROM qrydesignercostreport2 RIGHT JOIN report ON qrydesignercostreport2.reportname = report.reportname
              WHERE (((report.reportname)=[forms]![frmreportnew]![Combo6]));

              The reportname is being selected from…… Combo6

              Once again thanks all who responded…read all posts and all posts helped me out!

              here is sql for qrydesignercostreport2

              PARAMETERS [forms]![frmreportnew]![GCDESIGN subform2]![Begin] DateTime;
              SELECT GCTMDATA.Des, DESCRIP.route_number, DESCRIP.location, GCNAME.fname, GCNAME.mname, GCNAME.lname, GCTMDATA.tmdate, GCTMDATA.HrRate, GCTMDATA.regtime, GCTMDATA.overtime, GCTMDATA.dataid, GCDESIGN.Begin AS dum, GCDESIGN.reportname
              FROM ((GCTMDATA LEFT JOIN GCNAME ON GCTMDATA.CONTACT_IDENTIFIER = GCNAME.CONTACT_IDENTIFIER) RIGHT JOIN DESCRIP ON GCTMDATA.Des = DESCRIP.DESNO) RIGHT JOIN GCDESIGN ON DESCRIP.DESNO = GCDESIGN.DesNo
              WHERE (((GCTMDATA.tmdate)>=[Begin]) AND ((GCDESIGN.Begin) Is Not Null)) OR (((GCDESIGN.Begin) Is Null));

            • #626040

              It depends on where you use them and how. A97 couldn’t handle referencing methods and properties of the subform without the explicit reference to the subform’s Form in there. A2k and later can handle it.

        • #626004

          Well, I agree that it’s a bit confusing, and your are trying to do a pretty complicated task. Never the less, I think Charlotte has identified your difficulty with the criteria – it’s a difference between Access97 and later versions. Yes, you will probably need some logic to check for null versus there being a date, but the first issue is getting your query to check the date field correctly.

    Viewing 0 reply threads
    Reply To: Parameters query subform using date greater than (Access 97)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: