Lalit Mohan,
Thanks so much for taking the time to respond to my question. I tried replacing all of the asterisks with % and added in some debug print statements....and I got the following:
Code:
StrSQL:-
INSERT INTO PendingSamples
SELECT MYSELECT.% FROM
(SELECT SWITCH([Hold_Reason] LIKE '%X REPREP%', 'Reprep',
[Hold_Reason] LIKE '%X RAPIDFIRE%', 'RapidFire',
[Hold_Reason] LIKE '%X D/L ISOMER SEND OUT%', 'DLIsomer',
[Hold_Reason] LIKE '%X AMBIEN SEND OUT%', 'Quest_Sendout',
[Hold_Reason] LIKE '%X NEEDS DATA%', 'Needs_Data',
[Hold_Reason] LIKE '%X NEEDS SCREENING%', 'Needs_Screening',
[Hold_Reason] LIKE '%X TEST ORDER CONFIRMATION%', 'TO_Conf',
[Hold_Reason] LIKE '%X CLERICAL REVIEW CONFIRMATION%', 'Clerical_Review',
[Hold_Reason] LIKE '%X COMPLIANCE%', 'Compliance',
[Hold_Reason] LIKE '%X NO PAF%', 'NO_PAF',
[Hold_Reason] LIKE '%X POSITVE FOR ILLEGAL%', 'ILL_NARC',
[Pathologist] IS NULL, 'Other_NR',
TRUE, 'Other_NS'
) AS Type,
[MR_Num], [Chart_Number], [Clinic_Location], [Last_Name], [First_Name], [Date_Received], [Sales_Rep], [Hold_Reason],
[Pending_Days], [Pathologist]
FROM SamplesReceived
WHERE ([Clinic_Location] <> 'Central Perch' AND [Clinic_Location] IS NOT NULL) AND
([Chart_Number] LIKE 'UC14-%' OR [Chart_Number] LIKE 'OF14-%' OR [Chart_Number] LIKE 'RF14-%' OR [Chart_Number] LIKE 'SA14-%') AND [Pending_Days] <> 0) AS MYSELECT
LEFT JOIN PendingSamples ON MYSELECT.[Chart_Number] = PendingSamples.[Chart_Number]
WHERE (((PendingSamples.[Chart_Number]) IS NULL));
Error Number:- -2147217900
Error Description:- Invalid use of '.', '!', or '()'. in query expression 'MYSELECT.'.
June7,
I tried what you mentioned and ended up with no error but the update does not happen:
Code:
StrSQL:-
INSERT INTO PendingSamples
SELECT MYSELECT.* FROM
(SELECT SWITCH([Hold_Reason] LIKE '*X REPREP*', 'Reprep',
[Hold_Reason] LIKE '*X RAPIDFIRE*', 'RapidFire',
[Hold_Reason] LIKE '*X D/L ISOMER SEND OUT*', 'DLIsomer',
[Hold_Reason] LIKE '*X AMBIEN SEND OUT*', 'Quest_Sendout',
[Hold_Reason] LIKE '*X NEEDS DATA*', 'Needs_Data',
[Hold_Reason] LIKE '*X NEEDS SCREENING*', 'Needs_Screening',
[Hold_Reason] LIKE '*X TEST ORDER CONFIRMATION*', 'TO_Conf',
[Hold_Reason] LIKE '*X CLERICAL REVIEW CONFIRMATION*', 'Clerical_Review',
[Hold_Reason] LIKE '*X COMPLIANCE*', 'Compliance',
[Hold_Reason] LIKE '*X NO PAF*', 'NO_PAF',
[Hold_Reason] LIKE '*X POSITVE FOR ILLEGAL*', 'ILL_NARC',
[Pathologist] IS NULL, 'Other_NR',
TRUE, 'Other_NS'
) AS [Type],
[MR_Num], [Chart_Number], [Clinic_Location], [Last_Name], [First_Name], [Date_Received], [Sales_Rep], [Hold_Reason],
[Pending_Days], [Pathologist]
FROM SamplesReceived
WHERE ([Clinic_Location] <> 'Central Perch' AND [Clinic_Location] IS NOT NULL) AND
([Chart_Number] LIKE 'UC14-*' OR [Chart_Number] LIKE 'OF14-*' OR [Chart_Number] LIKE 'RF14-*' OR [Chart_Number] LIKE 'SA14-*') AND [Pending_Days] <> 0) AS MYSELECT
LEFT JOIN PendingSamples ON MYSELECT.[Chart_Number] = PendingSamples.[Chart_Number]
WHERE (((PendingSamples.[Chart_Number]) IS NULL));
When I run the same statement manually in SQL view it updates 3934 records.
The fiel type is .ACCDB
Thanks for the assistance. It doesn't seem like I can get this to work using cn.execute