Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This isn't so much a problem.. but i'm just interested if anyone knows why its doing what, its doing.

    I have a query. this is the SQL


    SELECT TWORKAREAS2.MEMOID, DATA.[work area], DATA.competency, DATA.[Document ID], DATA.DOCISS, [fname] & " " & [sname] AS Expr1, Documents.[Document Name], DATA.taskid, DATA.Criteria, MEMOID.shiftcode, MEMOID.deptcode, Documents.[Effective Date]
    FROM Documents INNER JOIN (MEMOID INNER JOIN (TWORKAREAS2 INNER JOIN DATA ON TWORKAREAS2.Workarea = DATA.[work area]) ON MEMOID.memoid = TWORKAREAS2.MEMOID) ON (Documents.[Issue Number] = DATA.DOCISS) AND (Documents.[Reference Number] = DATA.[Document ID])
    ORDER BY TWORKAREAS2.MEMOID;

    If i run this query i get a query box:-

    Query32.Document ID

    However, there are no parameters in the query.
    If i put something in, (anything) the query will run.

    here's the strange bit.
    If i remove the fourth item in the query , [the Document ID] from table 'Data' and then add it back in again to the design, the query runs correctly without any prompt box.

    The only difference here, is that the [Document ID] field from the table 'Data' is last in the list on the design view, instead of 4th from the left.

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    [quote name='Steve_in_Kent' post='780985' date='22-Jun-2009 23:02']This isn't so much a problem.. but i'm just interested if anyone knows why its doing what, its doing.

    I have a query. this is the SQL


    SELECT TWORKAREAS2.MEMOID, DATA.[work area], DATA.competency, DATA.[Document ID], DATA.DOCISS, [fname] & " " & [sname] AS Expr1, Documents.[Document Name], DATA.taskid, DATA.Criteria, MEMOID.shiftcode, MEMOID.deptcode, Documents.[Effective Date]
    FROM Documents INNER JOIN (MEMOID INNER JOIN (TWORKAREAS2 INNER JOIN DATA ON TWORKAREAS2.Workarea = DATA.[work area]) ON MEMOID.memoid = TWORKAREAS2.MEMOID) ON (Documents.[Issue Number] = DATA.DOCISS) AND (Documents.[Reference Number] = DATA.[Document ID])
    ORDER BY TWORKAREAS2.MEMOID;

    If i run this query i get a query box:-

    Query32.Document ID

    However, there are no parameters in the query.
    If i put something in, (anything) the query will run.

    here's the strange bit.
    If i remove the fourth item in the query , [the Document ID] from table 'Data' and then add it back in again to the design, the query runs correctly without any prompt box.

    The only difference here, is that the [Document ID] field from the table 'Data' is last in the list on the design view, instead of 4th from the left.[/quote]

    When you get a parameter prompt for a field when you don't expect it, it usually means that you have miss-spelt the field name.
    This is consistent what you observe. When you add it back at the end, you put it back correctly.
    But what is strange is that the prompt refers to Query32 rather than Data. What is query32? Does that have anything to do with it?
    Regards
    John



  3. #3
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='johnhutchison' post='780994' date='22-Jun-2009 13:46']When you get a parameter prompt for a field when you don't expect it, it usually means that you have miss-spelt the field name.
    This is consistent what you observe. When you add it back at the end, you put it back correctly.
    But what is strange is that the prompt refers to Query32 rather than Data. What is query32? Does that have anything to do with it?[/quote]

    Thats the funny thing John.. There IS no Query32, anywhere.

    Also. this query is the base query, for lots of other queries, then a final report. If you run the report, everything works fine.

    If you run the Query that This query feeds.. it produces the data no problem

    Also.. there is absolutely no spelling errors etc. it was originally added just by double clicking a table. and i've checked the spelling.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    [quote name='Steve_in_Kent' post='781002' date='22-Jun-2009 07:32']Thats the funny thing John.. There IS no Query32, anywhere.

    Also. this query is the base query, for lots of other queries, then a final report. If you run the report, everything works fine.

    If you run the Query that This query feeds.. it produces the data no problem

    Also.. there is absolutely no spelling errors etc. it was originally added just by double clicking a table. and i've checked the spelling.[/quote]
    Check and see if you have something in the Filter By or Order By fields in the Query Properties - I've seen that cause this kind of behavior.
    Wendell

Posting Permissions

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