good day!

I just want to ask if is it possible to have count on dinstint? I have a query below but it produces error in the expression. Im pulling data in MSAccess using ADODB.

SQL = "SELECT" & _
" (SELECT COUNT(DISTINCT DateOInvoice) FROM InvoiceMain AS A WHERE (DateOInvoice>=DateValue('" & StartDate & "') AND DateOInvoice<=DateValue('" & ReportDate & "'))) AS NoofDaysMonth," & _
" (SELECT COUNT(DISTINCT DateOInvoice) FROM InvoiceMain AS B WHERE (DateOInvoice>=DateValue('" & BeginDate & "') AND DateOInvoice<=DateValue('" & ReportDate & "'))) AS NoofDaysYear" & _
" FROM InvoiceMain"

Thank you!

Edited 2 Years Ago by blocker

I have modified the query and its running now but it give me a wrong result. Did i miss something?

SQL = "SELECT COUNT(M.DateOInvoice) AS DaysInMonth, COUNT(Y.DateOInvoice) AS DaysInYear FROM" & _
" (SELECT DISTINCT DateOInvoice FROM InvoiceMain AS A WHERE (DateOInvoice>=DateValue('" & StartDate & "') AND DateOInvoice<=DateValue('" & ReportDate & "'))) AS M," & _
" (SELECT DISTINCT DateOInvoice FROM InvoiceMain AS B WHERE (DateOInvoice>=DateValue('" & BeginDate & "') AND DateOInvoice<=DateValue('" & ReportDate & "'))) AS Y"
rs_NoofDays.Open SQL, conn, adOpenStatic, adLockOptimistic

Thank you!

I got the solution.. Working query below:

SQL = "SELECT" & _
      " (SELECT COUNT(DateOInvoice) FROM (SELECT DISTINCT DateOInvoice FROM InvoiceMain WHERE (DateOInvoice>=DateValue('" & StartDate & "') AND DateOInvoice<=DateValue('" & ReportDate & "')))) AS DaysinMonth," & _
      " (SELECT COUNT(DateOInvoice) FROM (SELECT DISTINCT DateOInvoice FROM InvoiceMain WHERE (DateOInvoice>=DateValue('" & BeginDate & "') AND DateOInvoice<=DateValue('" & ReportDate & "')))) AS DaysinYear" & _
      " FROM InvoiceMain"

Thank you!

This question has already been answered. Start a new discussion instead.