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

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

VicRauch

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
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

Well-known Member
Joined
Feb 20, 2002
Messages
1,796
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

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

Well-known Member
Joined
Feb 20, 2002
Messages
1,796

ADVERTISEMENT

try this:

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

hth,
Giacomo
 

guamlet

Board Regular
Joined
Dec 29, 2002
Messages
145

ADVERTISEMENT

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

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
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

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

Well-known Member
Joined
Mar 23, 2006
Messages
2,032
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.
 

Forum statistics

Threads
1,136,267
Messages
5,674,730
Members
419,523
Latest member
Urnovio

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
Top