Calculate Days (1 Viewer)

[rgreene]

I want to calculate the numbers of days from my [Date] field to todays date. BUT ONLY if there is no value in the [DDate] field.
If there is a date in the [DDate] field then calculate the number of days between [Date] and [DDate]

Thanks,
Rick

 

[Fuga]

Don´t know if this will work, just thinking out loud really

IIF(isnull([ddate]);DateDiff("d", [date], date());DateDiff("d", [date], [ddate])

Fuga.

 

[rgreene]

I get

Compile error:


Syntax error

 

[Fuga]

Ok, now I checked it out a little more:

If you put this in the field row in your query, I think it should work.


Calculated field: IIF(IsNull([ddate]);DateDiff("d";[Date];Date());DateDiff("d";[date];[ddate]))

By the way, the pros on this forum always tells me it´s not a good idea to name your fields "date", because it´s a reserved word.

Hope it works

Fuga.

 

[rgreene]

This is what I have now

IIf (IsNull(Forms![frmdischarge]![DDate])), DateDiff("d", [ADate], Date), DateDiff("d", [ADate], Forms![frmdischarge]![DDate])

but I keep getting errors can't find forms 'frmdischarge'. there is a frmdischarge and I've checked spelling several times. Any suggestion?

 

[The_Doc_Man]

If the IIf is in the ControlSource property of a text box on the same form as the frmdischarge form, you don't need to specify the form.

If it IIf is in the underlying query, don't reference the form. Reference the query's recordsource if that is where ddate is to be found.

Using a data item that depends on a form absolutely requires that the form must be open at the time you reference the data item. Not just defined. Open. (And not in Design View, either.)

Let's say the record looks like this:

myTable:
- DDate (date field)
- ADate (date field)
- lots of other data

The query that would populate this item could be as simple as:

myQuery1 (based on myTable)
for the date difference query field, and with the line broken out into parts to show exact punctuation...

Expr1: IIf(
IsNull( [DDate] ),
DateDiff( "d", [ADate], Date() ),
DateDiff( "d", [ADate], [DDate] )
)

Then Expr1 should be a number of days.

Alternatively, if this is ONLY useful for a form then you could put the SAME EXACT IIF STATEMENT as the controlsource for the text box that shows the number of days on the form. Of course, you must set that text box to Locked=True if it is based on an expression.

 

[rgreene]

I tried putting in the control source of my field. I think part of my problem is that the [DDate] is in a different table/form then my [ADate].

I originally had it in the ON Click event. I knew that wasn't where I wanted it but I was trying it there to see if it would work. Since then I have moved it to the control source. Now my field displays the #Name? thing.

 

[rgreene]

Let me expalin in detail.

I have

tblAdmissions
ADate
Days
Lot of other stuff

tblDischarge
DDate
Lot of other stuff

Other Tables
Bunch of other stuff

and I have a frmadmission. This is a tab form and each tab is basically from a different table. On one of the tabs I have the ADate and a bunch of other stuff. On a different tab I have the Days and a few other things.

On the tab with the Days I want the Days field to reflect the number of days between either the DDate (which is in a completely different table and form) if there is one. If there isn't one then calculate the difference between ADate and date().

My frmadmissions control source is a query qryAdmissions.

Now I have created a Test query that pulls the ADate (from tblAdmissions) and DDate (from tblDischarge) and I added the
Expr1: IIf(IsNull([DDate]),DateDiff("d",[ADate],Date()),DateDiff("d",[ADate],[DDate])) in the next box on my query. Also the join properties is include ALL records from tblAdmissions and only those from tblDischarge where the join fields are equal.
and this works.

My understanding is that if I use it in control source of the Days field the the frmDischarge would have to be open since DDate is on that form. Plus that doesn't seem to work even if the frmDischarge is open. If I use it as a Module is the same true? Does the frmDischarge need to be open? If not can someone help me with the code.

Otherwise since the control source for frmAdmissions is qryAdmissions how do I tell 1 field (Days) within that form to use a different query?


CONFUSED?

Thanks to anyone who wants to tackle this and enlighten an ACCESS idiot.

Rick

 

[Rich]

Isn't it better to have the Admittance and Discharge dates in the same table as part of the same record?

 

[rgreene]

I guess I don't know.
I created one form for entering the data whan the child is admitted. When the child is discharged they don't need the info from the admitted form. So I created 2 different forms.

 

[Rich]

There have been many examples posted here from members working with this type of data, but I would still say the two dates belong in the same table and the discharge date entered as part of the record, assuming this has a primary key then you can use that to add relevant records to the discharge table, just use the after update event of the discharge date to open the discharge form. Assuming days is recording the actual length of stay this field becomes redundant since it can easily be calculated at any time in an unbound textbox