Statement for dates with SQL

[charly.csh]

Hi everyone,

I want to make a query using SQL statement for dates
I understand how get this for text values but I have issues to state this for dates:

************************************************** **************


SELECT Acciones_Query.Responsable_Accion, Nz(DCount("*","Acciones_Query","Responsable_Accion = """ & [Responsable_Accion] & """ AND Estatus_Accion = 'Complete'"),0) AS Complete, Nz(DCount("*","Acciones_Query","Responsable_Accion = """ & [Responsable_Accion] & """ AND Estatus_Accion = 'In Progress'"),0) AS Pending
FROM Acciones_Query
WHERE (((Acciones_Query.Fecha_Accion) Between [Start Date] And [End Date]))
GROUP BY Acciones_Query.Responsable_Accion;

************************************************** **************


I want to distribute on this way but with the statement above

************************************************** ***************
SELECT Format([Fecha_Accion],"mmmm yyyy") AS Periodo, Count(*) AS Cantidad
FROM Acciones_Query
GROUP BY Format([Fecha_Accion],"mmmm yyyy")
ORDER BY First(Acciones_Query.Fecha_Accion);

************************************************** ***************


Thanks for the help!!

[pbaldy]

Is this what you're looking for?

DCount("*","Acciones_Query","Responsable_Accion = #" & [Responsable_Accion] & "# AND Estatus_Accion = 'Complete'")

[Welshgasman]

I have always formatted the date to mm/dd/yyyy and enclosed that with # when I have had to create sql or criteria for domain functions.

Strings need to be surrounded by single quotes ' unless it contains a single quote, then triple double quotes works, I think?


Date literals with # and in mm/dd/yyyy or yyyy-mm-dd format


Numbers do not need anything

I also put the criteria into a string variable so I can debug.print it until I get it correct, then I use that variable in the code.

[charly.csh]

Hi everyone

With your comments I corrected the SQL sequence, the only inconvenience I have just to finish my query is for grouping, it shows a sintaxis error for a missing operator with
GROUP BY

**************************************************
SELECT Format(Acciones_Query.Fecha_Accion,"mmmm yyyy") AS Periodo, Nz(DCount("*","Acciones_Query","Fecha_Accion = #" & [Fecha_Accion] & " # AND Estatus_Accion = 'Complete'"),0) AS Complete, Nz(DCount("*","Acciones_Query","Fecha_Accion = #" & [Fecha_Accion] & " # AND Estatus_Accion = 'In progress'"),0) AS Pending
FROM Acciones_Query
WHERE (((Acciones_Query.Fecha_Accion) Between [Start Date] And [End Date]))
ORDER BY Acciones_Query.Fecha_Accion

GROUP BY Format(Acciones_Query.Fecha_Accion, "mmmm yyyy"); =========> If I remove this it works but it shows month by month and I want to get the total of the month

************************************************** *

Thanks in advance for the help!!

[pbaldy]

I think you have the statements out of order, but you don't have any aggregate functions for it to group data with. Maybe add Sum() or Count() around your DCount() fields.

That said, having aggregate functions like DCount() inside a query can be a big performance problem. I wonder if this gives you want you want:

SELECT Format(Acciones_Query.Fecha_Accion,"mmmm yyyy") AS Periodo
, Sum(IIf(Estatus_Accion = 'Complete'", 1, 0)) AS Complete
, Sum(IIf(Estatus_Accion = 'In progress', 1, 0)) AS Pending
FROM Acciones_Query
WHERE (((Acciones_Query.Fecha_Accion) Between [Start Date] And [End Date]))
GROUP BY Format(Acciones_Query.Fecha_Accion, "mmmm yyyy")

Note I took out the date portion since it seemed odd to compare a field to itself.

[charly.csh]

Thank you very much!! it worked pretty good!! Just I had to take out the " from 'Complete'. I highly appreciate the help

I leave this for somebody else who could it also help



SELECT Format(Acciones_Query.Fecha_Accion,"mmmm yyyy") AS Periodo, Sum(IIf(Estatus_Accion = 'Complete', 1, 0)) AS Complete, Sum(IIf(Estatus_Accion = 'In progress', 1, 0)) AS Pending
FROM Acciones_Query
WHERE (((Acciones_Query.Fecha_Accion) Between [Start Date] And [End Date]))
GROUP BY Format(Acciones_Query.Fecha_Accion, "mmmm yyyy")

[pbaldy]

Ah, I missed that, glad you got it sorted.