the table is already opened exclusively by another user (1 Viewer)

[Thicko]

So I have a create table query in vba and there's a problem with the RemoveVol line, (I've only shown this part as substantial code). In the event that the iif goes down the iMaxWithReconstitutionMinLimit function (custom function) the table fails to fill in the RemoveVol column and subsequent calculated fields.

If I run this as an access query I get "the table is already opened exclusively by another user ..." I know that isn't the case as I'm the only user of this version.

I can however create another table with a different name and then Update the fields in the originally intended table which feels a bit of a substandard workaround.

I have wondered if it was an issue to do with the syntax round [TrialStockSupplied] and [EarlyAccessMedicineSupplied] which are Boolean fields.

SQLCreateatblChemoWSAll = SQLCreateatblChemoWSAll + "IIf([ChemoRoute] Like '*Infusion',IIf(Not IsNull([BandRemoveVolume]),[BandRemoveVolume],IIf(DLookUp('MaxFinalVolume','tblDrugWeightCost','[VolumeWithOverage]=' & [CalcFluidVolume])<(([Dose]/[DrugConcentrationinMGperML1])+[CalcFluidVolume]) Or [RemoveOverageAndDrugVolume]=-1, iMaxWithReconstitutionMinLimit(Nz(DSum('CalcReconstitutionVolFromInfusionML','qryChemoWSAllBatch1','[BatchNumber]=''' & [BatchNumber] & ''' And [TrialStock]=' & [TrialStockSupplied] & ' And [EarlyAccessMedicine]=' & [EarlyAccessMedicineSupplied] & ''),0),(Nz([WFI10ml],0)+Nz([WFI10ml1],0)+Nz([WFI100ml],0)+Nz([WFI100ml1],0)+Nz([NaCl10ml],0)+Nz([NaCl10ml1],0)),([Overage]+([Dose]/[DrugConcentrationinMGperML1])),Nz(DSum('CalcReconstitutionVolFromInfusionML','qryChemoWSAllBatch1','[BatchNumber]=''' & [BatchNumber] & ''' And [TrialStock]=' & [TrialStockSupplied] & ' And [EarlyAccessMedicine]=' & [EarlyAccessMedicineSupplied] & ''),0)),0)),0) AS RemoveVol, "

Any insight appreciated.

 

[theDBguy]

Even if you're the only user, you will get that error if you have two processes modifying the same table at the same time.

 

[Ranman256]

usu a form (or forms) will both have the table open more than once.

 

[The_Doc_Man]

The way you have presented your code is useless in determining whether you have a self-conflict because you don't show us the front part of your query. We can't see a FROM clause, which would be crucial in determining such a conflict. If it is at all possible, you need to simplify that query massively and perhaps use update methods to divide-and-conquer the parts of that beast a few fields at a time.

You say this is a CREATE TABLE query. The thing to look for is whether you have referenced one or more fields in that table in the FROM or WHERE clauses while trying to create another record in that same table. The "exclusive lock" is on the table being created and that exclusivity might prevent references to the same table.

 

[Pat Hartman]

It is poor practice to use make table queries in either the FE or the BE.

Sometimes we have to download data that originates in a different application to which we cannot make a direct connect. So, maybe, overnight, you need to download yesterday's transactions from your ERP system. This data doesn't belong to your application so it really should not be in the BE and it should also not be in the FE. A good way to handle this is to have a batch process that runs following the download that imports the data into a template copy of a separate BE that holds ONLY the downloaded data. The batch process creates a new copy of the temp BE every night which takes care of the bloat issue.

Sometimes, you might think you need to make a temp table to be used as the first step of some larger process. This is rarely necessary and most of the time, you can skip the make table step entirely and just use a select query that selects the base query.

If you re using make table queries because you are keeping separate tables by year or by month or by something else, you need to hang up your spreadsheet hat and find a relational database hat so you can rethink the whole process.

 

[Thicko]

My initial thought was to look for atblChemoWSAll (Output table) being used as the source and output, especially when it came to the custom function but no crossover.

I don't like create table queries and only comes as a last resort. In this case the Data is put into a local table, in this case for 3 reasons:

1. When running as a SELECT query the performance was severely degraded when printing multiple reports, essentially unusable. This way there's one initial slow query to create the table then after that all reports fly out.
2. As this is the final step in outputting reports there will be no crossover if two individuals want to print different reports at the same time from different PC's.
3. Had rare incidents of tables being deleted so by using create table rather than delete & append avoids that issue, as table will always be there.

I'm not IT trained so appreciate finding bad practice in the code will be like shooting fish in a barrel.

SELECT tblChemoRecord.BatchNumber, tblChemoPreparation.ChemoPreparation, tblChemoRecord.Preparation, tblChemoPreparation.ChemoRoute, Nz(DLookUp('Product','tblStockLive','[BatchNumber]=''' & [StockBatchNumberInfusionFluid1] & ''''),[tblChemoPreparation].[Fluid]) AS Fluid, tblChemoRecord.Consultant, qryChemoPreparationExpiry3.ExpiryTimeText AS ExpiryTime, [ExpiryTimeReference] & ' Accessed ' & [ExpiryTimeDateAccessed] AS ExpiryReference, tblChemoPreparation.WarningInstruction1, tblChemoPreparation.WarningInstruction2, IIf([PreCompoundChemoStock]=-1 And [Manufacturer1]=SiteLocationForPCname(),LocalPrecompoundedProductStorage([StockBatchNumber1],[Preparation],ChemoRoute),[qryChemoPreparationExpiry3].[ExpiryStorageCondition]) AS WarningInstruction3a, tblChemoPreparation.WarningInstruction4, tblChemoPreparation.Administer, IIf(ChemoRoute Like '*Cont. Inf*',Replace([Administer],' xx ',' ' & [ContinuousInfusionTimeHrs] & ' '),[Administer]) AS AdministerRevised, tblChemoPreparation.DrugConcentrationinMGperML, tblChemoPreparation.DrugConcentrationinMGperML AS DrugConcentrationinMGperML1, IIf([LicensedChemoStock]=-1,[Dose]/DLookUp('LicensedReadyToUseVolumeML','tblDrugWeightCost','[DrugNameVial]=''' & ChemoPreparation & ' ' & [Dose] & 'mg' & '''And [LicensedReadyToUsePreparation]=1'),[DrugConcentrationinMGperML]) AS DrugConcentrationinMGperMLWSLicensed, tblChemoPreparation.DrugUnit, tblChemoPreparation.FluidVolumeML, IIf([LicensedChemoStock]=-1,0,IIf(UseEVABag(ChemoPreparation,ChemoRoute,[Dose])>0,0,InfusionFluidVolume(Nz([NaCl50ml]),Nz([NaCl100ml]),Nz([NaCl250ml]),Nz([NaCl500ml]),Nz([NaCl1000ml]),Nz([Glucose100ml]),Nz([Glucose250ml]),Nz([Glucose500ml]),Nz([Glucose1000ml]),BatchSize,[IgnoreOverage]))) AS CalcFluidVolume, tblChemoPreparation.AdditionalData, tblChemoPreparation.Method1, tblChemoPreparation.Method2, tblChemoPreparation.Method3, tblChemoPreparation.Method4, tblChemoPreparation.Method5, tblChemoPreparation.Method6, tblChemoPreparation.LabelCode, tblChemoPreparation.AdditionalLabelCode, tblChemoPreparation.AdditionalElectrolyte, tblChemoPreparation.AdditionalElectrolyteConcMMOLperML, [AdditionMMOL]/[AdditionalElectrolyteConcMMOLperML] AS AdditionVolume, tblChemoRecord.Date, tblChemoRecord.TimeOfTreatment, tblChemoRecord.PatientNumber, tblChemoRecord.Route, tblChemoRecord.Ward, tblChemoRecord.BatchSize, tblChemoRecord.Dose, tblChemoPreparation.TotalFixedFinalVolume, tblChemoPreparation.ContiniousInfusionRate, tblChemoRecord.ContinuousInfusionTimeHrs, ([Dose]/[DrugConcentrationinMGperML1])+0.00005 AS DrugDose1, IIf([DrugDose1]>10,Round([DrugDose1],1),Round([DrugDose1],2)) AS DrugDose, IIf([Route]='Injection' And [DoseVolume1]>=50,50*[DrugConcentrationinMGperML],[Dose]) AS DrugDose2, IIf([Route]='Injection' And [DoseVolume1]>50,[Dose]-[DrugDose2],'') AS DrugDose3, IIf([Route]='Injection' And [DrugDose]>100,'50' & ' + ' & '50' & ' + ' & [DrugDose]-100,IIf([Route]='Injection' And [DrugDose]>50,'50' & ' + ' & [DrugDose]-50,[DrugDose])) AS SplitVolume, [CalcFluidVolume]-(50*Int([CalcFluidVolume]/50)) AS Overage, (IIf([PreCompoundChemoStock]=-1,IIf(IsNull(DLookUp('AbsoluteFixedVolume','tblPreCompoundedProducts','[Drug]=''' & ChemoPreparation & ''' And [Manufacturer]=''' & [Manufacturer1] & ''' And [Route]=''' & ChemoRoute & ''' And [Dose]=' & [Dose])),([Dose]/[DrugConcentrationinMGperML1])+(([CalcFluidVolume]+Nz([AdditionVolume]))-[RemoveVol]),DLookUp('AbsoluteFixedVolume','tblPreCompoundedProducts','[Drug]=''' & ChemoPreparation & ''' And [Manufacturer]=''' & [Manufacturer1] & ''' And [Route]=''' & ChemoRoute & ''' And [Dose]=' & [Dose])),([Dose]/[DrugConcentrationinMGperML1])+(([CalcFluidVolume]+Nz([AdditionVolume]))-[RemoveVol]))+0.00005) AS DoseVolume,

 

[Thicko]

IIf(Not IsNull([TotalFixedFinalVolume]),[TotalFixedFinalVolume],IIf([Route] Like '*Cont. Inf*',([ContiniousInfusionRate]*[ContinuousInfusionTimeHrs])+Nz(DLookUp('PumpResidualLineVolume','atblStockLookupLocal2','[PumpFlowRate]=' & RemoveAlphasNotDecimalPoint([Route]) & ''),0),IIf([Route]='Infusion',Round([DoseVolume],0),IIf([DoseVolume]>10,Round([DoseVolume],1),Round([DoseVolume],2))))) AS DoseVolume1, IIf([LicensedChemoStock]=-1,DLookUp('LicensedReadyToUseVolumeML','tblDrugWeightCost','[DrugNameVial]=''' & [Preparation] & ' ' & [Dose] & 'mg' & ''' And [LicensedReadyToUsePreparation]=1'),IIf([Route]='Injection' And [DoseVolume1]>=50,50,[DoseVolume1])) AS DoseVolume2, IIf([LicensedChemoStock]=-1,DLookUp('LicensedReadyToUseVolumeML','tblDrugWeightCost','[DrugNameVial]=''' & [Preparation] & ' ' & [Dose] & 'mg' & ''' And [LicensedReadyToUsePreparation]=1'),IIf([Route]='Injection' And [DoseVolume1]>=50,[DoseVolume1],[DoseVolume2])) AS DoseVolume3, IIf([ChemoRoute] Like '*Infusion',IIf(Not IsNull([BandRemoveVolume]),[BandRemoveVolume],IIf(DLookUp('MaxFinalVolume','tblDrugWeightCost','[VolumeWithOverage]=' & [CalcFluidVolume])<(([Dose]/[DrugConcentrationinMGperML1])+[CalcFluidVolume]) Or [RemoveOverageAndDrugVolume]=-1,iMaxWithReconstitutionMinLimit(Nz(DSum('CalcReconstitutionVolFromInfusionML','qryChemoWSAllBatch1','[BatchNumber]=''' & [BatchNumber] & ''' And [TrialStock]=' & [TrialStockSupplied] & ' And [EarlyAccessMedicine]=' & [EarlyAccessMedicineSupplied]),0),(Nz([WFI10ml],0)+Nz([WFI10ml1],0)+Nz([WFI100ml],0)+Nz([WFI100ml1],0)+Nz([NaCl10ml],0)+Nz([NaCl10ml1],0)),([Overage]+([Dose]/[DrugConcentrationinMGperML1])),Nz(DSum('CalcReconstitutionVolFromInfusionML','qryChemoWSAllBatch1','[BatchNumber]=''' & [BatchNumber] & ''' And [TrialStock]=' & [TrialStockSupplied] & ' And [EarlyAccessMedicine]=' & [EarlyAccessMedicineSupplied]),0))))) AS RemoveVol, tblChemoRecord.Patient, tblDrugList.WFI10ml, [WFI10ml]*BatchSize AS WFI10mlBS, tblDrugList.WFI10ml1, tblDrugList.WFI100ml, tblDrugList.WFI100ml1, tblDrugList.NaCl10ml, tblDrugList.NaCl10ml1, tblChemoPreparation.QCSignOffDate, 'MAW' & [ChemoPrepID] AS MDAWID, qryChemoPreparationExpiry3.ExpiryTimeMins AS ExpiryTimeM, Format([Date]) & [Forms].[frmChemoRecord].[DefaultStartTime] AS ExpiryTimeM1, IIf([PreCompoundChemoStock]=-1 Or [LicensedChemoStock]=-1,MinVal([StockExpiryDate1],[StockExpiryDate2],[StockExpiryDate3],[StockExpiryDate4]) & ' 23:59:00 ',DateAdd('n',[ExpiryTimeM],[ExpiryTimeM1])) AS ExpiryTimeM1PC, IIf([ExpiryTimeMins]=0,Format([StockExpiryDate1]) & Format([StockExpiryDate2]) & ' 23:59:00 ',IIf(CDate(Nz(MinVal([StockExpiryDate1],[StockExpiryDate2],[StockExpiryDate3],[StockExpiryDate4],[StockExpiryDate5],[StockExpiryDate6],[StockExpiryDate7],[StockExpiryDate8],[StockExpiryDate9],[StockExpiryDate10],[StockExpiryDate11],[StockExpiryDate12]),Format(DateAdd('n',[ExpiryTimeMins],[ExpiryTimeM1]),'Short Date')) & ' 23:59:00')<DateAdd('n',[ExpiryTimeMins],[ExpiryTimeM1]),CDate(MinVal([StockExpiryDate1],[StockExpiryDate2],[StockExpiryDate3],[StockExpiryDate4],[StockExpiryDate5],[StockExpiryDate6],[StockExpiryDate7],[StockExpiryDate8],[StockExpiryDate9],[StockExpiryDate10],[StockExpiryDate11],[StockExpiryDate12]) & ' 23:59:00'),DateAdd('n',[ExpiryTimeMins],[ExpiryTimeM1]))) AS ExpiryTimeM2, tblChemoRecord.MasterWorksheet, tblChemoPreparation.DrugDetail, IIf([Route] Like '*Injection*','for Intravenous Injection',IIf([Route] Like '*Infusion*','for Intravenous Infusion',IIf([Route]='Subcutaneous','for Subcutaneous Injection',IIf([Route]='I/M Injection','for Intramuscular Injection',IIf([Route]='Bladder WO','For Bladder Instillation','Sterile Solution'))))) AS LabelRoute, IIf([PreCompoundChemoStock]=-1 Or [LicensedChemoStock]=-1,Format([ExpiryTimeM1PC],'h:nn dd/mm/yyyy'),Format([ExpiryTimeM2],'h:nn dd/mm/yyyy')) AS ExpiryTimeM3, IIf([PreCompoundChemoStock]=-1 Or [LicensedChemoStock]=-1,Format([ExpiryTimeM1PC],'h:nn             dd/mm/yyyy'),Format([ExpiryTimeM2],'h:nn               dd/mm/yyyy')) AS ExpiryTimeM4, IIf(DateDiff('d',DateAdd('n',[ExpiryTimeM],[ExpiryTimeM1]),[ExpiryTimeM3])<0,-1,0) AS ExpiryWarning, tblChemoRecord.AdditionMMOL, tblChemoRecord.ChemoCare, [tblChemoPreparation].[LabelName] & IIf(Not IsNull([BrandedTradeName]),' (' & [BrandedTradeName] & ')','') AS LabelName, tblChemoRecord.ClinicalTrial, tblChemoRecord.Manufacturer1, tblChemoRecord.Quantity1, tblChemoRecord.StockBatchNumber1, tblChemoRecord.StockExpiryDate1, tblChemoRecord.Manufacturer2, tblChemoRecord.Quantity2, tblChemoRecord.StockBatchNumber2, tblChemoRecord.StockExpiryDate2, tblChemoRecord.Manufacturer3, tblChemoRecord.Quantity3, tblChemoRecord.StockBatchNumber3, tblChemoRecord.StockExpiryDate3, tblChemoRecord.Manufacturer4, tblChemoRecord.Quantity4, tblChemoRecord.StockBatchNumber4, tblChemoRecord.StockExpiryDate4, tblChemoRecord.Manufacturer5, tblChemoRecord.Quantity5, tblChemoRecord.StockBatchNumber5, tblChemoRecord.StockExpiryDate5, tblChemoRecord.Manufacturer6, tblChemoRecord.Quantity6, tblChemoRecord.StockBatchNumber6, tblChemoRecord.StockExpiryDate6, tblChemoRecord.Manufacturer7, tblChemoRecord.Quantity7, tblChemoRecord.StockBatchNumber7, tblChemoRecord.StockExpiryDate7, tblChemoRecord.Manufacturer8, tblChemoRecord.Quantity8, tblChemoRecord.StockBatchNumber8, tblChemoRecord.StockExpiryDate8, tblChemoRecord.Manufacturer9, tblChemoRecord.Quantity9, tblChemoRecord.StockBatchNumber9, tblChemoRecord.StockExpiryDate9, tblChemoRecord.Manufacturer10, tblChemoRecord.Quantity10, tblChemoRecord.StockBatchNumber10, tblChemoRecord.StockExpiryDate10, tblChemoRecord.Manufacturer11, tblChemoRecord.Quantity11, tblChemoRecord.StockBatchNumber11, tblChemoRecord.StockExpiryDate11, tblChemoRecord.Manufacturer12, tblChemoRecord.Quantity12, tblChemoRecord.StockBatchNumber12, tblChemoRecord.StockExpiryDate12, tblChemoRecord.ManufacturerInfusionFluid1, tblChemoRecord.QuantityInfusionFluid1, tblChemoRecord.StockBatchNumberInfusionFluid1, tblChemoRecord.StockExpiryDateInfusionFluid1, tblChemoRecord.ManufacturerInfusionFluid2, tblChemoRecord.QuantityInfusionFluid2, tblChemoRecord.StockBatchNumberInfusionFluid2, Left([Patient],InStr(1,[Patient],' ')-1) AS FirstName, Right([Patient],(Len([Patient]))-((Len([FirstName])+1))) AS LastName, Left([FirstName],1) & Left([LastName],1) AS PatientInitial, [BatchNumber] & [PatientInitial] AS BarcodeRef, tblChemoRecord.PreCompoundChemoStock, tblChemoPreparation.RemoveOverageAndDrugVolume, tblChemoRecord.EnteredBy, tblChemoRecord.ClinicalTrialName, tblChemoRecord.PlannedCycle, tblChemoRecord.ProtocolCycleNumber, tblChemoRecord.RealDay, tblChemoRecord.ProtocolDay, Left([Patient],1) & '.' & Mid([Patient],InStrRev([Patient],' ')) AS BoxName, tblChemoPreparation.AuthorisedBy, tblChemoPreparation.CheckedBy, Format([TreatmentTime],'Short Time') AS TreatmentTimeWS, tblChemoRecord.LicensedChemoStock, tblDocumentation.EPrescribingProtocol, tblChemoRecord.TreatmentTime, IIf(TimeValue([TreatmentTime])>TimeValue(#12/30/1899 17:0:0#),WeekdayName(Weekday([Date])) & ' ' & [Date]+1,WeekdayName(Weekday([Date]-1)) & ' ' & [Date]) AS BoxLabelDate, tblBrandedDrugName.BrandedTradeName, tblBrandedDrugName.BrandedManufacturer, qryChemoPreparationExpiry3.DrugManufacturer, qryChemoPreparationExpiry3.ExpiryTimeReference, tblChemoRecord.SupportFluidManufacturer1, tblChemoRecord.SupportFluidBN1, tblChemoRecord.SupportFluidExpiry1, tblChemoRecord.SupportFluidManufacturer2, tblChemoRecord.SupportFluidBN2, tblChemoRecord.SupportFluidExpiry2, tblChemoRecord.DeviceManufacturer, tblChemoRecord.DeviceBN, tblChemoRecord.DeviceExpiry, Nz(DLookUp('EarlyAccessMedicineSupplied','tblClinicalTrial','[ClinicalTrialName]=''' & [ClinicalTrialName] & ''' And [ClinicalTrialDrug]=''' & ChemoPreparation & ''''),0) AS EarlyAccessMedicineSupplied, Nz(DLookUp('ClinicalTrialStockSupplied','tblClinicalTrial','[ClinicalTrialName]=''' & [ClinicalTrial] & ''' And [ClinicalTrialDrug]=''' & ChemoPreparation & ''''),0) AS TrialStockSupplied, tblChemoPreparation.DrugMaxCumulativeDosePerUnit, tblChemoPreparation.CompoundedInIsolator, qryClinicalTrialLabel.ClinicalTrialSetupName, qryClinicalTrialLabel.ClinicalTrialEudraNumber, qryClinicalTrialLabel.ClinicalTrialSponsor, qryClinicalTrialLabel.ClinicalTrialSponsorAddress, qryClinicalTrialLabel.ClinicalTrialSponsorTelephoneNumber, qryClinicalTrialLabel.ClinicalTrialSiteNumber, qryClinicalTrialLabel.ClinicalTrialEmail, qryClinicalTrialLabel.ClinicalTrialPrincipalInvestigator, qryClinicalTrialLabel.ClinicalTrialSiteLocation, qryClinicalTrialLabel.ClinicalTrialPatientTrialNumber, tblChemoRecord.SiteLocation AS ActualSiteLocation, IIf([PreCompoundChemoStock]=-1 And [Manufacturer1]=SiteLocationForPCname(),LocalPrecompoundedFilterUse([StockBatchNumber1],[Preparation],[ChemoRoute]),[qryChemoPreparationExpiry3].[UseInLineFilter]) AS UseInLineFilterA INTO atblChemoWSAllX

 

[Thicko]

FROM qryClinicalTrialLabel RIGHT JOIN ((((((tblChemoPreparation INNER JOIN tblDrugList ON (tblChemoPreparation.ChemoRoute = tblDrugList.DrugRoute) AND (tblChemoPreparation.ChemoPreparation = tblDrugList.DrugName)) INNER JOIN tblChemoRecord ON (tblChemoPreparation.ChemoRoute = tblChemoRecord.Route) AND (tblChemoPreparation.ChemoPreparation = tblChemoRecord.Preparation)) LEFT JOIN tblDocumentation ON tblChemoRecord.PlannedCycle = tblDocumentation.Number) INNER JOIN tblDrugListConsumable ON (tblChemoPreparation.ChemoPreparation = tblDrugListConsumable.DrugName) AND (tblChemoPreparation.ChemoRoute = tblDrugListConsumable.DrugRoute)) LEFT JOIN tblBrandedDrugName ON (tblChemoRecord.Route = tblBrandedDrugName.BrandedDrugRoute) AND (tblChemoRecord.Preparation = tblBrandedDrugName.BrandedDrugName)) LEFT JOIN qryChemoPreparationExpiry3 ON (tblChemoRecord.PreCompoundChemoStock = qryChemoPreparationExpiry3.PreCompounded) AND (tblChemoRecord.Preparation = qryChemoPreparationExpiry3.DrugName) AND (tblChemoRecord.Route = qryChemoPreparationExpiry3.DrugRoute)) ON qryClinicalTrialLabel.TrialProductBatchNumber = tblChemoRecord.BatchNumber
WHERE (((tblChemoRecord.BatchNumber)='M2308341') AND ((tblChemoRecord.Date) Between [Forms]![frmDateSelect].[Startdate] And [Forms]![frmDateSelect].[Enddate]) AND ((tblBrandedDrugName.BrandedManufacturer) Is Null Or (tblBrandedDrugName.BrandedManufacturer)=IIf(IsNull([Manufacturer1]),IIf(IsNull([Manufacturer2]),IIf(IsNull([Manufacturer3]),IIf(IsNull([Manufacturer4]),[tblChemoPreparation].[Manufacturer],[Manufacturer4]),[Manufacturer3]),[Manufacturer2]),[Manufacturer1])) AND ((qryChemoPreparationExpiry3.DrugManufacturer)=IIf(IsNull([Manufacturer1]),IIf(IsNull([Manufacturer2]),IIf(IsNull([Manufacturer3]),IIf(IsNull([Manufacturer4]),[tblChemoPreparation].[Manufacturer],[Manufacturer4]),[Manufacturer3]),[Manufacturer2]),[Manufacturer1])) AND ((tblChemoRecord.SiteLocation)=SiteLocationForPCname()) AND ((tblChemoPreparation.SiteLocation)=Nz(DLookUp('SiteLocation','tblChemoPreparation','ChemoPreparation=''' & [ChemoPreparation] & ''' And ChemoRoute=''' & [ChemoRoute] & ''' And [SiteLocation]=''' & SiteLocationForPCname() & ''' And [CurrentDrug]=1'),'Global')) AND ((tblDrugList.SiteLocation)=Nz(DLookUp('SiteLocation','tblDrugList','[DrugName]=''' & [ChemoPreparation] & ''' And [DrugRoute]=''' & [ChemoRoute] & ''' And [SiteLocation]=''' & SiteLocationForPCname() & ''''),'Global')) AND ((tblDrugListConsumable.SiteLocation)=Nz(DLookUp('SiteLocation','tblDrugListConsumable','[DrugName]=''' & [ChemoPreparation] & ''' And [DrugRoute]=''' & [ChemoRoute] & ''' And [SiteLocation]=''' & SiteLocationForPCname() & ''''),'Global')) AND ((tblDrugList.MinDose)<[Dose]) AND ((tblDrugList.MaxDose)>=[Dose]) AND ((tblDrugListConsumable.MinDose)<[Dose]) AND ((tblDrugListConsumable.MaxDose)>=[Dose]) AND ((tblChemoRecord.TreatmentCancelled)=False));

 

[jdraw]

Might be more comprehensible if you told us WHAT you are trying to do rather than showing tidbits of HOW you did something with strings of SQL code.
Can you post a copy of the database with only a few records or made up names to protect anything personal/private?