Querying Hyperlinks

[guamlet]

I'm writing a query that's being run from a form. In the form, i have a check box. If the check box is checked, i would like to display any of the values that are not blank. If the check box is empty, i would like to display all values, blank and non-blank. The issue I think i'm having may be centered around the fact that the field in quesiton within the query contains hyperlinks. I have only been able to populate the query by using the name of the field [RNALoc] as either the true or false criteria for the iif statement i'm writing. Below is the IIF statement that i'm envisioning)

IIF([Forms]![Query Form]![chkItem]=-1, Is not null, All values)

So if I write the statement as

IIF([Forms]![Query Form]![chkItem]=-1,[RNALoc],[RNALoc])

I can get it to show everything that is not a null value

If I change the null values to 0, which then has the hyperlink address "http://0", then I can see everything with [RNALoc]. However, if I write

IIF([Forms]![Query Form]![chkItem]=-1, <>"http://0", [RNALoc])

or if i write

IIF([Forms]![Query Form]![chkItem]=-1 <>0, [RNALoc])

I retuen no values when the check box is checked. and I can still return all values when the check box is unchecked.

Any suggestions?

 

[VicRauch]

When I have a hard time with a null value in a field for comparison purposes, this is the "formula" I use to see if the field has nothing in it or is null: Len(Nz([RNALoc]))=0 That means there is thing there.
HTH,

 

[giacomo]

guamlet,

You cannot put the not equals (<>) expression into your iif function because the result is "<> 0" instead of <> "0" which is what you're trying to return. The way you've written it you are querying your table for a hyperlink equal to "<> 0" and understandably not getting any results.

Just wanted to provide some background as to why what you were trying was not working.

hth,
Giacomo

 

[guamlet]

In order to get around this, I think I've come up with a solution...I can put the following expression in the query as Expr 1: IsNull([RNALoc]). If I put true or false in the "criteria" part of this field within the query, then I can return those specific values. If I don't put anything in the "criteria field" I return all values. I can get the following to work as a criteria:

IIF([Forms]![Experiment Search]![chkPCR]=-1,False,True)

However, I would like to replace the True with nothing so that all values show. I've tried using "", Is Null, and , ) in there, but nothing seems to return all the values. Is there a way to do that?

guamlet

 

[giacomo]

try this:

LIKE IIF([Forms]![Experiment Search]![chkPCR]=-1,False,"*")

hth,
Giacomo

 

[guamlet]

Worked perfectly!!! Thanks!

 

[guamlet]

So everything was working fine, until I found out that some things that didn't have values were still showing up as not being "null". I used that Len(Nz([Field])) to find out that some of the empty fields were actually zero length strings, and some of the fields are Null...how aggravating!

Is there a way to reset a zero length string to Null?

Is there a way to rewrite my Expr1: isNull([Field]) that I was using as a Field in a query to differentiate between fields that had a value and those that didn't so that is is like an OR statement including the zero-length strings?

Thanks!

 

[VicRauch]

I think I have missed something in this last question of yours. The Len(Nz([Field])) = 0 will allow you to ignore (or select) any field that is EITHER null or a zero length string. Are you trying to handle these two types of fields differently?

 

[guamlet]

I don't want to differentiate between a null and a zero length string. I would like for a Null value or a zero length string to return a "False" so that my query only displays those fields that have a nonzero string in them.

Can this Len(Nz([Field]) thing do that? From what I understood of it, if there is a null value, the function will error. If there is a zero length string, i will get a "0" as a result. Feel free to correct me when i'm wrong, as it is often the case!

Thanks!

 

[VicRauch]

The Nz() function will look like either a 0 or a 0 length string. It seems to be smart enough to know when to do which. So, by using it the way it was presented, "Len(Nz([Field])) = 0" if the field is either Null or a zero length string, you will get a TRUE. That is what you want, that is why I suggested using this structure or formula. Try it and see what happens. If you don't like what happens, then ask and we will give more help.