Hi Guys, i am receiving data type mismatch in criteria expression, there is an invoice with paid ticked and an invoice with paid not ticked just to retrieve the answer
The paid field is a yes/no field and total is set to currency, i can't see why !!!
Code:
Dim StartDate As Date, EndDate As DateDim ExpRecs As Integer, InvRecs As Integer
Dim InvUnpaid As Currency, InvPaid As Currency, InvTotal As Currency
Dim ExpUnpaid As Currency, ExpPaid As Currency, ExpTotal As Currency
Dim InvPaidY As String, InvUnpaidN As String, ExpPaidY As String, ExpUnpaidN As String
StartDate = DateSerial(Me.cboYear, Me.cboMonth, 1)
EndDate = DateSerial(Me.cboYear, Me.cboMonth + 1, 0)
Me.txtStartDate = StartDate
Me.txtEndDate = EndDate
InvPaidY = "Yes"
InvUnpaidN = "No"
ExpPaidY = "Yes"
ExpUnpaidN = "No"
ExpRecs = DCount("ReceiptNo", "tblExpenses", "[Date] Between #" & StartDate & "# And #" & EndDate & "#")
InvRecs = DCount("InvoiceNumber", "tblInvoices", "[InvoiceDate] Between #" & StartDate & "# And #" & EndDate & "#")
InvTotal = DSum("Total", "tblInvoices", "[InvoiceDate] Between #" & StartDate & "# And #" & EndDate & "#")
ExpTotal = DSum("Amount", "tblExpenses", "[Date] Between #" & StartDate & "# And #" & EndDate & "#")
InvPaid = DSum("Total", "tblInvoices", "[InvoiceDate] Between #" & StartDate & "# And #" & EndDate & "# And [Paid] = '" & InvPaidY & "'")
InvUnpaid = DSum("Total", "tblInvoices", "[InvoiceDate] Between #" & StartDate & "# And #" & EndDate & "# And [Paid] = '" & InvUnpaidN & "'")
ExpPaid = DSum("Amount", "tblExpenses", "[Date] Between #" & StartDate & "# And #" & EndDate & "# And [Paid] = '" & ExpPaidY & "'")
ExpUnpaid = DSum("Amount", "tblExpenses", "[Date] Between #" & StartDate & "# And #" & EndDate & "# And [Paid] = '" & ExpUnpaidN & "'")
Me.txtTotalInvoices = InvRecs
Me.txtInvoicesPaid = InvPaidY
Me.txtTotalInvoicesAmount = InvTotal
Me.txtInvoicesUnpaid = InvUnpaidN
Me.txtTotalExpenses = ExpRecs
Me.txtExpensesPaid = ExpPaidY
Me.txtExpensesUnpaid = ExpUnpaidN
Me.txtTotalExpensesAmount = ExpTotal