Dsum problem when filtering by dates and with machine names

[sdel_nevo]

Hi guys



I am having a really hard time with a dsum.

the code below runs on a forms onload event

Private Sub Form_Load()

'code below is for the KDM areas
Me.KDMTXTBox = DCount("CriticalChangeMachineID", "SignificantEventLogMachine", "CriticatMachineLocation = 2") '2=KDM from theSignificantMachineLogLocationTable
Me.KDMDailyCapacityTXTbox = DLookup("DailyCapacity", "UNEXcapacity", "'MachineArea=kdm'")
Me.KDMDaysWorkedTXTbox = DLookup("DaysWorked", "UNEXcapacity", "'MachineArea=kdm'")

Dim AverageCapacity As String
AverageCapacity = Round(Me.KDMDailyCapacityTXTbox / Me.KDMDaysWorkedTXTbox / Me.KDMTXTBox, 2)
KDMAverageCapacityTXTbox = AverageCapacity

Dim CapacityDatesQuery As String
CapacityDatesQuery = "DueDate Between #" & [Forms]![form1]![StartDate] & "# And #" & [Forms]![form1]![EndDate] & "#"
Me.KDM1TXTbox = Me.KDMAverageCapacityTXTbox

Me.KDM1ActualCapacityTXTBox = Nz(DSum("QTYOUTST", "UNEXProcess", CapacityDatesQuery And "Machine = 'KDM 1'"), 0)
Me.Text36 = Nz(DSum("QTYOUTST", "UNEXProcess", "Machine='KDM 3'"), 0)
Me.Text37 = Nz(DSum("QTYOUTST", "UNEXProcess", "Machine='KDM 4'"), 0)
Me.Text38 = Nz(DSum("QTYOUTST", "UNEXProcess", "Machine='KDM 5'"), 0)
Me.Text39 = Nz(DSum("QTYOUTST", "UNEXProcess", "Machine='KDM 6'"), 0)
Me.Text40 = Nz(DSum("QTYOUTST", "UNEXProcess", "Machine='KDM 7'"), 0)
Me.Text41 = Nz(DSum("QTYOUTST", "UNEXProcess", "Machine='KDM 8'"), 0)
End Sub

basically what I want this code to do is work out the capacity of our machines on the warehouse floor.

this line is coursing me issues
Me.KDM1ActualCapacityTXTBox = Nz(DSum("QTYOUTST", "UNEXProcess", CapacityDatesQuery And "Machine = 'KDM 1'"), 0)

this line works using the code below
Me.KDM1ActualCapacityTXTBox = Nz(DSum("QTYOUTST", "UNEXProcess", CapacityDatesQuery ),0)

but what I really need to do is filter the result to show the total for the machine

when I add the machine to the code I get an "Run time error '13': type mismatch" error

the column "Machine" in the table "UNEXProcess" that the dsum is referencing has the machine name in text, so im pretty stumped as to what to do

any help would be most welcome, I think I've got to the point were I cant see the woods for the trees.

many thanks

Steve

[sdel_nevo]

Hi All

I have managed to sort this

I am now selecting dates from a form called UnexCapacityDatesSelectionForm

I changed the code to this
= Nz(Round(DSum("QTYOUTST", "UNEXProcess", "Completed = 0 And Machine = 'KDM 1' and dueDate Between #" & [Forms]![UnexCapacityDatesSelectionForm]![StartDate] & "# And #" & [Forms]![UnexCapacityDatesSelectionForm]![EndDate] & "#"), 2), 0)

instead of declaring the dates I added them within the code and was even able to only sum the values for the completed jobs
woop woop

Steve