Member Avatar for PinoyDev

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 by PinoyDev
  • 1 Contributor
  • 2 Replies
  • 274 Views
  • 5 Days Discussion Span
  • Latest Post Latest Post by PinoyDev
Member Avatar for PinoyDev

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!

Member Avatar for PinoyDev

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!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.