none
Another Query Question RRS feed

  • Question

  • I am experiencing something in a query that I cannot explain.

    I have the following query:

    SELECT USER_TABLE.FIRST_NAME, USER_TABLE.LAST_NAME, USER_TABLE.SUPERVISOR, USER_TABLE.CLOCK_NUMBER
    FROM USER_TABLE
    WHERE (((USER_TABLE.CLOCK_NUMBER)=[Forms]![5-FormToCheckToSeeIfDocumentIsStillRequired]![CLOCK_NUMBER]));

    In immediate window I get this result.

    ? [Forms]![5-FormToCheckToSeeIfDocumentIsStillRequired]![CLOCK_NUMBER]
     3596

    However, the query does not yield any result.

    If I change the query to:

    SELECT USER_TABLE.FIRST_NAME, USER_TABLE.LAST_NAME, USER_TABLE.SUPERVISOR, USER_TABLE.CLOCK_NUMBER
    FROM USER_TABLE
    WHERE (((USER_TABLE.CLOCK_NUMBER)=3596));

    It works fine.  What am I missing?

    Thursday, December 17, 2015 7:44 PM

Answers

  • Thanks so much for your input.

    I am using Access 2013. I ran the query from form view with a break point set on the call for the query.

    In retrospect, I seem to have made a big mistake.  It seems as if, when you run the queries while in debug mode, they don't work correctly.  I set a break point and then while in debug mode tried to run the query below.

    SELECT USER_TABLE.FIRST_NAME, USER_TABLE.LAST_NAME, USER_TABLE.SUPERVISOR, USER_TABLE.CLOCK_NUMBER
    FROM USER_TABLE
    WHERE (((USER_TABLE.CLOCK_NUMBER)=[Forms]![5-FormToCheckToSeeIfDocumentIsStillRequired]![CLOCK_NUMBER]));

    It didn't yield the results I expected as I indicated.

    However, after doing lots of other tests, the query really is OK.  It would appear that while in debug mode, I cannot expect that query to perform as expected. (Query was run while in debug mode.) It seems as if in debut mode, the reference to "[Forms]![5-FormToCheckToSeeIfDocumentIsStillRequired]![CLOCK_NUMBER]));" does not work.

    If I run the query by stopping the code at the point of failure when in debug mode and then running the query, it works as expected. 

    Sorry about the wasted post. 

    • Marked as answer by tkosel Friday, December 18, 2015 4:01 AM
    Friday, December 18, 2015 4:00 AM

All replies

  • Hi tkosel,

    What is your Access version, Access 2010 or Access 2013? Where did you run your query, from query design view? I made a test with your description under both Access 2010 and Access 2013, it worked correctly at my side.

    Have you changed the Form view to Design view while you run the query? I suggest you run the query first, and then if it query nothing, enter the statement in immediate window to check the value.

    For a workaround, I suggest you use query with “[Forms]![5-FormToCheckToSeeIfDocumentIsStillRequired]![CLOCK_NUMBER]” as parameter.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, December 18, 2015 2:40 AM
  • Thanks so much for your input.

    I am using Access 2013. I ran the query from form view with a break point set on the call for the query.

    In retrospect, I seem to have made a big mistake.  It seems as if, when you run the queries while in debug mode, they don't work correctly.  I set a break point and then while in debug mode tried to run the query below.

    SELECT USER_TABLE.FIRST_NAME, USER_TABLE.LAST_NAME, USER_TABLE.SUPERVISOR, USER_TABLE.CLOCK_NUMBER
    FROM USER_TABLE
    WHERE (((USER_TABLE.CLOCK_NUMBER)=[Forms]![5-FormToCheckToSeeIfDocumentIsStillRequired]![CLOCK_NUMBER]));

    It didn't yield the results I expected as I indicated.

    However, after doing lots of other tests, the query really is OK.  It would appear that while in debug mode, I cannot expect that query to perform as expected. (Query was run while in debug mode.) It seems as if in debut mode, the reference to "[Forms]![5-FormToCheckToSeeIfDocumentIsStillRequired]![CLOCK_NUMBER]));" does not work.

    If I run the query by stopping the code at the point of failure when in debug mode and then running the query, it works as expected. 

    Sorry about the wasted post. 

    • Marked as answer by tkosel Friday, December 18, 2015 4:01 AM
    Friday, December 18, 2015 4:00 AM