Handling Form Nulls in Query

[choytw]

Hello All,

I have a form which has 6 location fields. When values are selected in any combination of these fields (for example, building, floor, and room have values selected but zone, subroom, and cube do not), the query will pull the row in the table which matches the selections and populate the form.

My problem occurs when all 6 are not utilized. In the above example, the correct value will be returned as well as those rows which have values for the fields which are null (not selected).

The code in the condition line of the query is the following (only supplying the line for subroomNumber and not for the other five options):

Like Forms![WSM Edit/Add Space].[tab2subroomnumber] or Like Forms![WSM Edit/Add Space].[tab2subroomnumber] is null

I then modified the criteria line to the following:

IIF(isnull(Forms![WSM Edit/Add Space].[tab2subroomnumber]),null,Forms![WSM Edit/Add Space].[tab2subroomnumber])

Which, will return the correct value when all selections are made but will return nothing when the subroomnumber field has been left blank (is null).

I have tried many many combinations and so am now turning for help. Any ideas?

Thanks,

Calvin

 

[HiTechCoach]

I see an issue with the following:

Like Forms![WSM Edit/Add Space].[tab2subroomnumber] or Like Forms![WSM Edit/Add Space].[tab2subroomnumber] is null

Try:

(([fieldNameHere1]  Like Forms![WSM Edit/Add Space].[tab2subroomnumber])  Or ([fieldNameHere1] is Null))

What I prefer is to built the where clause "on the fly". I only include conditions for the fields where the user has actually specified a criteria.

 

[choytw]

I will give that a try in a few. Regardless of the outcome, thank you for the input!

 

[choytw]

Ok, have the same issue as before except now it returns the correct entry when one of the values is a null and more returns when all numbers are filled out. This is my setup:

There are 5 location variables to be filled out: BLDG, ZONE(auto-populates), FLOOR, [01-XXX RM], [SUB ROOM], and CUBE. To test the formulas, I have entered 3 different locations to test.

1) 41, 1, 03-, 321, null, 74
2) 41, 1, 03-, null, ,null, 74
3) 41, 1, 03-, 321, 1, 74

When the form has the following (BLDG, ZONE, FLOOR, [01-XXX RM], [SUB ROOM], and CUBE):

41, 1, 03-, 321, null, 74

The first line above is returned. When the following is in the form:

41, 1, 03-, 321, 1, 74

All 3 lines above are returned instead of just the third line. Previously, it was the exact opposite.

 

[HiTechCoach]

try:

(([fieldNameHere1]  Like Forms![WSM Edit/Add Space].[tab2subroomnumber])  Or (Forms![WSM Edit/Add Space].[tab2subroomnumber]  is Null))

 

[choytw]

Sorry for the delay. Took the weekend/holiday off.

I tried this solution for the SUB ROOM field only, and didn't get any returns. I removed the criteria line for SUB ROOM (using the same data on the form) and the correct record shows up.

Is it not possible to do this?

 

[HiTechCoach]

It is possible to get the desired results. I do what you want a lot6. I do it with a different method.

Do you have a place where you could post a sample of your database? If not I will put together a same database to see if I can duplicate what you are trying to do.

 

[choytw]

Unfortunately, due to the nature of the DB, I can't.

But, to make sure that I was clear earlier, I'll give a rundown on my setup.

In my table, I have 5 dropdowns which are used to select the correct location. They are: BLDG, FLOOR, ROOM, [SUB ROOM], and CUBE (zone is another but is populated once bldg, floor, and room are selected).

In the table, there is a unique location for building 41, floor 3, and room 321; another for building 41, floor 3, room 321, [sub room] 1, and cube 79; another for building 41, floor 3, room 321, and cube 49.

I'm sure you already understood, but want to make sure that you don't have wasted effort since you are trying to help. Thank you in advance!

 

[HiTechCoach]

Unfortunately, due to the nature of the DB, I can't.

But, to make sure that I was clear earlier, I'll give a rundown on my setup.

In my table, I have 5 dropdowns which are used to select the correct location. They are: BLDG, FLOOR, ROOM, [SUB ROOM], and CUBE (zone is another but is populated once bldg, floor, and room are selected).

In the table, there is a unique location for building 41, floor 3, and room 321; another for building 41, floor 3, room 321, [sub room] 1, and cube 79; another for building 41, floor 3, room 321, and cube 49.

I'm sure you already understood, but want to make sure that you don't have wasted effort since you are trying to help. Thank you in advance!

Thanks for the info.

Let me see what I can figure out.