I have two problems that have stumped me. You guys are pretty smart, please comment...
SELECT tblVolunteers.VolID, [LName] & ', ' & [FName] AS Name, IIf([MailTo]=1,[City],[MCity]) AS CurrentCity, IIf([MailTo]=1,[tblStates].[Abrv],[tblStates_1].[Abrv]) AS CurrentState
FROM (tblVolunteers LEFT JOIN tblStates ON tblVolunteers.StateID = tblStates.StateID) LEFT JOIN tblStates AS tblStates_1 ON tblVolunteers.MStateID = tblStates_1.StateID
WHERE (((tblVolunteers.TypeID)=2) AND ((tblVolunteers.Archive)=0))
ORDER BY [LName] & ', ' & [FName];
This is the SQL for the rowsource of a listbox. This listbox has four columns, the first (VolID) is hidden, the second (Name), the third is city and the fourth is state. Some of our volunteers have a primary address and a secondary address. The MailTo field determines which address to use. When I do the datasheet view this query works perfectly...it lists the volunteers by "LName, FName" and lists their city and state according to their MailTo field. However, when I click on the listbox, it displays the volunteer's primary address even if their MailTo address is their secondary address. What's the deal? Any suggestions?
SELECT tblVolunteers.FName, tblVolunteers.LName, [Lname] & ", " & [Fname] AS Name
Similar problem as above. Certain records in our tblVolunteers are not displayed correctly when queried. When I view the datasheet view, on certain records the FName is displayed correctly, the LName is displayed correctly but the Name (LName, FName) is not being displayed correctly. Sometimes it will display only the LName without the ", FName ". Any ideas?
Sorry if this is confusing! I look forward to your comments!