Subtract dates in subform

[dragonlady]

I have a table with the fields -
Income_Date Income.
19/3/07 $320
21/2/07 $200
27/1/07 $300

In a continuous subform, I need to show for each income period the average weekly income on each line. As far as I can figure so far, I need to use the datediff function to calculate the difference between the two dates, and then loop to the next record and repeat. I just have no idea how to achieve this.

Please can somebody help me with the code I need to use, and how to use it? In case it's not already obvious painfully, I'm new to this - any suggestions would be much appreciated before I go nuts.

[Rabbit]

How do you calculate the weekly average income?

[dragonlady]

I'd subtract date 2 from date 1, convert to weeks and divide into the income. So in the example -

Income_Date Income.
19/3/07 $320
21/2/07 $200
27/1/07 $300

19/3/07 - 21/2/07 = 27 days, say 3.8 weeks. Divide $320 by 3.8 for average weekly income. I could do it on a spreadsheet!

[Rabbit]

Difficult, you'll need either a subquery or a DMax to pull the last date.
DMax is easier to implement but less efficient.

[dragonlady]

Difficult, you'll need either a subquery or a DMax to pull the last date.
DMax is easier to implement but less efficient.

Would you be able to be a bit more specific please, I'm new to this. I thought I'd need to use datediff, but can't figure that out either. I don't know what code to use or how to use it ...

[Rabbit]

Something along the lines of

SELECT DateDiff("d", [Income_Date], DMax("Income_Date", "TableName", "Income_Date<" & [Income_Date])) As Days
FROM TableName;
 

[dragonlady]

Thanks heaps Rabbit for your time, I appreciate it. I've put that in as the control source for an unbound field in the subform, and get the following error message:
"the syntax of the subquery is incorrect. Check the subquery's status and enclose the subquery in parenthesis". Do you have any other clues?
Cheers

[Rabbit]

Thanks heaps Rabbit for your time, I appreciate it. I've put that in as the control source for an unbound field in the subform, and get the following error message:
"the syntax of the subquery is incorrect. Check the subquery's status and enclose the subquery in parenthesis". Do you have any other clues?
Cheers

I was under the impression you were looking for a query.

You can't use a SQL statement as the control source of a control. You can use them for the row sources and record source of combo/list boxes and forms respectively.

If you're only looking to calculate the value for the one record you're viewing then you only need the formula and can forgo the SQL statement.

In the end, the tables are poorly designed. Ideally you would want each record to have both a start date and an end date.

[dragonlady]

I was under the impression you were looking for a query.

You can't use a SQL statement as the control source of a control. You can use them for the row sources and record source of combo/list boxes and forms respectively.

If you're only looking to calculate the value for the one record you're viewing then you only need the formula and can forgo the SQL statement.

In the end, the tables are poorly designed. Ideally you would want each record to have both a start date and an end date.

When the operator is entering the latest collection date, there is no way of knowing the last collection date, aka the start date, without going back and looking it up manually.
The value to be calculated would be on each row of a continuous subform, to give a continuous history of income at a glance.
Thanks again for your input, I'll give it another go.

[dragonlady]

Hi again,

I've put in the following code as the control source

=DateDiff("d",[Collection_Date],DMax("Collection_Date","M_Income","Collection_Dat e<" & [Collection_Date]))

and it's returning a blank field.
There is an #Error on the line for a new record, which I suppose is to be expected.

[dragonlady]

I was under the impression you were looking for a query.

You can't use a SQL statement as the control source of a control. You can use them for the row sources and record source of combo/list boxes and forms respectively.

If you're only looking to calculate the value for the one record you're viewing then you only need the formula and can forgo the SQL statement.

In the end, the tables are poorly designed. Ideally you would want each record to have both a start date and an end date.

I've tried putting the SQL statement in as a query, and I get the same results, a series of blank fields.

[Rabbit]

In the query did you give it an alias?

[dragonlady]

In the query did you give it an alias?

No, I didn't. What is that and how do I do it?

[Rabbit]

In design view it would be:
Alias: Formula

In SQL it would be:
Formula As Alias

The Alias is to give the field a name. Functions can't be the name of the field.

[dragonlady]

In design view it would be:
Alias: Formula

In SQL it would be:
Formula As Alias

The Alias is to give the field a name. Functions can't be the name of the field.

I'm sorry to be so thick, I have no idea what I'm doing here. Could you please be utterly specific, what do I type in, and where? I have the subform, I've put the formula in as the control source for a text box. I don't know what you mean to do with the alias from there.

[Rabbit]

I'm sorry to be so thick, I have no idea what I'm doing here. Could you please be utterly specific, what do I type in, and where? I have the subform, I've put the formula in as the control source for a text box. I don't know what you mean to do with the alias from there.

Control source? You do realize we've been talking about a query this whole time right? Go up a few posts.

[Rabbit]

After some testing, I think I've figured out why, need to delimit the value as a date using #.

[code]
DateDiff("d", [Income_Date], DMax("Income_Date", "TableName", "Income_Date<#" & [Income_Date] & "#"))

[dragonlady]

After some testing, I think I've figured out why, need to delimit the value as a date using #.

[code]
DateDiff("d", [Income_Date], DMax("Income_Date", "TableName", "Income_Date<#" & [Income_Date] & "#"))

Thanks Rabbit, I think that's getting closer ... I get a result now, just not the right one, eg:

Line 1 3.4.07 -32
Line 2 9.2.07 54
Line 3 4.1.07 85

Or

Line 1 30.3.07 -2
Line 2 23.3.07 -4
Line 3 16.3.07 -4
Line 4 2.3.07 -28
Line 5 22.2.07 -2
Line 6 16.2.07 -6
Line 7 9.2.07 54

I thought it would be easy to figure out what the results mean and adjust the formula accordingly, but I can't make any sense of the results.

In the first example, if I change the date from 3.4.07 to 9.2.07 it changes from -32 to 54, when the result should be 0.

[Rabbit]

Thanks Rabbit, I think that's getting closer ... I get a result now, just not the right one, eg:

Line 1 3.4.07 -32
Line 2 9.2.07 54
Line 3 4.1.07 85

Or

Line 1 30.3.07 -2
Line 2 23.3.07 -4
Line 3 16.3.07 -4
Line 4 2.3.07 -28
Line 5 22.2.07 -2
Line 6 16.2.07 -6
Line 7 9.2.07 54

I thought it would be easy to figure out what the results mean and adjust the formula accordingly, but I can't make any sense of the results.

In the first example, if I change the date from 3.4.07 to 9.2.07 it changes from -32 to 54, when the result should be 0.

Try flipping the second argument of DateDiff with the third argument.

Basically what it's doing is calculating the difference in days between the date of the current record with the date of the next highest date. It does not necessarily use the next record. If there are going to be records with the same dates then that adds a whole other layer of complexity.

[dragonlady]

Try flipping the second argument of DateDiff with the third argument.

Basically what it's doing is calculating the difference in days between the date of the current record with the date of the next highest date. It does not necessarily use the next record. If there are going to be records with the same dates then that adds a whole other layer of complexity.

Did you mean swap "TableName", and "Income_Date<#" & [Income_Date] & "#"))?
That gives a jet error.

[Rabbit]

Did you mean swap "TableName", and "Income_Date<#" & [Income_Date] & "#"))?
That gives a jet error.

I said DateDiff, not DMax.

[dragonlady]

I said DateDiff, not DMax.

=DateDiff("d",DMax("M_Income","Income_Date","M_Income","Income_Date<#" & [Income_Date] & "#"),[Income_Date]) 

Sorry, told you I was a bit thick. Is this what you meant - it returns #Error

[Rabbit]

=DateDiff("d",DMax("M_Income","Income_Date","M_Income","Income_Date<#" & [Income_Date] & "#"),[Income_Date]) 

Sorry, told you I was a bit thick. Is this what you meant - it returns #Error

You have an extra parameter in your DMax, specifically the first one shouldn't be there.

[dragonlady]

You have an extra parameter in your DMax, specifically the first one shouldn't be there.

Oops, must have done copy and past instead of cut and paste - too early in the morning.

That gives a result now, it's still wrong though. Its the same as the previous results posted, but the positives and negatives are reversed.

Basically what it's doing is calculating the difference in days between the date of the current record with the date of the next highest date. It does not necessarily use the next record. If there are going to be records with the same dates then that adds a whole other layer of complexity..

I would have thought there should be no circumstance where there are negative results, no matter which date it's using.
I've added and subtracted and can't find a way to get these same results.

[Rabbit]

The only thing I can think of is that your Date field is a text field rather than a date field because I tested it and it works.

[dragonlady]

The only thing I can think of is that your Date field is a text field rather than a date field because I tested it and it works.

No, the date field is definitely set as a date field.

[dragonlady]

No, the date field is definitely set as a date field.

Hi Rabbit, I've had a play, set up a new database table with some data, and entered your formula. What it does is give me the number of days between the current record, and the first record in the table, not the very next record in the table, eg
1.4.07 90
1.3.07 59
1.2.07 31
1.1.07

What I need is the number of days from the immediately previous record, for that particular site (this control is on a subform in the Sites table, so only the records for that particular site display).

So I guess something unexpected is happening in my live data, because is has multiple sites and multiple dates?

[Rabbit]

Hi Rabbit, I've had a play, set up a new database table with some data, and entered your formula. What it does is give me the number of days between the current record, and the first record in the table, not the very next record in the table, eg
1.4.07 90
1.3.07 59
1.2.07 31
1.1.07

What I need is the number of days from the immediately previous record, for that particular site (this control is on a subform in the Sites table, so only the records for that particular site display).

So I guess something unexpected is happening in my live data, because is has multiple sites and multiple dates?

It's possible, I tested it with just a table with a date field and it worked fine. It takes the date of the record it's currently on, gets the next highest date after the current record and finds the difference in days. Like I said before, it's not necessarily going to get the next record because it skips all records that are the same date.

Also, the fact that you need it by site brings up more complications which you should have stated at the beginning, it changes things.

[dragonlady]

It's possible, I tested it with just a table with a date field and it worked fine. It takes the date of the record it's currently on, gets the next highest date after the current record and finds the difference in days. Like I said before, it's not necessarily going to get the next record because it skips all records that are the same date.

Also, the fact that you need it by site brings up more complications which you should have stated at the beginning, it changes things.

Sorry, I thought I did mention that when I said I was using it in a subform.

Any further suggestions on where to go from here?

[Rabbit]

Sorry, I thought I did mention that when I said I was using it in a subform.

Any further suggestions on where to go from here?

The subform doesn't have anything to do with this. I'm talking about site. You're going to have to account for site in the third parameter of the DMax function so that you're only returning dates for the same site.

[dragonlady]

The subform doesn't have anything to do with this. I'm talking about site. You're going to have to account for site in the third parameter of the DMax function so that you're only returning dates for the same site.

Ok, I've got this far, but still giving me weird results. Can you see where I'm going wrong with this?

The income and dates are in a table called (M_Income), which is displayed in a continuous subform on a form named (F_Sites) which is updating the table M_Sites. The link field is Site_ID between the M_Sites and M_Income tables.

=IIf(Len([Collection_Date] & "")>0,DateDiff("d",DMax("Collection_Date","M_Incom e","Collection_Date< #" & [Collection_Date] & "# And Site_ID=" & [Site_ID]),[Collection_Date]),Null)

The IIf is so it doesn't give an error on the new record line.

[Rabbit]

Ok, I've got this far, but still giving me weird results. Can you see where I'm going wrong with this?

The income and dates are in a table called (M_Income), which is displayed in a continuous subform on a form named (F_Sites) which is updating the table M_Sites. The link field is Site_ID between the M_Sites and M_Income tables.

=IIf(Len([Collection_Date] & "")>0,DateDiff("d",DMax("Collection_Date","M_Incom e","Collection_Date< #" & [Collection_Date] & "# And Site_ID=" & [Site_ID]),[Collection_Date]),Null)

The IIf is so it doesn't give an error on the new record line.

I don't know what that & "" is for, that probably doesn't have to be there. The rest looks OK as long as the Site_ID is of a numeric data type.

But the problem is that you can't use this on a continuous subform. You have to do it through a query and then you can show it in a subform by including the field. The problem is that unbound controls always display the same thing on a continuous subform. They don't have seperate values depending on what record they belong to.