Date Coding

[Drunkenneo]

I have a date stored, lets say 22/07/2013, and i have a table with entry date, and ordernumber.

i want to write a count of all the ordernumbers they were in month of july i.e from taking month and year from current month, from coding.
Thanks in advance..

 

[nanscombe]

I would create a column based on the Year and Month of the order date, Format([orderDate],"yyyymm"), then create a criteria from today's date based on the same format of "yyyymm".

SELECT [COLOR="Red"]Format([orderDate],"yyyymm")[/COLOR] AS orderMonth, Count(tblOrders.orderNumber) AS NoOrders
FROM tblOrders
GROUP BY Format([orderDate],"yyyymm")
HAVING (((Format([orderDate],"yyyymm"))=[COLOR="RoyalBlue"]Format(Now(),"yyyymm")[/COLOR]));

This would give an output of

orderMonth	NoOrders
201307		2

 

[Drunkenneo]

Dim qrystr, y As String


qrystr = "SELECT SUM(Invoice_Log.Total_Orders_Processed) AS NoOrders,"
qrystr = qrystr & " #" & Format(Invoice_Log.invoice_date, "yyyymm") & "# AS orderMonth From invoice_log"
qrystr = qrystr & "GROUP BY #" & Format(invoice_date, "yyyymm") & "#"
qrystr = qrystr & " HAVING (#" & Format(invoice_date, "yyyymm") & "# = #" & Format(strdate, "yyyymm") & "&)"


giving error for obj required, please help

 

[nanscombe]

The original was SQL which could have put into a query.


Right, now we have a bit more information let's try again. In your table you have multiple records with a number field called "Total_Orders_Processed" saying how many orders were processed on a particular day, rather than individual orders, which you then want to sum using code.

Am I reading that correctly?

This is one line of code which should suffice. It uses the DSum() function to do the task rather than having to use SQL or a query.

noOrders = DSum(Nz("Total_Orders_Processed", 0), "Invoice_Log", "Format(invoice_date, ""yyyymm"") = '" & Format(Now, "yyyymm") & "'")

 

[Drunkenneo]

learnt new thing today...thanks