IIF works on form, not in Query
- Thread starter DDishShop
- Start date
This was an "inherited" database, so there are many issues with it that I don't have the time (nor are my employers willing to let me spend the time) to fix everything, so I am more or less stuck with putting bandages on things just to get them up and functional again.
The first thing I would love to do is get rid of all lookup's in the tables, but I kid you not, there are 100's of them spread out throughout the database. Everything works though except for what I am trying to accomplish here, so there are no immediate issues or reasons to fix what is currently functional.
As a work around, I just created a new form, stuck all of my "Replaced" fields on it from the Query (which consists of multiple queries), added the Dlookup's in text boxes (which gives me a part number and cost) and generated a report directly from the form, which does what I need it to do end game. The form is never meant to be viewed, so I didn't have to spend much time with formatting, I did all that on the report.
Perhaps down the road I'll have time to restructure everything, but that certainly won't happen today.
The first thing I would love to do is get rid of all lookup's in the tables, but I kid you not, there are 100's of them spread out throughout the database. Everything works though except for what I am trying to accomplish here, so there are no immediate issues or reasons to fix what is currently functional.
As a work around, I just created a new form, stuck all of my "Replaced" fields on it from the Query (which consists of multiple queries), added the Dlookup's in text boxes (which gives me a part number and cost) and generated a report directly from the form, which does what I need it to do end game. The form is never meant to be viewed, so I didn't have to spend much time with formatting, I did all that on the report.
Perhaps down the road I'll have time to restructure everything, but that certainly won't happen today.
That does sound like a lot of lookup tables.
However lookups are generally a good thing as they abstract the data from what is displayed. Storing and searching short text or numeric values is more efficient and allows what is to be displayed to be easily changed by altering a single record instead of updating all the values in the main data.
Some developers combine their lookup tables into one.
Pat Hartman
Super Moderator
- Local time
- Today, 05:45
- Joined
- Feb 19, 2002
- Messages
- 47,280
Table level lookups - BAD. Form level lookups - GOOD
That't the whole purpose of a form. Forms are the user interface. How is a user to work with a form if he can't view it. If you are creating forms and automating them, you are doing way more work than you need to and need to spend some time learning how to write code.
Dang Pat, harsh much? I have been learning all I can to write "code", but I am no genius as I am certain you must be. This whole post is about how something that works on a form is not working in a Query. So I had to make a fix. I created the form only for the direct purpose of putting in the IIF formulas that are not working in the Query. I am only trying to print a parts list from multiple forms, and the form the parts list comes from is NOT meant to be edited, its for reporting purposes ONLY! I have forms where the data is edited by the user like it's supposed to work. This is only an effort to combine that data into a single report.
I have it all working now. I did not find the solution I was looking for here, so I had to come up with my own that works, and it does.
Thank you all for the constructive advice, I did learn a thing or two from this post.
I have it all working now. I did not find the solution I was looking for here, so I had to come up with my own that works, and it does.
Thank you all for the constructive advice, I did learn a thing or two from this post.
I could not get the IIF formula to work in a Query as it does in a text box on a form. I tried all of the offered suggestions, but to no avail. It was eventually noted that some VBA does not work the same in a query as it does on a form. Taking that knowledge I came up with an alternate solution that is working as I had hoped it would originally.
I created a query with 15 other queries that only pull fields that have a lookup from a table with options of New, Used or Warranty (all these fields have the word "Replaced" to make it easy to differentiate). There are 230 of them among 15 separate forms that the end user fills out . I then created a form based off this combined query that is not intended to be edited or even formatted except to squeeze everything together so it's organized. Then I created a text box for each field that has the IIF function combined with a Dlookup function that is able to find the part and cost from multiple tables. I then saved the form as a report and modified the report for visual purposes (hiding all bound fields and only displaying the text boxes). I then created a macro that runs the report, based on the form, using an ID (work order number) to only print a single report.
The result is a parts list that I am able to total all costs (full price for "New", half price for "Used", and zero cost for "Warranty"). Originally my thought was to only create the Query and run a report from that, but the IIF formula wouldn't work, so I had to run the report off of a form where the formulas do work instead. Despite all the lookups, it only takes about 3 seconds for the report to open, which I am more than willing to deem as acceptable.
I created a query with 15 other queries that only pull fields that have a lookup from a table with options of New, Used or Warranty (all these fields have the word "Replaced" to make it easy to differentiate). There are 230 of them among 15 separate forms that the end user fills out . I then created a form based off this combined query that is not intended to be edited or even formatted except to squeeze everything together so it's organized. Then I created a text box for each field that has the IIF function combined with a Dlookup function that is able to find the part and cost from multiple tables. I then saved the form as a report and modified the report for visual purposes (hiding all bound fields and only displaying the text boxes). I then created a macro that runs the report, based on the form, using an ID (work order number) to only print a single report.
The result is a parts list that I am able to total all costs (full price for "New", half price for "Used", and zero cost for "Warranty"). Originally my thought was to only create the Query and run a report from that, but the IIF formula wouldn't work, so I had to run the report off of a form where the formulas do work instead. Despite all the lookups, it only takes about 3 seconds for the report to open, which I am more than willing to deem as acceptable.
Pat Hartman
Super Moderator
- Local time
- Today, 05:45
- Joined
- Feb 19, 2002
- Messages
- 47,280
IIF() is an SQL function so it is native to SQL. If - then - else is the VBA alternative. However, VBA also supports the IIf().
One IMPORTANT think you need to understand though is that IIf() works DIFFERENTLY in VBA than it does in SQL. When an IIf() is executed in SQL, execution stops as soon as a condition tests true. However, in VBA, ALL conditions are evaluated. The problem arises if you are doing a division and using the IIf() to avoid a divide by zero error. The IIf() will work as you need it to in SQL but fail in VBA because the divide will be performed and raise the divide by zero error if the divisor is zero.
One IMPORTANT think you need to understand though is that IIf() works DIFFERENTLY in VBA than it does in SQL. When an IIf() is executed in SQL, execution stops as soon as a condition tests true. However, in VBA, ALL conditions are evaluated. The problem arises if you are doing a division and using the IIf() to avoid a divide by zero error. The IIf() will work as you need it to in SQL but fail in VBA because the divide will be performed and raise the divide by zero error if the divisor is zero.
You do have your work cut out for you. But, please remember on your journey that you NEVER need to create a form to automate. Forms are used to display/update data, as menus, or to capture parameters to be used in running reports. If you find that you need to reuse code that you've written in a form, the best solution is to modify the original form to remove the code and place it in a standard module where it will be available for all uses and then call the code from multiple places.
Just to clarify, each of the 15 forms the users fill out, is the same IIF()/Dlookup formulas in text boxes so the user has a running list of parts. When it came time to generate a report that combines all 15 forms, I hit a wall. That's when I figured I could do it all in a query, which obviously didn't work either.
So the idea of an non-editable form to place all my IIF's for no other reason than to generate a report came into being.
When you say to:
I'm not understanding what you mean by this exactly. Could you expand on this process for me? What do you mean by "Module"?
So the idea of an non-editable form to place all my IIF's for no other reason than to generate a report came into being.
When you say to:
I'm not understanding what you mean by this exactly. Could you expand on this process for me? What do you mean by "Module"?
Pat Hartman
Super Moderator
- Local time
- Today, 05:45
- Joined
- Feb 19, 2002
- Messages
- 47,280
There is something very wrong with the current design. You should not need 15 forms to do the same thing.
The code used by forms and reports is stored in a class module. The class module for a form or report is only available if that form or report is open. Generic code is stored in standard modules. The procedures and functions are defined as public to make them available throughout the application to any other piece of code that is running and in the case of functions, queries and macros can also reference them.
For example, you might have a piece of code that validates birth dates. It rejects future dates and it checks previous dates for logical correctness. You probably won't have any employees who were born in 1856. So you create a function to do the validation and have the function return true or false. Then, any place you need to validate DOB, you use the function.
The code used by forms and reports is stored in a class module. The class module for a form or report is only available if that form or report is open. Generic code is stored in standard modules. The procedures and functions are defined as public to make them available throughout the application to any other piece of code that is running and in the case of functions, queries and macros can also reference them.
For example, you might have a piece of code that validates birth dates. It rejects future dates and it checks previous dates for logical correctness. You probably won't have any employees who were born in 1856. So you create a function to do the validation and have the function return true or false. Then, any place you need to validate DOB, you use the function.
Each form is asking for different information, they are all not doing the same thing, there is nothing wrong with the design. However, I am trying to combine all the information from each form into a single report. That's it, nothing more.
What you are saying about the code could be useful in some areas, but for the most part each bit of code I have is specific for each field, and is easier to maintain and modify on the form it's located on, rather than sifting through a bunch of code to find it. If you read my initial posts, that is evident by the code I show.
I appreciate all the help, but I have my fix that works, and I honestly cannot spend anymore time on it. One day I would love to just start over from scratch because as mentioned earlier, I inherited the database and have been charged for now to just fix the issues it has, not to redesign the whole thing.
What you are saying about the code could be useful in some areas, but for the most part each bit of code I have is specific for each field, and is easier to maintain and modify on the form it's located on, rather than sifting through a bunch of code to find it. If you read my initial posts, that is evident by the code I show.
I appreciate all the help, but I have my fix that works, and I honestly cannot spend anymore time on it. One day I would love to just start over from scratch because as mentioned earlier, I inherited the database and have been charged for now to just fix the issues it has, not to redesign the whole thing.
Pat Hartman
Super Moderator
- Local time
- Today, 05:45
- Joined
- Feb 19, 2002
- Messages
- 47,280
Forms don't hold data. Tables hold data so I don't understand why 15 forms come into play. Maybe 15 tables, but not 15 forms. But as long as you are happy with your solution. We can move on.
15 different tables.
15 different forms.
Each form has several IIF's with Dlookups in text boxes (all are part lookups with cost).
Want to run a single report on just the text boxes from all 15 different forms, not the fields.
Built a multi query with only the fields with "New, Used, Warranty" lookup fields from the 15 different tables.
Created a single form based on the query.
Added textboxes with the IIF(),Dlookups (actually copied/pasted from each of the 15 different forms).
Saved the form as a report displaying only the text boxes.
Works great
Amen
15 different forms.
Each form has several IIF's with Dlookups in text boxes (all are part lookups with cost).
Want to run a single report on just the text boxes from all 15 different forms, not the fields.
Built a multi query with only the fields with "New, Used, Warranty" lookup fields from the 15 different tables.
Created a single form based on the query.
Added textboxes with the IIF(),Dlookups (actually copied/pasted from each of the 15 different forms).
Saved the form as a report displaying only the text boxes.
Works great
Amen
