I have a query which is quite complex – several tables and queries. It has been working fine but i wanted to display “unknown” as a litteral when no Strategic Health Authority was known, it’s relatively simple in code terms i.e. chaneg the existing field to use:
StHA: IIf([NHS Organisation].[NHSO_StHA]=-1 And [QRY Strategic Health Authorities && WDC Champions].[NHSO_Id] Is Null,[NHS Organisation].[NHSO_Name],IIf([QRY Strategic Health Authorities && WDC Champions].[NHSO_Id] Is Null,”Unknown”,[QRY Strategic Health Authorities && WDC Champions].[Organisation]))
but this is embedded in this SQL:
SELECT [QRY Strategic Health Authorities && WDC Champions].[WDC Champion], [QRY Strategic Health Authorities && WDC Champions].[Work Phone] AS [WDC Phone], [QRY Strategic Health Authorities && WDC Champions].Email AS [WDC Email], IIf([NHS Organisation].NHSO_StHA=-1 And [QRY Strategic Health Authorities && WDC Champions].NHSO_Id Is Null,[NHS Organisation].NHSO_Name,[QRY Strategic Health Authorities && WDC Champions].Organisation) AS StHA, [NHS Organisation].NHSO_JoiningRequestDate, [NHS Organisation].NHSO_PreferredTakeOnDate, [NHS Organisation].NHSO_DateReadyToUseService, [NHS Organisation TakeOn Status].OTS_Name, [NHS Organisation TakeOn Priority].OTP_Name, [NHS Organisation].NHSO_id AS [Organisation Id], [NHS Organisation].NHSO_Name AS Organisation, [QRY Pilot Primary Contacts_1].[Primary Contact Name], [QRY Pilot Primary Contacts_1].[Primary Contact Title], [QRY Pilot Primary Contacts_1].[Primary Contact Email], [QRY Pilot Primary Contacts_1].[Primary Contact Phone], [NHS Organisation].NHSO_PilotSite, [NHS Organisation].NHSO_NotSelectedPilotSite, [NHS Organisation].NHSO_WithdrewFromPilot, [NHS Organisation].NHSO_SharedService, [NHS Organisation_1].NHSO_id AS [Sharee Id], [NHS Organisation_1].NHSO_Name AS Sharee, [NHS Organisation].NHSO_KoQuestionnaireRecd, [NHS Organisation].NHSO_Id_UsesSharedService, [QRY Pilot HR Directors].[HRD Name], [QRY Pilot HR Directors].[HRD Title], [QRY Pilot HR Directors].[HRD Email], [QRY Pilot HR Directors].[HRD Phone]
FROM [QRY Pilot HR Directors] RIGHT JOIN (((([NHS Organisation] AS [NHS Organisation_1] RIGHT JOIN ([NHS Organisation] LEFT JOIN [QRY Pilot Primary Contacts] AS [QRY Pilot Primary Contacts_1] ON [NHS Organisation].NHSO_id = [QRY Pilot Primary Contacts_1].NHSO_id) ON [NHS Organisation_1].NHSO_Id_UsesSharedService = [NHS Organisation].NHSO_id) LEFT JOIN [NHS Organisation TakeOn Status] ON [NHS Organisation].OTS_Id = [NHS Organisation TakeOn Status].OTS_Id) LEFT JOIN [NHS Organisation TakeOn Priority] ON [NHS Organisation].OTP_Id = [NHS Organisation TakeOn Priority].OTP_Id) LEFT JOIN [QRY Strategic Health Authorities && WDC Champions] ON [NHS Organisation].NHSO_Id_StHA = [QRY Strategic Health Authorities && WDC Champions].NHSO_id) ON [QRY Pilot HR Directors].NHSO_id = [NHS Organisation].NHSO_id
WHERE ((([NHS Organisation].OTS_Id)=1 Or ([NHS Organisation].OTS_Id)=2 Or ([NHS Organisation].OTS_Id)=3 Or ([NHS Organisation].OTS_Id)=4 Or ([NHS Organisation].OTS_Id)=7 Or ([NHS Organisation].OTS_Id)=9))
ORDER BY IIf([NHS Organisation].NHSO_StHA=-1 And [QRY Strategic Health Authorities && WDC Champions].NHSO_Id Is Null,[NHS Organisation].NHSO_Name,[QRY Strategic Health Authorities && WDC Champions].Organisation), [NHS Organisation].NHSO_Name;
What happen is, it works if I show the datasheet, but if I save the query Access closes without warning – not even th eirritating but polite XP message apologising for the inconvenience.
I have compacted and repaired the database and I have tried copying the query and editing the copy. i haven’t tried to completely rebuild it from scratch.
Any ideas why this should ahppen?
many thanks…………………. liz
