Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Crash on changing a query (XP)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Crash on changing a query (XP)

    Hard to tell. What happens if you take out the sort order on the calculated field (ORDER BY ... in the SQL) and try to save?

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Crash on changing a query (XP)

    I've seen XP do this in a number of different situations. It's very annoying, but I haven't found a way around it except to recreate the object it is choking on. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •