finding last yes.

[Salomon Turgman]

In one of my tables I have a date field and a gym field which is yes/no
field. In the form for this table I want to display in a textbox the number
of days since the last date were the gym field was yes. Any ideas because I
have none...
Thanks

 

[Allen Browne]

In the Control Source of the text box, use DMax() to get the last date, and
DateDiff() to calculate how long ago that was:

=DateDiff("d", DMax("[MyDate]", "[MyTable]", "[GymField] = True"), Date())

 

[Salomon Turgman]

Im having trouble using the DMax function. Do I need the brackets and
quotations? MyDate would be the name of the date field? MyTable would be
the table name? do I need to put the last True in Quotes?

Thanks

In the Control Source of the text box, use DMax() to get the last date, and
DateDiff() to calculate how long ago that was:

=DateDiff("d", DMax("[MyDate]", "[MyTable]", "[GymField] = True"), Date())

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

In one of my tables I have a date field and a gym field which is yes/no
field. In the form for this table I want to display in a textbox the number
of days since the last date were the gym field was yes. Any ideas

because

I
have none...
Thanks

 

[Allen Browne]

Have assumed the table "MyTable", has fields:
MyDate Date/Time
GymField Yes/No

If GymField is a Text-type field, you need the quotes. Otherwise no.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Im having trouble using the DMax function. Do I need the brackets and
quotations? MyDate would be the name of the date field? MyTable would be
the table name? do I need to put the last True in Quotes?

Thanks

In the Control Source of the text box, use DMax() to get the last date, and
DateDiff() to calculate how long ago that was:

=DateDiff("d", DMax("[MyDate]", "[MyTable]", "[GymField] = True"), Date())

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

In one of my tables I have a date field and a gym field which is yes/no
field. In the form for this table I want to display in a textbox the number
of days since the last date were the gym field was yes. Any ideas

because

I
have none...
Thanks

 

[Salomon Turgman]

Thank you very much my friend. It works perfectly!!!

Have assumed the table "MyTable", has fields:
MyDate Date/Time
GymField Yes/No

If GymField is a Text-type field, you need the quotes. Otherwise no.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Im having trouble using the DMax function. Do I need the brackets and
quotations? MyDate would be the name of the date field? MyTable would be
the table name? do I need to put the last True in Quotes?

Thanks

In the Control Source of the text box, use DMax() to get the last

date,
and

DateDiff() to calculate how long ago that was:

=DateDiff("d", DMax("[MyDate]", "[MyTable]", "[GymField] = True"), Date())

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

In one of my tables I have a date field and a gym field which is yes/no
field. In the form for this table I want to display in a textbox the
number
of days since the last date were the gym field was yes. Any ideas because
I
have none...
Thanks