#name? error in form using sql expression...

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • anansi
    New Member
    • Nov 2006
    • 15

    #1

    #name? error in form using sql expression...

    Hi

    I'm having what i would guess is a really trivial problem. i have searched the forum but nothing can solve my problem: here is my sql code that goes into a edit box control:

    SELECT targetFromVenue Activity.target
    FROM venues INNER JOIN (Activities INNER JOIN targetFromVenue Activity ON Activities.Acti vitiesID=target FromVenueActivi ty.activityID) ON venues.venue=ta rgetFromVenueAc tivity.venueID
    WHERE (((targetFromVe nueActivity.ven ueID)=Forms![shift viewer]!venueID) And ((targetFromVen ueActivity.acti vityID)=Forms![shift viewer]!activityID));

    the query view of this works fine, but in the form i get #name? - what 2 do?
  • willakawill
    Top Contributor
    • Oct 2006
    • 1646

    #2
    Originally posted by anansi
    Hi

    I'm having what i would guess is a really trivial problem. i have searched the forum but nothing can solve my problem: here is my sql code that goes into a edit box control:

    SELECT targetFromVenue Activity.target
    FROM venues INNER JOIN (Activities INNER JOIN targetFromVenue Activity ON Activities.Acti vitiesID=target FromVenueActivi ty.activityID) ON venues.venue=ta rgetFromVenueAc tivity.venueID
    WHERE (((targetFromVe nueActivity.ven ueID)=Forms![shift viewer]!venueID) And ((targetFromVen ueActivity.acti vityID)=Forms![shift viewer]!activityID));

    the query view of this works fine, but in the form i get #name? - what 2 do?
    Hi. There are a couple of things about this that stand out. What is most important is this;
    "the query view of this works fine"
    Have you pasted this syntax from a query view and added in the form fields?
    Have you put this expression into an edit box control and then use it to submit to access?

    Comment

    • MMcCarthy
      Recognized Expert MVP
      • Aug 2006
      • 14387

      #3
      Originally posted by anansi
      Hi

      I'm having what i would guess is a really trivial problem. i have searched the forum but nothing can solve my problem: here is my sql code that goes into a edit box control:

      SELECT targetFromVenue Activity.target
      FROM venues INNER JOIN (Activities INNER JOIN targetFromVenue Activity ON Activities.Acti vitiesID=target FromVenueActivi ty.activityID) ON venues.venue=ta rgetFromVenueAc tivity.venueID
      WHERE (((targetFromVe nueActivity.ven ueID)=Forms![shift viewer]!venueID) And ((targetFromVen ueActivity.acti vityID)=Forms![shift viewer]!activityID));

      the query view of this works fine, but in the form i get #name? - what 2 do?
      Are you trying to put this query into the control source of a textbox?

      If so, is 'shift viewer' the form that the textbox is on?

      Mary

      Comment

      • anansi
        New Member
        • Nov 2006
        • 15

        #4
        Originally posted by willakawill
        Have you pasted this syntax from a query view and added in the form fields?
        Have you put this expression into an edit box control and then use it to submit to access?
        Yes, I have a form called Shift Viewer containing a control called Text42. I created a separate query that returns the value (a single value and a single record).
        When looking at this query in the query's datasheet view (access 2007) it works, the result is not #name?. So looking at this query in its "SQL view" showed the SQL statement for it. I copied and pasted this into Text42's control

        but the control shows #name? ...

        Comment

        • MMcCarthy
          Recognized Expert MVP
          • Aug 2006
          • 14387

          #5
          Originally posted by anansi
          Yes, I have a form called Shift Viewer containing a control called Text42. I created a separate query that returns the value (a single value and a single record).
          You can't use a query in a control source in this way. You will need to replace it with a DLookup function as follows:

          Code:
          =DLookup("[target]","targetFromVenueActivity","[venueID]=" & [venueID] & " And [activityID]=" & [activityID])

          Comment

          • anansi
            New Member
            • Nov 2006
            • 15

            #6
            Originally posted by mmccarthy
            You can't use a query in a control source in this way. You will need to replace it with a DLookup function as follows:

            Code:
            =DLookup("[target]","targetFromVenueActivity","[venueID]=" & [venueID] & " And [activityID]=" & [activityID])

            You are a legend. thank you

            Comment

            • MMcCarthy
              Recognized Expert MVP
              • Aug 2006
              • 14387

              #7
              Originally posted by anansi
              You are a legend. thank you
              No problem.

              Comment

              Working...