Querying Hyperlinks

guamlet

Board Regular
Joined
Dec 29, 2002
Messages
145
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?
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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,
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
try this:

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

hth,
Giacomo
 
Upvote 0
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!
 
Upvote 0
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?
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top