Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2003
    Posts
    7

    Question Unanswered: how to use a query in a form

    Now I created a query named qry1 and I want to use it's result in my new form.
    I use Controle Source to choose this query like this

    = [qry1]![Sum Of Saving/Costing]

    but I got the #Name? Problem. What does this mean? And I always get this #Name? or #Error? Problems when I try to use some data from other table, query or form. Any body can tell me how to do it? Thank you very much

  2. #2
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369

    Re: how to use a query in a form

    Originally posted by harry_hurry
    Now I created a query named qry1 and I want to use it's result in my new form.
    I use Controle Source to choose this query like this

    = [qry1]![Sum Of Saving/Costing]

    but I got the #Name? Problem. What does this mean? And I always get this #Name? or #Error? Problems when I try to use some data from other table, query or form. Any body can tell me how to do it? Thank you very much
    First, do you get the desired result in the query datasheet view? I believe that the [Sum Of Saving/Costing] is not a single field/column in your query? Can you enter the query in SQL mode and post the syntax here?

    Does it help you if you select the query in the database view and then click on the AutoForm button and study the result?

    If you want to do a calculation on two fields in the underlying table, you can either enter the calculation with proper field references as a new psudo-field in the query design pane, or in the property of unbound text fields in the form.

    I can give you a couple of examples if you enter the query syntax here, then we can avoid some common misunderstandings.

  3. #3
    Join Date
    Jun 2003
    Posts
    7

    this is my qurey

    SELECT DISTINCTROW Format$([Circulation].[Date],"mmmm yyyy") AS [Date By Month], Circulation.AccountID, Sum(Circulation.Deposit) AS [Sum Of Deposit]
    FROM Circulation
    GROUP BY Format$([Circulation].[Date],"mmmm yyyy"), Circulation.AccountID, Year([Circulation].[Date])*12+DatePart("m",[Circulation].[Date])-1
    HAVING (((Format$([Circulation].[Date],"mmmm yyyy"))<#12/1/2003# And (Format$([Circulation].[Date],"mmmm yyyy"))>=#11/1/2003#) AND ((Circulation.AccountID)="MM-1"));


    Yeah, you are right. this query can't return any result. Do you know what 's wrong with it? I know the problem is about the criteria :
    <#12/1/2003# And >=#11/1/2003#

    but I don't know how to correct it.

    Thank you for your help.

  4. #4
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369

    Re: this is my qurey

    Originally posted by harry_hurry
    HAVING (((Format$([Circulation].[Date],"mmmm yyyy"))<#12/1/2003# And (Format$([Circulation].[Date],"mmmm yyyy"))>=#11/1/2003#) AND ((Circulation.AccountID)="MM-1"));

    Yeah, you are right. this query can't return any result.

    problem is about the criteria :
    <#12/1/2003# And >=#11/1/2003#
    hm. I have not studied you code very carefully, but how do you know that it is from the criteria part? Any error message? I am not sure, but could it be that you need to specify the dates in the format you are demanding? Other than that, it could be that there is a logical mismatch between the grouping/criteria/table-structure that produces zero results if you do not receive any error message, but only an empty datasheet..

  5. #5
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369

    Re: how to use a query in a form

    Originally posted by kedaniel
    I can give you a couple of examples if you enter the query syntax here, then we can avoid some common misunderstandings.
    From watching your code , for example
    Sum(Circulation.Deposit) AS [Sum Of Deposit]
    I think you do not need much examples on "pseudo"-fields/columns, as you are generating them already?

    (Example anyway:
    Sum(Circulation.Deposit + Circulation.ExtraFee) AS [Sum Of DepositAndFees]

    For the #name issue:
    = [qry1]![Sum Of Saving/Costing]

    could possibly be:
    = [qry1]![Sum Of Saving]/[qry1]![Costing]
    in order to reference two separate fields to calculate?
    Not sure aboiut this one, thought.

Posting Permissions

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