Form Control Values Display As different from source values

[svrich]

Hi John, sorry for the delay. I've been traveling a lot lately.

Properties are attached in a screen cap. the Column is highlighted in Orange "P N_ P"






All properties are displayed except "Text Align" which is set as "General"

[John_G]

Hi -

Try this as the second part of the criteria for Column P N_ P - it gets away from using the Like...

len(trim(nz(Null," "))) = 0

This changes a Null to a blank if necessary, then determines the length of the resulting string. If the length is zero, the field is empty, whether it contains a Null, a zero-length string, or a blank.

John

[svrich]

So how would I utilize this with my either the check box (True - Display all. False - only show zero-length/Null) or a drop down/multi-select box with similar value set?

The problem so far with that theory is that the P N_ P field is a text based, not a 0/1. I feel an If/Then should be able to be written to perform a "translation" so to speak. Or am I just thinking bout this the wrong way from the start?

[John_G]

Hi -

When it comes to referring to the checkbox on the form, the data type of the P N_ P field doesn't matter. The checkbox is a Yes/No or True/False boolean.

I have made this work with test data, and below are screenshots to show you what I did.

http://i62.tinypic.com/zilwli.jpg The test data set - the "designation" fileld corresponds to your P N_ P
http://i62.tinypic.com/2w5uko5.jpg The query that will be opened by the test form
http://i59.tinypic.com/10hvams.jpg The form for testing - the checkbox is called Show_All_Records
http://i59.tinypic.com/o9g5eo.jpg The query results with the checkbox selected (all records)
http://i62.tinypic.com/vy45r7.jpg The query results with the checkbox not selected - only nulls in Designation

Note that in the query design, there are two criteria, and they are on SEPARATE lines - separate lines because they are combined with OR.

The SQL for the query is:

SELECT [Null Test].UserName, [Null Test].Grade, [Null Test].Designation
FROM [Null Test]
WHERE [Null Test].Designation Is Null OR [Forms]![Null Test]![Show_All_Records]=True;

The query SQL can be:

SELECT [Null Test].UserName, [Null Test].Grade, [Null Test].Designation
FROM [Null Test]
WHERE Len(Trim(nz([Designation]," ")))=0 OR [Forms]![Null Test]![Show_All_Records]=True;

if you are unsure about whether P N_ P contains Nulls, blanks, or zero-length strings.

HTH

John