query date

[morgan]

Hi everyone, in a previous post I asked your help to have the following result.

SELECT Format([Data Valuta],"yyyy mm") AS [Data Valuta per mese],
Sum([Importo Debito]+[importo credito]) AS [SumOfImporto Debito], [Movimenti
2006-->].[Centro Costo]
FROM [Movimenti 2006-->]
WHERE ((([Movimenti 2006-->].[Data Valuta]) Between Date() And
DateAdd("m",-5,Date())+2))
GROUP BY Format([Data Valuta],"yyyy mm"), [Movimenti 2006-->].[Centro Costo];

It work as I wanted but now I want to try something more complicated.
I'd like to set the year of [Data Valuta] as a variable in order to check
the situation on the same period of previous years. The Year would be choosen
from a combo box on the main form (Forms![Movimenti Y]![data]). The SQL above
is used to create a subform.
I should change Date() with something else that includes the variable but
it's really too much for my little knowledge. Can someone help me?

Thks in advance

 

[Conan Kelly]

morgan,

I THINK that this might work.



SELECT Format([Data Valuta],"yyyy mm") AS [Data Valuta per mese],
Sum([Importo Debito]+[importo credito]) AS [SumOfImporto Debito], [Movimenti
2006-->].[Centro Costo]
FROM [Movimenti 2006-->]
WHERE ((([Movimenti 2006-->].[Data Valuta]) Between
DateSerial(Forms![Movimenti Y]![data],Month(Date()),Day(Date())) And
DateAdd("m",-5,DateSerial(Forms![Movimenti
Y]![data],Month(Date()),Day(Date())))+2))
GROUP BY Format([Data Valuta],"yyyy mm"), [Movimenti 2006-->].[Centro
Costo];



Make sure you try this in a new query or a copy of your original....I don't
want you to loose your original.

HTH,

Conan

 

[morgan]

Great! It works perfectly.
Thanks
Morgan

morgan,

I THINK that this might work.



SELECT Format([Data Valuta],"yyyy mm") AS [Data Valuta per mese],
Sum([Importo Debito]+[importo credito]) AS [SumOfImporto Debito], [Movimenti
2006-->].[Centro Costo]
FROM [Movimenti 2006-->]
WHERE ((([Movimenti 2006-->].[Data Valuta]) Between
DateSerial(Forms![Movimenti Y]![data],Month(Date()),Day(Date())) And
DateAdd("m",-5,DateSerial(Forms![Movimenti
Y]![data],Month(Date()),Day(Date())))+2))
GROUP BY Format([Data Valuta],"yyyy mm"), [Movimenti 2006-->].[Centro
Costo];



Make sure you try this in a new query or a copy of your original....I don't
want you to loose your original.

HTH,

Conan

Hi everyone, in a previous post I asked your help to have the following
result.

SELECT Format([Data Valuta],"yyyy mm") AS [Data Valuta per mese],
Sum([Importo Debito]+[importo credito]) AS [SumOfImporto Debito],
[Movimenti
2006-->].[Centro Costo]
FROM [Movimenti 2006-->]
WHERE ((([Movimenti 2006-->].[Data Valuta]) Between Date() And
DateAdd("m",-5,Date())+2))
GROUP BY Format([Data Valuta],"yyyy mm"), [Movimenti 2006-->].[Centro
Costo];

It work as I wanted but now I want to try something more complicated.
I'd like to set the year of [Data Valuta] as a variable in order to check
the situation on the same period of previous years. The Year would be
choosen
from a combo box on the main form (Forms![Movimenti Y]![data]). The SQL
above
is used to create a subform.
I should change Date() with something else that includes the variable but
it's really too much for my little knowledge. Can someone help me?

Thks in advance

 

[Conan Kelly]

Da Nada.

Great! It works perfectly.
Thanks
Morgan

morgan,

I THINK that this might work.



SELECT Format([Data Valuta],"yyyy mm") AS [Data Valuta per mese],
Sum([Importo Debito]+[importo credito]) AS [SumOfImporto Debito],
[Movimenti
2006-->].[Centro Costo]
FROM [Movimenti 2006-->]
WHERE ((([Movimenti 2006-->].[Data Valuta]) Between
DateSerial(Forms![Movimenti Y]![data],Month(Date()),Day(Date())) And
DateAdd("m",-5,DateSerial(Forms![Movimenti
Y]![data],Month(Date()),Day(Date())))+2))
GROUP BY Format([Data Valuta],"yyyy mm"), [Movimenti 2006-->].[Centro
Costo];



Make sure you try this in a new query or a copy of your original....I
don't
want you to loose your original.

HTH,

Conan

Hi everyone, in a previous post I asked your help to have the following
result.

SELECT Format([Data Valuta],"yyyy mm") AS [Data Valuta per mese],
Sum([Importo Debito]+[importo credito]) AS [SumOfImporto Debito],
[Movimenti
2006-->].[Centro Costo]
FROM [Movimenti 2006-->]
WHERE ((([Movimenti 2006-->].[Data Valuta]) Between Date() And
DateAdd("m",-5,Date())+2))
GROUP BY Format([Data Valuta],"yyyy mm"), [Movimenti 2006-->].[Centro
Costo];

It work as I wanted but now I want to try something more complicated.
I'd like to set the year of [Data Valuta] as a variable in order to
check
the situation on the same period of previous years. The Year would be
choosen
from a combo box on the main form (Forms![Movimenti Y]![data]). The SQL
above
is used to create a subform.
I should change Date() with something else that includes the variable
but
it's really too much for my little knowledge. Can someone help me?

Thks in advance