IIF works on form, not in Query

[DDishShop]

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.

 

[Galaxiom]

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.

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.

 

[DDishShop]

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.

 

[isladogs]

Agree with pat's comments which aren't in any way harsh

Glad you have found a solution.
For the benefit of anyone reading this post in the future, please explain what the solution was.

 

[DDishShop]

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.

 

[DDishShop]

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:

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

I'm not understanding what you mean by this exactly. Could you expand on this process for me? What do you mean by "Module"?

 

[DDishShop]

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.

 

[DDishShop]

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