iif nothing

[54.69.6d.20]

In the Criteria. I need to add a statement if there is no data then add a word or number to show that there's no data created yet. Tried =IIf(IsNull([123report30Days-AC.DateTimePlaced])," ")

I have 3 queries that I'm using for this 1 query. If an item comes in, it will be on the one query. Then finished, it will show up on #2 query and the same with #3. Trying to pull data even if it's not on all 3 queries. Right now the part will only show up if on all 3 queries

 

[MarkK]

It looks like the syntax of your IIF() function is incorrect. I think that function requires three parameters, like...

IIF(expression, truepart, falsepart)

See if that makes a difference.
Mark

 

[54.69.6d.20]

Unfortunately, that didn't work for me. It was just showing up a message box, over and over. If I don't have any Criteria. It will show only the records that are in those 3 queries. I need it to show all of them, but add a value to show that there's no input yet.

 

[Shado]

If you use the query designer, you can set this as apart of the relationships between the tables.

 

[54.69.6d.20]

If you use the query designer, you can set this as apart of the relationships between the tables.

I usually always use the designer. I don't have the knowledge to go down this road. It's got to be possible to show a value if it doesn't see one

 

[MSAccessRookie]

Lagbolt is correct in his assessment of your situation. You are missing one of the parts of your IIf() Statement. In the Query Designer, there is a different format for the structure. See if you can get something like this to work as a new column:

NewColumn:IIf(expression, truepart, falsepart)

 

[Brianwarnock]

I thing that you have 3 queries input to this query with the field appearing from each query each with this criteria applied, and you are ANDING the criteria when you need to Or them, in the design view that means coding each criteria on a different row.

Else show us your SQL

Brian

 

[54.69.6d.20]

I've attached 2 screen shots. You'll see what I'm trying to do. #2 you'll see that the report does work, however, it's not showing all. It's only showing the lineItem if it's been in all 3.

Then after this works. The goal is to find out an avg of how long the part is taking in each department

Again Thanks for the help

 

[Brianwarnock]

Your screen shot confirms what I suspected and suggested. With the three criteria on the same row they are anded , you require them to be Ored , put them on separate rows or switch to SQL view and change the And s to Or s

Brian

 

[54.69.6d.20]

I think I'm having the issue because these are date/time fields and it's trying to put "" in a date/time field. Anyway to get around that? :banghead:

 

[54.69.6d.20]

I think I'm having the issue because these are date/time fields and it's trying to put "" in a date/time field. Anyway to get around that? :banghead:

And I think the other problem is that there's no second field if the first one's not done. So a part comes in. It's in the Awaiting Calibration (1st query). After there're done. The Status changes to Awaiting QA. That's when it will show on the second query. and so forth to the 3 query. So it's not really a null field because there's nothing there yet for that line item

 

[Brianwarnock]

It would appear that I have been answering the wrong question, these are not criteria, which is how you filter the data, these are formulae to manipulate the data and as such need to be in a new column.

Brian

 

[jdraw]

I think we are all a little confused with what exactly you are trying to do. Seems, as Brian said, we may be answering the wrong question.

Can you tell us in plain English WHAT you are trying to do; then let's see what options there are.

 

[54.69.6d.20]

Sorry if I'm not making it clear. I'm just sure how to go about it. I'm trying to figure out a way to get an average of a tool in each department (how long was it in that department before it moved to another). When a tool gets put in the system, It's got a date/time with a lineitemID for that department (let's say "awaiting cal"). When that parts done. It will go to another department (Awaiting QA) which creates another line with the same LineID but different date/time and department. Which that last department is Complete. In the in 2.png, you'll see lines with the LineItemID but and different date/time. This works; however, what your not seeing is all the other tools that came in and only under the first stage awaiting calibration. Right now everything is put in this one table and needed to find a way to get that information. So I created a query for each of the three departments. Then created another query to look at the line itemid and show all three dates/times in that department. This way I can control the information needed for an average. LOL.. Again thanks. I didn't create this DB, I've only been supporting this company for about a month. I've been reading the inside and out access 2003 book.. But not getting what I need

 

[54.69.6d.20]

I've put out this question along with other questions. Seems that everyone here knows the basics and nothing more.