Thank you so much Tina. Very good!
I think I will go for the code to return the value I need so that
is useful code. Thanks a lot!
However, I think I also could use a query as a row source for
my sub form. Then I could have a calculated field that returns
a date only if myObjID=4, otherwise 0.
Then, in my subform I could have a text box with control source;
=Max([myDate ])
Oh, BTW, I wrote Min in stead of Max in my earlier posting.
This way I retrieve the right date or maximum date. But, I have
not considered speed or performance. That might be an issue here?
Sincerely
Jan T.
"tina" <
[email protected]> skrev i melding
well, first of all, i see that my previous post gave you the opposite
of
what you asked for; it should have been
=Sum(IIf([myObjID] = 4, 1, 0))
sorry about that, but it looks like you got the intent and caugth the
error - good job on both. your syntax for multiple conditions is correct,
good job again; the only issue is the the expression won't handle Leap
Year
correctly. suggest you try the following instead, as
=Sum(IIf([myObjID]=4 And [myDate ]<=DateAdd("yyyy", -1, Date());1;0))
Next challange is to return the Minium [myDate] if [myObjID] = 4 ?
How would I write a function here, if possible? ???
do you mean you want to find the minimum [myDate] value out of all the
records in the subform's current recordset? well, the easiest way would
probably be to run a DMin() function on the table or query used as the
form's RecordSource. but if you've applied a filter to the form at
runtime,
you may get inaccurate results with the DMin(). if form filtering is a
possibility, the following code will return the oldest date of the
subform's
current recordset, filtered or not, as
Public Function isMinDate()As Date
Dim rst As DAO.Recordset, dat As Date
Set rst = Me.RecordsetClone
rst.MoveFirst
dat = rst("myDate")
rst.MoveNext
Do
If rst("myDate") < dat Then dat = rst("myDate")
rst.MoveNext
Loop Until rst.EOF
rst.Close
Set rst = Nothing
isMinDate = dat
Exit Function
hth
Thank you so much! It worked perfectly.
What about two conditions? Would that also be possible to write?
Condition one: myObjID = 4
Condition two: myDate <= #02/14/09#
Here is how I solved it: =Sum(IIf([myObjID]=4 And
[myDate ]<=Date()-365;1;0))
... and that worked also
Next challange is to return the Minium [myDate] if [myObjID] = 4 ?
How would I write a function here, if possible? ???
Jan T.
"tina" <
[email protected]> skrev i melding
try
=Sum(IIf([myObjID] = 4, 0, 1))
to show the value in the mainform control, set the ControlSource as
=[NameOfSubform].[Form]![NameOfSubformTextbox]
make sure that NameOfSubform is the name of the subform control within
the
mainform, rather than the name of the subform form object in the
database
window. those two names may be the same or different. for more
information,
see
http://home.att.net/~california.db/instructions.html and look at
the
SubformControlName link.
hth
I use Access 2k and have a userform with a subform.
In my subforms footer section I have a text box "txtmyObjIdCount"
returning number of myObjID. = Count([myObjID]).
Now I want to know if it is possible to count only records where
myObjID is say = 4. Is that possible to achieve? It is easy in
Excel
where
you can use = COUNTIF - function. How can i do this in Access.
My final goal is then to have a Text Box in my Main Form having the
source set to = txtmyObjIdCount in the footer of the subForm.
Thank you for your help!
Sincerely
Jan T.
