Problem with code on report

[Guest]

Table name is "Main"
Fields I am using to do the calc/query on: "Last_Name" and "Company_Room"
Report data source is Table.Main

To get a total count of rooms, I did: DCount(*) in text box and works fine.

Now I want to have a few fields that show how many rooms each company has
and how many of them are occupied. If I count the number of records where
Company_Room = "A", then I should get total number of rooms for Alpha Company.
If I want to see how many rooms are occupied by A then Company_Name = "A"
and Last_Name is not null would give it do me.

Can someone help me? I've tried everyway my limited knowledge will let me
and I can't get it to work.

Thanks!

 

[Guest]

You can use something like

=Sum(IIf([Room]="A" And [Last_name] Is Not Null, 1 , 0))

You can use the sum and iif with any criteria , and just sum 1 when the
criteria is true

 

[Guest]

I didn't even think of that approach! That's why your the MVP!

Thanks!

You can use something like

=Sum(IIf([Room]="A" And [Last_name] Is Not Null, 1 , 0))

You can use the sum and iif with any criteria , and just sum 1 when the
criteria is true

--
Good Luck
BS"D

Table name is "Main"
Fields I am using to do the calc/query on: "Last_Name" and "Company_Room"
Report data source is Table.Main

To get a total count of rooms, I did: DCount(*) in text box and works fine.

Now I want to have a few fields that show how many rooms each company has
and how many of them are occupied. If I count the number of records where
Company_Room = "A", then I should get total number of rooms for Alpha Company.
If I want to see how many rooms are occupied by A then Company_Name = "A"
and Last_Name is not null would give it do me.

Can someone help me? I've tried everyway my limited knowledge will let me
and I can't get it to work.

Thanks!

 

[Guest]

Not an MVP, but thanks for the thought,
I have a long way before I'll become one.

--
Good Luck
BS"D

I didn't even think of that approach! That's why your the MVP!

Thanks!

You can use something like

=Sum(IIf([Room]="A" And [Last_name] Is Not Null, 1 , 0))

You can use the sum and iif with any criteria , and just sum 1 when the
criteria is true

--
Good Luck
BS"D

Table name is "Main"
Fields I am using to do the calc/query on: "Last_Name" and "Company_Room"
Report data source is Table.Main

To get a total count of rooms, I did: DCount(*) in text box and works fine.

Now I want to have a few fields that show how many rooms each company has
and how many of them are occupied. If I count the number of records where
Company_Room = "A", then I should get total number of rooms for Alpha Company.
If I want to see how many rooms are occupied by A then Company_Name = "A"
and Last_Name is not null would give it do me.

Can someone help me? I've tried everyway my limited knowledge will let me
and I can't get it to work.

Thanks!

 

[Guest]

Have a new problem. When I use the code, it works fine, but I get continuous
records showing on the report. I just want one single page that lists the 14
companies total rooms assigned, vacant, and occupied. How do I get it not to
display the records as it counts them?
Thanks!

Not an MVP, but thanks for the thought,
I have a long way before I'll become one.

--
Good Luck
BS"D

I didn't even think of that approach! That's why your the MVP!

Thanks!

You can use something like

=Sum(IIf([Room]="A" And [Last_name] Is Not Null, 1 , 0))

You can use the sum and iif with any criteria , and just sum 1 when the
criteria is true

--
Good Luck
BS"D


:

Table name is "Main"
Fields I am using to do the calc/query on: "Last_Name" and "Company_Room"
Report data source is Table.Main

To get a total count of rooms, I did: DCount(*) in text box and works fine.

Now I want to have a few fields that show how many rooms each company has
and how many of them are occupied. If I count the number of records where
Company_Room = "A", then I should get total number of rooms for Alpha Company.
If I want to see how many rooms are occupied by A then Company_Name = "A"
and Last_Name is not null would give it do me.

Can someone help me? I've tried everyway my limited knowledge will let me
and I can't get it to work.

Thanks!

 

[Guest]

In that case you need to create a group by query, that counts the records and
returns one line for each company.

And then base the report on this query

--
Good Luck
BS"D

Have a new problem. When I use the code, it works fine, but I get continuous
records showing on the report. I just want one single page that lists the 14
companies total rooms assigned, vacant, and occupied. How do I get it not to
display the records as it counts them?
Thanks!

Not an MVP, but thanks for the thought,
I have a long way before I'll become one.

--
Good Luck
BS"D

I didn't even think of that approach! That's why your the MVP!

Thanks!

:

You can use something like

=Sum(IIf([Room]="A" And [Last_name] Is Not Null, 1 , 0))

You can use the sum and iif with any criteria , and just sum 1 when the
criteria is true

--
Good Luck
BS"D


:

Table name is "Main"
Fields I am using to do the calc/query on: "Last_Name" and "Company_Room"
Report data source is Table.Main

To get a total count of rooms, I did: DCount(*) in text box and works fine.

Now I want to have a few fields that show how many rooms each company has
and how many of them are occupied. If I count the number of records where
Company_Room = "A", then I should get total number of rooms for Alpha Company.
If I want to see how many rooms are occupied by A then Company_Name = "A"
and Last_Name is not null would give it do me.

Can someone help me? I've tried everyway my limited knowledge will let me
and I can't get it to work.

Thanks!