I am trying to get a report to display a field even if it is empty.

Here is what I tried using that doesn't seem to work. I am thinking it's not
working bc it only works if it was intentionally "blank" instead of just empty?



Dept: IIf(IsNull([DepartmentName])," ",[DepartmentName])

What I want this to do is in the Dept control show the results of DepartmentName whether it is blank or not.

Is this the right way to do this? BC when I use it, it still doesn't show any of my records that contain empty fields.:(

So that field just doesn't appear in the report if it's not populated? That's strange. Is there an option in the report design to say 'omit if null' or something?

Actually the field still shows up for all the records that contain values, but the records that contain empty DepartmentName fields do not show up at all, (in any of the fields).

Oh I see.... what does the query that the report is based on look like? You might have to change it to show all department names and matching records from the other table(s), rather than just showing where records match (i.e. a left join rather than an inner join).

Well, I am not so confident w/ my ability to correctly join tables so that could be the problem.

Right now I have a 1-many relationship between tblDepartments and tblFaculty and I have done nothing to the join properties in the query.

I get very confused as to the correct direction to join my tables using the left/right join properties. If you know of a good tutorial/website for explaining that let me know.:o

The microsoft one isn't bad :)

What you'd want to have is the join between the two tables in your query, with the arrow going from tbldepartments towards tblfaculty (so double click on the join, and select the option that says show all records from tbldepartsments and matching records in tblfaculty). that way, when you pull down department name from tbldepartments as a field in your query, and any other field from tblfaculty, you'll see everything from tbldepartments, and where there isn't a matching value in tblfaculty, there will be a blank.

Have a muck about with it and see how you get on... that's the best way I reckon!

I tried changing the join so the arrow points to tblfaculty but when I try to close the query window I get a msg that reads, "JOIN expression not supported."

It still shows the 1 to many symbol in addition to the arrow. I thought other times I joined them those symbols disappeared. Is this why it's not working?

BTW, under your sig it says to click on the scales icon, but I don't see any scales icon anywhere.:confused:

Nevermind, now the scales are showing up. It must have just been slow to load.

Just for the heck of it, I tried pointing the arrow to Depts instead of Faculty and then my records showed up. Does this mean I should leave it this way since it seems to work? Or does it mean I may have screwed something else up somewhere else if this is working?

One faculty can have many departments, so you've got the relationship right. Glad it works, see how you get on I guess!

Well...actually one faculty should only have one department the way I set it up. In the Faculty table I select a department for the faculty. They technically could have more than 1 department...when that happens I am going to need help again!!

Ah I thought that's what the 1-many relationship was for. but hey as long as it works...