Solved

Formula: show total only if certain company is choosen

Posted on 2011-09-21
5
316 Views
Last Modified: 2012-06-21
Hi-
Access 2000
Preface: User opens a form, chooses company, enters date range and a report appears. This formula is part of the report:

=(0.25)*Sum(IIf([PrintLessCode]="P2S",1,0))+Sum(IIf([PrintLessCode]="P2U",1,0))+Sum(IIf([PrintLessCode]="S2S",1,0))

I only want this formula to show up on the report only if a certain company is chosen. We have 20+ companies the user can choose from so to make it simpler let's say we only have the following company names:

Acme1
Acme2
Acme3

If the user chooses Acme 1, then show the formula on the report. If they choose Acme2 or Acme3 then don't have anything show up on report.

When I try the formula below, I get "expression contains invalid syntax, you may have entered operand w/o operator.

=IIf([CustomerName]=”Acme1”,(0.25)*Sum(IIf([PrintLessCode]="P2S",1,0))+Sum(IIf([PrintLessCode]="P2U",1,0))+Sum(IIf([PrintLessCode]="S2S",1,0)),””)

What seems like it should  be easy is giving me trouble- thanks!!!
0
Comment
Question by:wgraphics
5 Comments
 
LVL 19

Expert Comment

by:Eric Sherman
ID: 36575063
<<<<I only want this formula to show up on the report .....>>>>>

Can you explain a bit further.  I'm not sure what you mean by show up on the report.

ET
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 200 total points
ID: 36575074
why not just hide/unhide the textbox if a company was chosen or not

me.txtboxName.visible= [CustomerName] & ""<>""
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 300 total points
ID: 36575163
I agree with capricorn1,

Use the Format event of the section of the report where the control is where that calculation is performed to hide it.  Probably the detail section, so it would look like:

Private Sub Detail_Format

    me.txtboxName.visible = ([CustomerName] = "Acme1")

End If
0
 

Author Closing Comment

by:wgraphics
ID: 36575598
Thank you so much. It took me awhile but using your instructions I was able to figure it out. I do not have any formal training so I truly appreciate your patience- often times what I think should be a simple task ends up having to be done in an area I am not familiar with!
Have a great day!
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 36575623
wgraphics,

Glad to help.  That is why we are here, to help others learn.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Delete QueryDef IF it Exists: Access VBA 5 36
Access Update Query 1 20
SQL Group on First occurrence 9 25
Progress bar in access form 10 19
Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

778 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question