Select Syntax problem in a Query
Hi - i have a query setup that is held on a subform that displays all activities that have been logged by users. there is a field called 'leadofficer' that displays the name of the user who logged the activity. Based on prior help from forum users I thought it would be easy enough to set criteria whereby the form, when opened would only show activity where the values in the 'leadofficer' field would equal the value of the user who has logged in - where the value of the user who has logged in is held in a global variable called 'loginname' - but i was wrong! - ive been wrestling with the SQL syntax in the query but can only get it to work by having the query to prompt me for the name, which is no use because it allows an user to enter a different user name to their own.
here is the syntax ive been wrestling with - can anyone tell me how to put it right?
SELECT tblactivity.activityID, tblactivity.activitydate, tblactivity.companyname, tblactivity.contactname, tblactivity.project, tblactivity.activity, tblactivity.activityhours, tblactivity.activitynotes, tblactivity.leadofficer
FROM tblactivity
WHERE tblactivity.leadofficer = loginname;
i have also tried
WHERE ("tblactivity.leadofficer = '" & [loginname])
WHERE ("tblactivity.leadofficer = '" & [loginname] & "'");
these also prompt for loginname but unlike the first WHERE clause above they dont actually return the results that match the name entered, they just bring all of the activities up.
Thanks again,
Rhys
