Caption display on label 1

[rmd62163]

Is it possible to have a label caption display the number of records on a form to where you can count the records based on a field not being empty or it's null or not null values?
I can't figure this one out. I can do the

Private Sub Form_Current()

LblCount.Caption = (Recordset.RecordCount)

EndSub

But not a count based on a fields value. Can someone point me in the right direction please! If it is possible.

 

[randy700]

If this doesn't work:

LblCount.Caption = [i]dlookup code[/i]

You could always use a text box formatted to look like a label.


Randy

 

[PHV]

I'd replied yesterday but my post seems to be vanished ...
Anyway I just said this:
Have a look at the DCount function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rmd62163]

Thanks to both of you. I could not get the Dlookup to do what I needed randy. probably due to lack of know how. But I looked further into the Dcount and was able to apply it with

DCount("[accession number]", "daily exam log")

, but still can not have it only count the current filtered recordset. It counts the table as a whole. At least I'm headed in the right direction, thanks to both of you again. And randy what did you mean by "You could always use a text box formatted to look like a label"? Curious as to where you are going with that, may be I can try and play around with that idea.

Thanks again

 

[PHV]

can not have it only count the current filtered recordset
What about the 3rd parameter of the DCount function ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[randy700]

Instead of a label, use a text box.
Put your code in the text box source.
Play with the formatting until it has the appearance you desire.


Randy

 

[rmd62163]

Sry guys. can't get the 3rd parameter to do it either. Tried,

LabelAcnt.Caption = DCount("[accession number]", "[daily exam log]", "[accession number] ='" & Me.Filter & "'")

to try and filter off the current form but no go. I just don't think it's possible to apply a Dcount to a form that is filtered, at least that I can come up with. Tried your text box method also Randy but that was pretty much the same results as applying the caption to a label. I'm gonna play around with the Dlookup and see if I can make it work. Good thing this is just my curiosity on rather this can be done or I might be in a bad way. Thanks again! If I get it to work I'll let you know. And if you can think of some other way please let me know.

 

[PHV]

What is the value of Me.Filter ?
I'd try this:
LabelAcnt.Caption = DCount("[accession number]", "[daily exam log]", Me.Filter)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rmd62163]

That gives me a Null or zero return, but no errors. The same return as

LabelAcnt.Caption = DCount("[accession number]", "[daily exam log]", "[accession number] ='" & Me.Filter & "'")

does. Good thought though. Thanks.

 

[PHV]

Again, what is the value of Me.Filter ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

Another try, provided that the form is bound to a query or a table and that [accession number] is a field there:
LabelAcnt.Caption = DCount("[accession number]", Me.RecordSource, Me.Filter)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

If you want to count the number of records in a form's record source where a specific field in the record source is not null, use a text box with a control source of:

=Count([Your Field Name Here])

Duane
Hook'D on Access
MS Access MVP

 

[rmd62163]

Sry for the delay gents. The value of the Me.filter is as follows.
[Daily Exam Log]![Date_DailyExamLog]=[Enter date mm/dd/yyyy]
and
[Daily Exam Log]![Date_DailyExamLog]=Date()

Filter is changed with command button entry.

PHV I tried the LabelAcnt.Caption = DCount("[accession number]", Me.RecordSource, Me.Filter) But gives a zero return with no errors. But when I removed all filters from the form it returns the total record count. And if I apply a filter by right clicking in the date field on the form say like last month. It works as expected.
Sure glad there is more then one way to approach desired results Here have ago at the attachment if you wish.

Dhookom thanks, "=Count([Your Field Name Here]) " that gives a total record count, which is not what I am going for. I am trying to count the current filtered records.

 

[rmd62163]

dhookom

About what I said on the previous post I was wrong. It does display the "is not null" only. Sorry don't know what I done to get it wrong on the first try. And it even works when filtered. man do I feel dumb now! Thanks to the both of you for everything. I'm still gonna keep trying to get the other method to work just to see if I can figure it out.