Query dates

[tom1252]

How can i specify a query to be for a period of time e.g. i want to find out to total sales of each year?!

 

[boblarson]

Do you have a Date field in your table which corresponds to each record?

 

[tom1252]

Yes there is

 

[tom1252]

I store the dates of each sale

 

[spaddhu]

If so, then add a new column into the query, that extracts the year from the date, (eg. use DatePart, or convert into string and extract the last two chars). Now group by this column and give 'sum' for sales column.

 

[tom1252]

Would i use the following

DatePart(Date()-?)

I not sure what would go where the "?" is?

And then group it by that

 

[spaddhu]

DatePart("yyyy",date_variable) is what you are looking for...

 

[tom1252]

Where says YYYY do i put a year because when i do it doesnt let u run it but when i leave it and the parameter comes up i put in a year and it stil shows all of them. This will be a report after as well

 

[spaddhu]

can you share the table structure you are working with??

 

[tom1252]

tblSales - SalesID Date ServerID
tblSalesItems - SalesItemID SalesID ItemCode Price Quantity

 

[JohnLee]

Hi,

Here is much easier way to achieve what you are looking to do:

In your query where you have the date field for your data, in the row titled "Field" beneath your date field enter the following:

Year: Year([dtmDate]) - Note the name of your datefield should replace my dtmDate

Then

In row titled "Criteria" beneath the date field for your data, which sits directly below the above field enter the following:

Year(Now())

=======

dtmDate should be the name of your date field.

Your query will always display the data for the current year only regardless of month or day.

Good Luck

John

 

[tom1252]

Cheers i added some rep to everyone that helped

 

[JohnLee]

Hi,

Did my suggestion resolve your problem.

John

 

[tom1252]

yh John cheers