I am trying to run a query on a table [tblMain]. I have an input form where the user enters a date range. I would like to count the Month to Date Sales. The month would be that of the end date (forms!frmflash!enddate). How could I count the number of sales between the first of the month and the end date? Each record has a salesdate1, salesdate2, salesdate3, salesdate4, salesdate5, and salesdate6. I need to look in each of these fields and count the sale if it is between the 1st of the month and the enddate.
Count Between Date Range
- Thread starter smaumau
- Start date
Lister
Z Shift
- Local time
- Tomorrow, 00:42
- Joined
- Aug 24, 2003
- Messages
- 305
Count Dataset
How do you intend to use this "count"? To get the number you have a parameter query that would take the date range that you want.
Between #First Date# And #Last Date#
Then you use the Count function to count the result in any field.
eg: Number of Sales: Count(tblMain.strSalesData)
If this were going to be presented on a report it would be easier to use the Count function on an unbound field in the footer.
You have no intention of storing this value in a table do you?
And why so many "SaleDates", how many times do you sell the same item?
Perhaps I do not understand you correctly.
How do you intend to use this "count"? To get the number you have a parameter query that would take the date range that you want.
Between #First Date# And #Last Date#
Then you use the Count function to count the result in any field.
eg: Number of Sales: Count(tblMain.strSalesData)
If this were going to be presented on a report it would be easier to use the Count function on an unbound field in the footer.
You have no intention of storing this value in a table do you?
And why so many "SaleDates", how many times do you sell the same item?
Perhaps I do not understand you correctly.
Continued
One home can be sold and cancelled, and then sold again, etc. I guess the only thing that I am really struggling with is how to make the date range I am looking at to be from the first of the month to the end date. For example, if the report that is trying to be run is between 3/15/06 and 3/21/06, I also need to look at the range from 3/1/06 to 3/21/06. If the date range is 3/28/06 thru 4/3/06, I would need to look at the range from 4/1/06 to 4/3/06, and so on.
One home can be sold and cancelled, and then sold again, etc. I guess the only thing that I am really struggling with is how to make the date range I am looking at to be from the first of the month to the end date. For example, if the report that is trying to be run is between 3/15/06 and 3/21/06, I also need to look at the range from 3/1/06 to 3/21/06. If the date range is 3/28/06 thru 4/3/06, I would need to look at the range from 4/1/06 to 4/3/06, and so on.
Lister
Z Shift
- Local time
- Tomorrow, 00:42
- Joined
- Aug 24, 2003
- Messages
- 305
Think I am seeing the issue here. The date is easy; you can code it into the running of the report or the underlying query.
Syntax is Between [First Date] And [Last Date]
This is a simple to pull data with user input date ranges.
Have a look in the help file at the DatePart and the DateSerial Functions.
May find what you are looking for.
The bigger issue is that you are storing Many dates in the same table as the house data. Since there are "MANY" dates for "ONE" house, the dates should have been stored in there own table and associated with the house data table.
So if you are storing many dates for the same house in one table
ie: [HouseData],[Date1],[Date2],[Date3], etc
If this is the case it makes the solution trickier.
Also if this is the case how many "extra" date fields do you have?
Syntax is Between [First Date] And [Last Date]
This is a simple to pull data with user input date ranges.
Have a look in the help file at the DatePart and the DateSerial Functions.
May find what you are looking for.
The bigger issue is that you are storing Many dates in the same table as the house data. Since there are "MANY" dates for "ONE" house, the dates should have been stored in there own table and associated with the house data table.
So if you are storing many dates for the same house in one table
ie: [HouseData],[Date1],[Date2],[Date3], etc
If this is the case it makes the solution trickier.
Also if this is the case how many "extra" date fields do you have?
