Subtract dates in subform

Originally Posted by Rabbit

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

Originally Posted by 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

Originally Posted by Rabbit

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.

Originally Posted by Rabbit

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.

Originally Posted by Rabbit

In the query did you give it an alias?

Originally Posted by 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.

Originally Posted by dragonlady

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.

Originally Posted by 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] & "#"))

Originally Posted by dragonlady

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.

Originally Posted by Rabbit

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.

Originally Posted by dragonlady

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

Originally Posted by Rabbit

I said DateDiff, not DMax.

Originally Posted by dragonlady

Originally Posted by Rabbit

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

Originally Posted by Rabbit

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..

Originally Posted by 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.

Originally Posted by dragonlady

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

Originally Posted by dragonlady

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?

Originally Posted by Rabbit

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.

Originally Posted by dragonlady

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?

Originally Posted by Rabbit

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.

Originally Posted by dragonlady

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.