Return results in a date range

[Teofilo Homsany]

Hi! what would be the correct form to specify on my query that I want the
info from a starting date to an ending date?

I keep getting a Data Type mismatch in criteria expression when I try to run
this in ColdFusion.
Is there something I'm doing wrong with the t.Fecha fields (date fields)?

Please let me know if you can help,
Thanks much in advance,

Teo
SELECT t.[Fecha transacción] as fecha, d.[Nombre Corto] as documento,
t.Documento as codigo,
t.Débitos as debitos, t.Créditos as creditos FROM Transacción t
INNER JOIN Documentos d
on t.[Tipo de Documento] = d.[Tipo de Documento]
WHERE t.[Fecha transacción] > '10-10-03' and t.[Fecha transacción] <
'01-01-04' AND t.Cliente='1'

 

[Jeff Boyce]

Teofilo

I believe you need to "bracket" the dates with the pound sign ("#"), not the
apostrophe.

 

[Teofilo Homsany]

Hi, thanks so much for replying.
I'm actually using # signs cause the original date is a variable. What I
pasted here was the result of the query that was run. So it should just be
#date# without the apostrophe?

Thanks much, I'll try that.

Teo

 

[Jeff Boyce]

Teofilo

A combination of what you've describe should work.

First, you'll need the "#" signs to bracket what will be a date. Then, if
you are using a variable, you'll need to concatenate. Something like
(actual syntax may vary):

" ... WHERE t.[Fecha transacción] > #" & YourFirstDateVariable & "# and
t.[Fecha transacción] < # & YourSecondDateVariable & "# AND ... ;"

See if that helps.