Unkown Date error in Query

[arlelua]

I have the next query

SELECT tbfacturas.dsNumeroFactura, tbfacturas.psCliente, tbClientes.dsNombreRazon, tbfacturas.ddFecha, SUM (tbfacturas.dsSubtotal) as Subtotal
FROM tbFacturas INNER JOIN tbClientes ON tbFacturas.psCliente = tbClientes.psCliente
WHERE (tbFacturas.ddFecha >= #" & ddFecha_Inicio & "#) AND (tbFacturas.ddFecha <= #" & ldFecha_Fin "#)
GROUP BY tbFacturas.dsNumeroFactura,tbFacturas.psCliente,tbFacturas.ddFecha,tbClientes.dsNombreRazon

when my Date (ddFecha_Inicio or ldFecha_Fin ) are in Janaury, it works but when I put Febraury on it, I have this error,

Type mismatch on Access, (the message is in spanish).

Please Help Me if need more code please tell me

 

[SkipVought]

Please post the START & END date values that are in your controls when you get the error.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[arlelua]

Thank´s for yout time SkipVought let me tell you that I´m using access 2007, and this query is from a program develop in VB 6.0,

the values are in format mm/dd/yyyy and this query works fine with this values:

ddFecha_Inicio = 1/1/2011
ldFecha_Fin = 1/31/2011

But when the values change(because if it have to do this)

ddFecha_Inicio = 2/1/2011
ldFecha_Fin = 2/28/2011

Send the error message.

I Also try to do this inside Access 2007 and produces the same error, only changing the month.

If I use the same query without the GROUP BY instruction works, this tell me that GROUP BY have some problems with dates.

Thanks again

 

[SkipVought]

You only had smattering of QUOTES

[red][b]"[/b][/red]SELECT tbfacturas.dsNumeroFactura, tbfacturas.psCliente, tbClientes.dsNombreRazon, tbfacturas.ddFecha, SUM (tbfacturas.dsSubtotal) as Subtotal
FROM tbFacturas INNER JOIN tbClientes ON tbFacturas.psCliente = tbClientes.psCliente 
WHERE (tbFacturas.ddFecha >= #" & ddFecha_Inicio & "#) AND (tbFacturas.ddFecha <= #" & ldFecha_Fin "#)[red][b]"[/b][/red]

Of course this string must be assigned to a variable or the CommandText property.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

What about this ?

yourSQL = "SELECT F.dsNumeroFactura,F.psCliente,C.dsNombreRazon,F.ddFecha,SUM (F.dsSubtotal) AS Subtotal" _
 & " FROM tbFacturas F INNER JOIN tbClientes C ON F.psCliente=C.psCliente" _
 & " WHERE F.ddFecha Between #" & Format(ddFecha_Inicio,"yyyy-mm-dd") & "# And #" & Format(ldFecha_Fin,"yyyy-mm-dd") & "#" _
 & " GROUP BY F.dsNumeroFactura,F.psCliente,C.dsNombreRazon,F.ddFecha"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[arlelua]

Dear SkipVought and PHV, thanks for your answer, but there is not a problem with QOUTES, sorry if I didn´t put it in my first message, and also don´t put the assignation to the variable.

But this aren´t the problem I try to do this inside Acces 2007 (Create a Query) and the problem persists.

This Query works perfectly if I erase the GROUP BY instruction,

PHV maybe I try to use the format yyyy/mm/dd, you think the problem maybe by the format, Thanks again. And thank for your forward.