Count records and show total on a form

[boblarson]

It's Monday for me. I put the stuff on the wrong side of the right paren:

=Sum(IIf([Status]="4" And [Invoiced]="0",1,0))

Sorry

 

[fenhow]

Bob, thanks so much. I dont want to be a pain and I really appreciate your time and skill, when I put this in the control

=Sum(IIf([Status]="4" And [Invoiced]="0",1,0))

and run the form both this one and the =Sum(IIf([Status]="1" And DateDiff("d",[AssnDate],Date())>30,1,0))]

show "error"

Suggestions?

Fen

 

[boblarson]

It's because you don't have the field in the switchboard's recordset. Why don't you change the query underlying the form to just Select * From [Order Details] so all of the fields are available to you.

 

[boblarson]

Also, I'm not sure what the criteria in the name fields of

Like "*" Or Is Null

is going to get you. It seems like that would select everything that either has a value or doesn't and it is the same as leaving it blank.

 

[fenhow]

Thanks Bob,

How do I do this?

It's because you don't have the field in the switchboard's recordset. Why don't you change the query underlying the form to just Select * From [Order Details] so all of the fields are available to you.

Fen

 

[boblarson]

 

[fenhow]

Thanks Bob,

Did you try this on your side? Does it work because I am still getting an error on both of those fields?

Fen

 

[boblarson]

Yep, it worked. Hold on I'll repost it for you.

 

[boblarson]

Here you go.

One of the other things is for your True/False field I had to use = False instead of = 0.

 

[fenhow]

Bob, Thanks a bunch for your help. I do sincerely appreciate it.

I have another question, In my query to complete all of my "Snapshot" numbers I needed to add another table "Basic Close Info" when I added this my record count went from 705 to 596.

596 is how many records are in the "Basic Close Info" table. By doing this I am loosing records 109 to be exact?

Is there a way to overcome this?

In essence, running the Sum on total open orders will be inaccurate with the "Basic Close Info" table added to the query?

Fen

 

[peterlee516]

Hi Bob,

I've been reading through this thread and others but can't seem to find the answer. I know it's simple but I just don't know the syntax.

I'm just trying to count the records based on the value in a field. Specifically count how many records in the subform where "Type" = Client and where "Type" = BU Head. Only 2 choices.

I tried putting =Count([Type]="Client") in a textbox in the form header and another textbox on the form but it just gave me the total count of records on the subform.

What am I doing wrong?

Thank you in advance for you assistance.

 

[boblarson]

Use this instead Peter:

=Sum(IIf([Type]="Client",1,0))

And, you should change your field name as TYPE is a reserved word in Access and should NOT be used as a field or object name.

 

[peterlee516]

It worked! And thanks for the extra tip! I'll make the change!

 

[jackshield]

I know this is an old post, but I'm needing to do something similiar, and I'm not an access guy at all.

I have a date field, used to reresent a verification process of procedures in our database.

in one report, I have it displayed as a column, where null entries print in red background and populated entries print with green background.

I would like to add a counter at the top of the column that would display some kind of numerical value of the null vs populated entries, whether in fractional or pecertage format.

the field is set as "date" value
the field is named "VERIFIED"

all I need is the proper syntax to make the expression work, can anyone help?

 

[jackshield]

I can submit or post file if that helps?