|
|
|
|
New Member
|
|
Aug 10, 2012, 08:20 AM
|
|
Microsoft Access 2010 IIF Function
The Subtotal field in my Expenses tbl contains the following expression:
IIf([Expenses]="Tuition/Fees" Or "Airfare" Or "Taxi" Or "Ground Transportation" Or "Registration Fee" Or "Miscellaneous" Or "Salary for the period",[OneTimeCost],([Rate]*[Duration]))
However, it is not producing the result I need.
The result from the truepart is accurate, but nothing is resulting from the falsepart.
i.e. I do not see the calculation for [Rate]*[Duration] in the results.
Any input would be greatly appreciated.
|
|
|
Computer Expert and Renaissance Man
|
|
Aug 10, 2012, 09:31 AM
|
|
OK, you are getting closer. But you have to fully qualify each condition. So it has to be:
IIf([Expenses]="Tuition/Fees" Or [Expenses]="Airfare" Or [Expenses]="Taxi" Or [Expenses]="Ground Transportation" Or [Expenses]="Registration Fee" Or [Expenses]="Miscellaneous" Or [Expenses]="Salary for the period",[OneTimeCost],([Rate]*[Duration]))
What I would recommend is that you create a lookup table for Expense types. And bunch all the OneTimeCost types together. So you can do it this way.
IIf([Expenses]<20 ,[OneTimeCost],([Rate]*[Duration]))
|
|
|
New Member
|
|
Aug 10, 2012, 09:43 AM
|
|
I keyed in the first expression, but I'm getting the same result as before. There is no return for the false part.
|
|
|
Computer Expert and Renaissance Man
|
|
Aug 10, 2012, 12:49 PM
|
|
Can you show me the whole SQL statement and attach an example of the results of the query?
|
|
|
New Member
|
|
Aug 10, 2012, 12:58 PM
|
|
I have not created any queries. Would I still be able to view a SQL statement?
The file is still not uploading.
|
|
|
Computer Expert and Renaissance Man
|
|
Aug 10, 2012, 01:42 PM
|
|
I'm confused. Where are you putting the IIF statement if not in a query?
Are you trying to attach a file here? What type of file and how big?
|
|
|
New Member
|
|
Aug 11, 2012, 05:38 AM
|
|
I'm using the IIF statements as an expression for the Subtotal field.
I want the subtotal to include [Rate]*[Duration] as well as OneTimeCost where the former is not applicable
The file is 3.0 MB. 1the file name extension is .bmp
|
|
|
Computer Expert and Renaissance Man
|
|
Aug 11, 2012, 05:49 AM
|
|
Aha, So you are using the Calculated field feature in Access 2010? I do not recommend using that feature, especially for complex expressions. It would probably work with the Rate*Duration, but the IIF() might not work.
Calculations should be done using a query. So you use Query Design mode and enter Subtotal: IIF(... ) in a blank column.
As for the BMP, 3 Mg is too large. The Attachments box tells you the max file sizes for attachments. Try saving the screen shot as a JPG or zip it.
|
|
|
New Member
|
|
Aug 11, 2012, 06:10 AM
|
|
I zipped it, but the file still exceeds the max file size. The compressed size of the file is 125KB.
Since the database I was working on is at work, I created a Sample Database with an Expenses tbl. I run the query, but I'm getting the same results
|
|
|
Computer Expert and Renaissance Man
|
|
Aug 11, 2012, 09:03 AM
|
|
You can use one of the cloud storage sites like Skydive and upload it there and send me a link.
|
|
|
New Member
|
|
Aug 13, 2012, 06:32 AM
|
|
That site is currently blocked. So I will send the link later this evening.
But, I am still having problems with the syntax of the expression. Like I mentioned earlier, the query is producing the same results.
The expression used in the Subtotal (calculated) field is:
IIF([Expenses]= "Per Diem" OR "Hotel Accommodation", ([Rate]*[Duration]), [OneTimeCosts])
|
|
|
Computer Expert and Renaissance Man
|
|
Aug 13, 2012, 09:37 AM
|
|
Originally Posted by FergieF
The expression used in the Subtotal (calculated) field is:
IIF([Expenses]= "Per Diem" OR "Hotel Accommodation", ([Rate]*[Duration]), [OneTimeCosts])
As I've said, you have to fully enter each condition so it needs to be:
IIF([Expenses]= "Per Diem" OR [Expenses] = "Hotel Accommodation", ([Rate]*[Duration]), [OneTimeCosts])
But if that doesn't work, I need to work with the data to see why.
|
|
|
New Member
|
|
Aug 13, 2012, 10:15 AM
|
|
The database contains the following tables:
tblExpenses
Employee Id
Expenses
Allocation Code/Work Order
Rate
OneTimeCost
Duration
Subtotal
tblOverseas Visit
Place to be visited
Purpose of visist
Start Date
End Date
tblEmployees Expenses
Employee Id
First Name
Last Name
Department
Note: The Overseas Visit tbl is a subdatasheet of the Employees Expenses tbl and the Expenses tbl is a subdatasheet of the Overseas visit tbl.
1. Each employee completed one or more course(s). Jim (an employee) completed two courses in different places.
He did a Driving course in Alabama and a First Aid course in The Bahamas.
This is how the screenshot may have looked:
For the first course:
tblEmployee Expenses
Employee ID First Name Last Name Department 1234 Jim Doe Customer Relations
tblOverseas visit
Place to be visited Purpose of Visit Start Date End Date
Alabama Driving Course June 4, 2012 June 10, 2012
tblExpenses
Expenses Rate Duration OneTimeCosts Subtotal
Per Diem $ 80 7 $ 560Airfare $300 300
Hotel Accommodation 98 6 588
Ground Transportation
Taxi
Registration Fee
Tuition/Fees
Salary For The Period
Miscellaneous 200 200
TOTAL $1,648
This is for the second course:
tblEmployee Expenses
Employee ID First Name Last Name Department
1234 Jim Doe Customer Relations
tblOverseas visit
Place to be visited Purpose of Visit Start Date End Date
The Bahamas First Aid Course July 1, 2012 July 3, 2012
tblExpenses
Expenses Rate Duration OneTimeCosts Subtotal
Per Diem $ 87 3 $ 261Airfare $450 450
Hotel Accommodation 106 2 212
Ground Transportation
Taxi
Registration Fee
Tuition/Fees
Salary For The Period
Miscellaneous 200 200
TOTAL $1,123
Note: The Employee Data, i.e. Employee ID, First Name, Last Name, etc. is only shown once. There is an expand button (+) attached the Employee Expenses tbl. If clicked. You will see the Overseas visit tbl:
Place to be visited Purpose of Visit Start Date End Date
+ Alabama Driving Course June 4, 2012 June 10, 2012
+ The Bahamas First Aid Course July 1, 2012 July 3, 2012
If you click the expand button for this subdatasheet, you will see the Expenses tbl... shown above.
The values shown in the Expenses table is what I would like to see. However, since I entered the expenses for the First Aid course last, those are the values shown in both of the expenses tbl. The values in the other table, (that is, for the course held in Alabama) automatically changed to match the most recent values I keyed in (that is, for the course held in The Bahamas)
How can I stop this from occurring? The values are supposed to be different.
2. I keyed in the expression you just gave me, but I am still getting the same results. Should I just manually calculate the fields?
|
|
|
New Member
|
|
Aug 13, 2012, 12:22 PM
|
|
I keyed in the expression again and it worked. Thanks a lot!
But, I still need assistance with the issue mentioned above.
|
|
|
New Member
|
|
Aug 13, 2012, 07:53 PM
|
|
|
|
|
Computer Expert and Renaissance Man
|
|
Aug 14, 2012, 04:55 AM
|
|
Did you make it public? Because its not allowing me access.
Your relationships are unclear. Your OverseasVisit table should have EmployeeID as a Foreign Key and tblExpenses should have VisitID as a Foreign Key. That's how you tie the expense to a specific visit and visits to a specific employee.
|
|
|
New Member
|
|
Aug 14, 2012, 12:29 PM
|
|
I reproduced the OverseasVisit and Expenses tables and inserted the fields you mentioned. However, the records are still simultaneously updating.
As for the screenshot, I will be able to change the setting to public in approximately three hours.
|
|
|
New Member
|
|
Aug 15, 2012, 08:58 AM
|
|
I have another issue. I am trying to create a form based on three tables which are linked. However, I am only allowed to use one table as the Record Source. I tried using the wizard, but it did not produce the result that I need. Any idea about what to do?
|
|
|
Computer Expert and Renaissance Man
|
|
Aug 15, 2012, 09:21 AM
|
|
Originally Posted by FergieF
I have another issue. I am trying to create a form based on three tables which are linked. However, I am only allowed to use one table as the Record Source. I tried using the wizard, but it did not produce the result that I need. Any idea about what to do?
Use subforms. I don't recommend using a multi-table query as a recordsource, especially if you want to use the form to enter data.
|
|
Question Tools |
Search this Question |
|
|
Add your answer here.
Check out some similar questions!
Microsoft Word Starter 2010
[ 0 Answers ]
This came loaded on my new computer. Supposedly it's free because it has ads but... is this permanent or something temporary that I will be told I have to buy a little later on? I don't want to write documents with it that I won't be able to open down the road unless I buy.
Microsoft Word 2010 feature "Auto Correct"
[ 4 Answers ]
Just bought a new HP computer with Windows 7 and Microsoft 2010 loaded. I am a medical transcriptionist and I use the feature 'Auto Correct' heavily to format frequently used phrases. I have found WHERE this feature is in Word 2010 but when I add phrases, they do NOT remain the next day when I...
Hyperlink trouble with Microsoft Word 2010
[ 1 Answers ]
Whenever I click a hyperlink on any of my 2010 Word documents I get the message "This operation has been canceled due to restrictions in effect on this computer. Please contact your system administrator." Well, I am the administrator of my computer and I don't know what to do to resolve this...
Microsoft Access
[ 1 Answers ]
I have a form and entering an amount received or amount used from a transaction table and trying to update a balance on hand in another table. Help.I want to update the record one time.
Any suggestions?
Microsoft Access
[ 1 Answers ]
How can I build a Calendar where I can schedule several activities in a monthly basis. I want this calendar to be linked to a database (access table) with at least 8 fields (Date, Project, Name of project, Type of task/activity, Name of person in charge... )
If there is any site or links where...
View more questions
Search
|