How to calculate the previous age of a record

[speedracer1971]

I am trying write a select query to find the past age of the records based on a dated field "consigned date" and select only those records over 10 days old. I would like the user to input the date in time that will be used for the calculation reference. When I try this Access tells me the function is too complex. The query works when I change the user selected date with "date()" but this only give me the current age of the record not the age it was at a previous date. I would like to sum all record older than 10 days old from a user inputted date. Can someone please help I have exhausted my Access capabilities.

My code looks like below:

Records over 10 days: Sum(IIf([user input date]-[Consigned Date]>=10,1,0))

This one give me the current age.
Records over 10 days: Sum(IIf(date()-[Consigned Date]>=10,1,0))

 

[Jon K]

You can use the CDate() function or the DateValue() function on the parameter e.g.

Records over 10 days: Sum(IIf(CDate([user input date])-[Consigned Date]>=10,1,0))


Or declare the data type of the parameter:-

Open the query in Design View. choose menu Query, Parameters..., type the parameter [User input date] and select the Date/Time data type. Click OK.
.

 

[speedracer1971]

You fixed it!

That works! Thanks a Ton for your Access knowledge! You saved me a lot of aggrevation.

 

[ScottGem]

Frankly I would not use a paramter query for this. Instead I would create a form with an unbound text box for the user to enter a date. I would then add a column to my query with the expression:

TransAge: Forms!formname!controlname-[ConsignedDate]

then set the criteria for that column to:

>=10

 

[EMP]

Frankly I would not use a paramter query for this. Instead I would create a form with an unbound text box for the user to enter a date.

"Using an unbound text box for the user to enter a date" is considered more user-friendly. But still it's a parameter query.

.

I would then add a column to my query with the expression:

TransAge: Forms!formname!controlname-[ConsignedDate]

then set the criteria for that column to:

>=10

That would return all the records where Forms!formname!controlname-[ConsignedDate] >=10, but it would not count how many records are with Forms!formname!controlname-[ConsignedDate] >=10

I don't think it is a solution to the question.

^

 

[ScottGem]

I disagree. A parameter query is one where the parameter is prompted for as part of running the query. By your definition any filtered query would be a parameter query which I don't beleive is the accepted terminology.

After rereading the question I'm not sure of what the result being asked for is. The question starts off by asking for a select query to return all records where the ConsignedDate is 10 or more days prior to a specified date. That's what my solution does. If a count of those records is needed, there are several ways to obtain that count from the filtered records.

 

[Rich]

I disagree. A parameter query is one where the parameter is prompted for as part of running the query.

Well then that's not correct either, you have to specifically define the parameters for Crosstab queries whether prompted or not.

 

[ScottGem]

Well then that's not correct either, you have to specifically define the parameters for Crosstab queries whether prompted or not.

I'm not following you, Rich. Defining criteria in a query, whether its a crosstab or not is not a parameter query. A parameter query, from what I know, is defined as a query where variable data is supplied to the query at run time. I don't include a setup where the criteria is read from a form in that since the criteria is supplied before the query is run.

 

[EMP]

After rereading the question I'm not sure of what the result being asked for is.

ScottGem,

It is in this sentence in the first paragraph:-

"I would like to sum all record older than 10 days old from a user inputted date."

.

By your definition .....

No, it is not my definition. It is Access's.

Here's an example of a crosstab query:-
PARAMETERS [forms]![Form1]![txtStartDate] DateTime, [forms]![Form1]![txtEndDate] DateTime;
TRANSFORM Sum([tblData].[Sales]) AS SumOfSales
SELECT [tblData].[Area], Sum([tblData].[Sales]) AS [Total Of Sales]
FROM tblData
WHERE [Date] Between [forms]![Form1]![txtStartDate] And [forms]![Form1]![txtEndDate]
GROUP BY [tblData].[Area]
PIVOT Format([Date],"mmm");


Without declaring the data type of the two PARAMETERS [forms]![Form1]![txtStartDate] and [forms]![Form1]![txtEndDate], the query won't work.

In the query, Access describes the two unbound text boxes as parameters.

^

 

[ScottGem]

This assumes that you put your criteria into the Crosstab. While that certainly is possible its not the way I would do it. I would do my filtering in an interim query. The source of the crosstab would already be filtered to the records I wanted and not need any parameters.

I still feel the generally used definition of a parameter query is one where the user is prompted for data at run time. If the query pulls data from values that exist on a form, that's not prompting at run time.

As for the Sum issue. Its not real clear what is being summed. A sum is a total of values, yet no value was specified. What it appears is wanted is not a sum but a count. The IIF expression used is returning a 0 or 1 depending on whether the # of days meets the criteria. It then sums the returned value. Personally I think it more efficient to count the filtered records, which is what my solution offered.

Neither solution is right or wrong. They would both work. But I don't like parameter queries since they are inflexible and advise against them. If all that was wanted was a count of records that satisfy the criteria then using a Dcount would also work and no actual query would be necessary.

 

[EMP]

Its not real clear what is being summed.

It's very clear. The poster just wanted to make this expression work in a Totals Query:-

Records over 10 days: Sum(IIf([user input date]-[Consigned Date]>=10,1,0))

and Jon K provided solutions that worked (prior to your post which didn't do any counting or summing at all.)

^

 

[ScottGem]

It's very clear. The poster just wanted to make this expression work in a Totals Query:-

Records over 10 days: Sum(IIf([user input date]-[Consigned Date]>=10,1,0))

and Jon K provided solutions that worked (prior to your post which didn't offer any counting or summing.)

^

Sorry, but I have to disagree. I may be a stickler for terminology (improper use of terminology can often cause confusion) but this is not a Sum. What is being described is a count, not a sum. A sum is artificially being created using an IIF. I believe it would be more efficient to filter the records and use a Group By to get a count or just look at the number of records returned from the filtered recordset.

Again the initial request was for a Select query. A select query just selects records that meet a criteria.

I never indicated the original solution wouldn't work. I said simply that I would do it differently and offered an alternative. I firmly believe that the method the asker was using was a jury-rig that arrived at the solution but in a more roundabout way (using an artificial summing) when a more direct was available.

 

[EMP]

Again the initial request was for a Select query. A select query just selects records that meet a criteria.

I have to disagree.

Rcords over 10 days: Sum(IIf([user input date]-[Consigned Date]>=10,1,0))

When there is the aggregate function Sum() there, it has to be a Totals Query. And the poster didn't use the parameter [user input date] in the criteria.

.

What is being described is a count, not a sum.

You have confused count with sum. You can count the records, but you can't count the expression IIf([user input date]-[Consigned Date]>=10,1,0)

What the poster wanted was to sum the 1's and 0's, which, as Jon has shown, can be done.

^

 

[ScottGem]

You have confused count with sum. You can count the records, but you can't count the expression IIf([user input date]-[Consigned Date]>=10,1,0)

What the poster wanted was to sum the 1's and 0's, which, as Jon has shown, can be done.

^

No, I don't believe I have confused anything. What you aren't taking into account is what those 1s and 0s represent. That is the key issue. As I said earlier; "The IIF expression used is returning a 0 or 1 depending on whether the # of days meets the criteria." The IIF is being used to perform a count, there can be no question about that. The IIF is saying; if the difference is >= top 10 then add a 1 (count it), otherwise add a 0 (don't count it). While the poster may have said he wanted a sum, what he really meant was a count. An example of using the wrong terminology causing confusion. There are other (better IMHO) ways to do a count.

 

[Jon K]

I agree with EMP. It is a Totals Query.

In a Totals Query, we use Count to count all the records in a group.

To count only part of the records in a group in a Totals Query, we can simply Sum an expression that returns True (-1) or False (0). So if we want to, we can use Sum directly on the expression without using IIF:

Records over 10 days: -Sum([User input date]-[Consigned Date]>=10)


The using of IIF is not an issue here. What is important is how to make Access recognise the user inputted date as a Date/Time data type so as to avoid the error when the query is run. As I have posed, we can use the CDate() function or the DateValue() function on the parameter value, or declare the data type of the parameter in the query.
.

 

[ScottGem]

I agree with EMP. It is a Totals Query.

In a Totals Query, we use Count to count all the records in a group.

To count only part of the records in a group in a Totals Query, we can simply Sum an expression that returns True (-1) or False (0). So if we want to, we can use Sum directly on the expression without using IIF:

Records over 10 days: -Sum([User input date]-[Consigned Date]>=10)


The using of IIF is not an issue here. What is important is how to make Access recognise the user inputted date as a Date/Time data type so as to avoid the error when the query is run. As I have posed, we can use the CDate() function or the DateValue() function on the parameter value, or declare the data type of the parameter in the query.
.

I'm not saying it isn't a Totals (Group By) query. Nor am I saying that using the Sum in conjunction with the IIF is the wrong way of doing it. But you can still use a Count by setting criteria to just return the records you want and to count them. Its another, viable way of doing it. I think its more efficient.

As for the user input of the date, I still feel using a form is a better way of doing things.

I've been defending my answer because it appears that its being attacked as being incorrect or erroneous at worst or not as good as the original answer at best. I believe my solution was at least as good as the original answer. I believe that some parts of it, at least, offered a better way of doing things. That doesn't mean the original solution was wrong or bad.