Between 2 dates

[The Bey]

I've got a problem and I think i'm going to settle it with 3 queries... cue problem:

A set of dates in a column can be in a range of 6 months from today's date, to 5 years from today's date, dependent on what's in its previous criteria.

I would like to run a query that will look if the dates in that column are between 0 to >1 year of today's date. The next between 1 - 3 years, and the final between 3 - 5 years; I can do this with temporary queries I think, thus not clogging up my navigation bar.

I will then use a text box on a form to count the amount of records that the query has, but I'm also unsure on what to write as the statement for the DCount.

The items I'm interested in is the SQL for the text in the query, and also the data for the text box.

An e.g. for the query criteria: Between DateAdd("yyyy", 1, Date()) And ("yyyy", 3, Date()) - I can't write the brackets in the SQL, so how do I do it?

 

[jzwp22]

I'm not really following what you are trying to do, but you can just use 3 Dcount() functions (one in each of 3 textboxes)



Count for records between 1 and 3 years from today's date:
=Dcount("*","yourtable", "yourdatefield BETWEEN #" & DateAdd("yyyy", 1, Date()) & "# and #" & dateadd("yyyy", 3, Date()) & "#")


Count for records between 3 and 5 years from today's date:
=Dcount("*","yourtable", "yourdatefield BETWEEN #" & DateAdd("yyyy", 3, Date()) & "# and #" & dateadd("yyyy", 5, Date()) & "#")

between 0 to >1 year of today's date.

Did you mean from today to infinity: (greater (>) 1 year from today's date)? Or did you mean from from today's date to 1 year from today's date? Or did you mean from today until the end of the current year?

 

[Galaxiom]

Using multiple DCounts against the same data is not very efficient. It would be better to use a single query as the recordsource of a subform with bound textboxes to display the counts.

Either way, make sure you index the date field in the table.

 

[The Bey]

Only just got round to testing this as I only have Access in work, but the DCount is working great, thanks.

I'd like to be able to make graphs from these textboxes, but they're not bound to any previous tables as they are record counts and not values. What'd be the best way to draw tables/graphs from these values?

Just a simple bar chart would be sufficient

Oh and btw I did a schoolboy error and used the > sign when I meant to use the < sign

 

[jzwp22]

In order to be able to create a chart, I believe the data must come from a query or table, so the DCount() functions in the form will probably not work. I am thinking that you will need separate queries that do the counting since the criteria will be different for each and then bring those 3 queries together into 1 query similar to what GalaxiomAtHome suggested instead of the DCount() functions. That final query is what you will need to create the chart. In order to bring the counts from the 3 queries together, the data needs to be grouped on some field/value that is common between the 3 queries, so what do these counts represent? Are they for orders for a particular customer, product, project? We'll need a little more info in order to help out better.

 

[Taruz]

Hello..

Age ranges can get the best partition function. Use.:

Partition(Field_Name, lower_limit, upper_limit, interval)

I prepared a sample; please review

My English is not very good; maybe i can not understood the question.