Substracting months to a date

[ruthib4]

Hi Everybody,
I need your help is possible, i have the following query, qhet I need to get from it is the ifnormation of my DB of the last 13 months.

SELECT [Recoveries TC].Fecha, [Recoveries TC].País, Sum([Recoveries TC].[Debit TC]) AS [SumaDeDebit TC], Sum([Recoveries TC].[Credit Tc]) AS [SumaDeCredit Tc], Sum([Recoveries TC].[Total TC]) AS [SumaDeTotal TC], Sum([GFl TC].[Debit TC]) AS [SumaDeDebit TC1], Sum([GFl TC].[Credit TC]) AS [SumaDeCredit TC1], Sum([GFl TC].[Total TC]) AS [SumaDeTotal TC1], IIf([GFl TC]![Debit TC]=0,0,[Recoveries TC]![Debit TC]/[GFl TC]![Debit TC]) AS RecRatioDeb, IIf([GFl TC]![Credit TC]=0,0,[Recoveries TC]![Credit TC]/[GFl TC]![Credit TC]) AS RecRatioCre, IIf([GFl TC]![total TC]=0,0,[Recoveries TC]![total TC]/[GFl TC]![total TC]) AS RecRatiotot
FROM [Recoveries TC] LEFT JOIN [GFl TC] ON ([Recoveries TC].Fecha = [GFl TC].Fecha) AND ([Recoveries TC].País = [GFl TC].País)
WHERE ((([Recoveries TC].Fecha) Between DateAdd("m",-13,Now()) And Now()))
GROUP BY [Recoveries TC].Fecha, [Recoveries TC].País, IIf([GFl TC]![Debit TC]=0,0,[Recoveries TC]![Debit TC]/[GFl TC]![Debit TC]), IIf([GFl TC]![Credit TC]=0,0,[Recoveries TC]![Credit TC]/[GFl TC]![Credit TC]), IIf([GFl TC]![total TC]=0,0,[Recoveries TC]![total TC]/[GFl TC]![total TC])
HAVING ((([Recoveries TC].País)=[Formularios]![Dashboard]![cmdPaisKPI]));

This query qithout the date criteria works perfectly, but when i run with the adddate function i am getting no values, and i am sure there are.

Please help

[jzwp11]

I would break this up into 2 queries. Use a simple SELECT query that pulls the data based on the date criteria you specify.

Then create a second query based on the first one. Use the second query to do your grouping and summing.

[boblarson]

1. is Fecha really a Date/Time field or stored as Text?

2. When using dates, without time, don't use NOW() use DATE() instead.