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

[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 targetFromVenueActivity.target
FROM venues INNER JOIN (Activities INNER JOIN targetFromVenueActivity ON Activities.ActivitiesID=targetFromVenueActivity.ac tivityID) ON venues.venue=targetFromVenueActivity.venueID
WHERE (((targetFromVenueActivity.venueID)=Forms![shift viewer]!venueID) And ((targetFromVenueActivity.activityID)=Forms![shift viewer]!activityID));

the query view of this works fine, but in the form i get #name? - what 2 do?

[willakawill]

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 targetFromVenueActivity.target
FROM venues INNER JOIN (Activities INNER JOIN targetFromVenueActivity ON Activities.ActivitiesID=targetFromVenueActivity.ac tivityID) ON venues.venue=targetFromVenueActivity.venueID
WHERE (((targetFromVenueActivity.venueID)=Forms![shift viewer]!venueID) And ((targetFromVenueActivity.activityID)=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?

[MMcCarthy]

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 targetFromVenueActivity.target
FROM venues INNER JOIN (Activities INNER JOIN targetFromVenueActivity ON Activities.ActivitiesID=targetFromVenueActivity.ac tivityID) ON venues.venue=targetFromVenueActivity.venueID
WHERE (((targetFromVenueActivity.venueID)=Forms![shift viewer]!venueID) And ((targetFromVenueActivity.activityID)=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

[anansi]

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? ...

[MMcCarthy]

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:

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

[anansi]

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:

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

You are a legend. thank you

[MMcCarthy]

You are a legend. thank you

No problem.