Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query based on query based on query based on form (2003)

    I have a form that has 3 text fields with dates in them.

    I have a query that uses those dates to calculate the age of something. Call it query1.

    I have a query that is based on query1 that gets the count of distinct item numbers & some other fields. Call it query 2.

    I have another query, based on query 2, that sums the $$ fields. Call it query3.

    Queries 1 & 2 work just fine, but query 3 gives me an error that the jet engine does not recognize the items from my form. ?????

    They are not referenced in query 3, only query1, which works.

    Any pointers?

    Thanks!
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Query based on query based on query based on form (2003)

    Use the Eval function in query1 when referencing fields on a form. So in your query, instead of something like this (I'm guessing at form and control names):

    Forms!frmSomething!txtDate1

    Use this instead:

    Eval("Forms!frmSomething!txtDate1")

    Notice that you encase the entire reference string in quotes.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: Query based on query based on query based on form (2003)

    Another possibility is to declare the references to the form text boxes as parameters for the each of the queries:
    - In design view, select Query | Parameters...
    - Enter a reference as used in the first query, e.g. [Forms]![NameOfForm]![NameOfTextbox], and the corresponding data type.
    - Repeat for the other text boxes.
    - Click OK.

  4. #4
    3 Star Lounger
    Join Date
    Sep 2001
    Location
    Stuck at work..., Missouri, USA
    Posts
    248
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query based on query based on query based on form (2003)

    Hans, I tried your suggestion. Now when I try to open the query in VBA, I get the following error:
    Run-time error 3061
    Too Few Parameters. Expected 0.

    This is the VBA in question:
    Set rstDetails = db.OpenRecordset("SELECT * FROM [qryReportA-PO_Count] WHERE BU = '" & rstBU!BU & "'", dbOpenForwardOnly)
    <font face="Comic Sans MS">Morgan Erickson</font face=comic>
    morgan.erickson@sprint.com
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18>-From <img src=/S/flags/Colorado.gif border=0 alt=Colorado width=30 height=18> but living in <img src=/S/flags/Missouri.gif border=0 alt=Missouri width=30 height=18>...and working in Kansas.

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

    Re: Query based on query based on query based on form (2003)

    I'd try Mark's suggestion first. If that doesn't help, could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

Posting Permissions

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