Counting Number of Days (not records) in a Month

[Tomfernandez1]

Greetings-

Attached is a "TripsDatabase" which includes a Table and Report. The report shows records for two dates in January, and both dates fall on a Wed.

Instead of counting 194 Wed, I'd like to count only two Wed, which I have attempted in the header of the report, but to no avail.



I would be grateful for any assistance or direction on this matter..

Thank you,

-Tommy

p.s. on a different post of mine, I thought you had helped me in solving this, so forgive me if this request may seem redundant..

[ItsMe]

Not perfectly sure what you are trying to accomplish. If you want to count the number of Saturdays for a given date range, that would require some fancy code. It might be easier and quicker for the app to process the request if you stored some date info in a separate table to support the calculation.

To answer a previous complex date question I created a little program that will first populate a table with dates and relative (calculated) data, like day of week. So, when you start the program you must first supply a date you want your table to begin with and the number of days forward you would like the table to end.

If you choose 1/1/2000 to begin and 3000 days, the program will populate the table with dates from 1/1/2000 to 3/18/2008. There will be 3000 records in the table that you can use as a reference to do further calculations. I believe a table like this could help you because you can create a query to retrieve a date range and count the number of Saturdays. All you would need to start is the table.

The attached app was not built to answer your question but it may help to incorporate the populated table in your DB.

[Tomfernandez1]

ItsMe - thanks for sharing your Weekday database. I am sure that some of the code will prove useful in the further development of my projects.

-Tommy

[ItsMe]

Let us know if you still have questions. I felt like I had been down a similar road before. An additional table seems appropriate to me.

[Tomfernandez1]

I still need help with an expression/code/SQL for counting the actual number of Wed - only two on the report (1/8/2014 and 1/15/2014) instead of counting all records that have "wed" (194).

-Tommy

[ItsMe]

My solution would be to incorporate a table that has a column to indicate what weekday a given date is. Otherwise, you will have to hold many variables in memory as you create a date param and then evaluate the param.

[rpeare]

Create this query:

SELECT TA_REPOSITORY.DATED
FROM TA_REPOSITORY
GROUP BY TA_REPOSITORY.DATED
HAVING (((TA_REPOSITORY.DATED)=#1/15/2014# Or (TA_REPOSITORY.DATED)=#1/8/2014#));

Save it as qry_DaysActive

On your report in the place where you currently have 194 showing

put

=dcount("*", "qry_DaysActive")

[rpeare]

One note before you do this though, that your criteria driving the qry_DaysActive has to be the same (in terms of date range) as your main query. So if your actual query is using dates on a form as it's criteria use the same form references for qry_DaysActive.

[Tomfernandez1]

repeare - setting up the query and using DCount moved me in the right direction, ultimately solving my issue!

Thanks so much.

-Tommy

[rpeare]

A word of caution, do not use domain functions (dsum, dcount, davg, etc) in a query that you are using to build a report. For instance the query driving your report is fine, don't try to add a domain function to that, it will needlessly slow your query down. Domain functions should be used sparingly.

[Tomfernandez1]

repeare - Thanks for the information..

-Tommy