Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Lounger
    Join Date
    Jun 2002
    Location
    Anderson, Indiana, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Parameters query subform using date greater than (Access 97)

    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

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Parameters query subform using date greater than (Access 97)

    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?
    Wendell

  3. #3
    Lounger
    Join Date
    Jun 2002
    Location
    Anderson, Indiana, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameters query subform using date greater than (Access 97)

    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

  4. #4
    Lounger
    Join Date
    Jun 2002
    Location
    Anderson, Indiana, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameters query subform using date greater than (Access 97)

    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.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Parameters query subform using date greater than (Access 97)

    <P ID="edit" class=small>(Edited by charlotte on 22-Oct-02 19:46. Afterthoughts)</P>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
    Charlotte

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Parameters query subform using date greater than (Access 97)

    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.
    Wendell

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Parameters query subform using date greater than (Access 97)

    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

  8. #8
    Lounger
    Join Date
    Jun 2002
    Location
    Anderson, Indiana, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameters query subform using date greater than (Access 97)

    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,

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Parameters query subform using date greater than (Access 97)

    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.
    Charlotte

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Parameters query subform using date greater than (Access 97)

    There is a strange trick you can use for this. It was discussed in some detail in the thread starting with <post#=171726>post 171726</post#>. 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.
    Attached Images Attached Images
    • File Type: gif x.gif (10.0 KB, 1 views)

  11. #11
    Lounger
    Join Date
    Jun 2002
    Location
    Anderson, Indiana, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameters query subform using date greater than (Access 97)

    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
    Attached Images Attached Images

  12. #12
    Lounger
    Join Date
    Jun 2002
    Location
    Anderson, Indiana, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameters query subform using date greater than (Access 97)

    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
    Attached Images Attached Images

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Parameters query subform using date greater than (Access 97)

    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).

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Parameters query subform using date greater than (Access 97)

    In so far as I am able to understand your description, the selection criteria (from the subform) are stored in the GCDESIGN table.
    <UL><LI>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.
    <LI>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]!

  15. #15
    Lounger
    Join Date
    Jun 2002
    Location
    Anderson, Indiana, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameters query subform using date greater than (Access 97)

    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)

Page 1 of 2 12 LastLast

Posting Permissions

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