Help & FAQs    Search BCF    Members    Groups    Events Calendar
Resend my activation email : Register : Log in 
BCF: Bike Chat Forums


Stupid MS Access!!!

 Reply to topic
Bike Chat Forums Index -> The Geek Zone
View previous topic : View next topic  
Author Message

Yoko
Brolly Dolly



Joined: 25 Feb 2007
Karma :
PostPosted: 10:34 - 23 Nov 2007    Post subject: Stupid MS Access!!! Reply with quote
Right I'm trying to write a query that if a form tickbox is ticked it applies a certain criteria to a field and if its not ticked it returns everything (as if no criteria was specified).

I tried:

Code:
Is NOT Null


Which worked as expected... Doesnt return the empties so...

Code:
Is NOT Null OR Is Null


Again worked as expected... Returns everything...

However...

Code:
IIf ( True, Is Not Null, "Some Criteria" )


Returns Nothing...

Similarly...

Code:
IIf ( True, Is Not Null or Is Null, "Some Criteria" )


Comes back with...

Quote:
The expression is typed incorrectly or is too complex


Where is the logic in that?!?!

Confused Evil or Very Mad
____________________
Sheep Shagger
 Back to top
View user's profile Send private message Visit poster's website You must be logged in to rate posts

Kickstart
The Oracle



Joined: 04 Feb 2002
Karma :
PostPosted: 11:24 - 23 Nov 2007    Post subject: Reply with quote
Hi

Err, that isn't IIF syntax.

Should be iif ( condition, value_if_true, value_if_false ).

Yours seems to be saying if True is True then return "Is Null Or Is Not Null" (god knows what it would try and return).

All the best

Keith
____________________
Traxpics, track day and racing photographs - Bimota Forum - Bike performance / thrust graphs for choosing gearing
 Back to top
View user's profile Send private message Send e-mail You must be logged in to rate posts

JonB
Afraid of Mileage



Joined: 03 Jun 2004
Karma :
PostPosted: 11:30 - 23 Nov 2007    Post subject: Reply with quote
And why would you be using database software?

Haven't got 2 new CD's recently have you? Razz
____________________
Be careful whose advice you buy, but, be patient with those who supply it. Advice is a form of nostalgia, dispensing it is a way of fishing the past from the disposal, wiping it off, painting over the ugly parts and recycling it for more than it?s worth.
 Back to top
View user's profile Send private message Send e-mail You must be logged in to rate posts

Yoko
Brolly Dolly



Joined: 25 Feb 2007
Karma :
PostPosted: 11:58 - 23 Nov 2007    Post subject: Reply with quote
Kickstart wrote:
Hi

Err, that isn't IIF syntax.

Should be iif ( condition, value_if_true, value_if_false ).

Yours seems to be saying if True is True then return "Is Null Or Is Not Null" (god knows what it would try and return).

All the best

Keith


Err not really, the condition is a questions which returns true or false, I've simply left out the question and given it the answer.

Eg:

IIf ( True, do a, do b ) will 'do a' likewise

IIf ( False, do a, do b ) will 'do b'

I dont actually use that statement in my query, it was just do demonstrate that 'Is Not Null' doesnt work when imbedded in an IIf statement.

Why can I use it in normal criteria and not in an if statement. Doesnt make sense to me.

What I want is an IIf statement that if a button in my form is ticked no criteria is specified (and therefore everything is returned), if its unticked then a list box becomes enabled and the user specifies a criteria from the list box which is then passed to the criteria in the query.

'Is Null or Is not Null' returns everything, which is what I want it to do if the button is ticked.

Is null = Everything that is empty

Is not null = Everything that is not empty

Is null or is not null = Everything that is empty or is not empty

Saying IIf ( 'button ticked' (aka true Wink) , , 'criteria from list box')

Is the same as saying...

IIf ( 'button ticked', "", 'criteria from list box')

In which case when the button is ticked it will return everything with an empty string in that field. Which is not what I want...

Anyway I've now overcome my problem a different way but still doesnt make sense why you can say "something" in an IIf statement but not 'is null' or 'is not null' or 'is null or is not null'

Jon B wrote:
And why would you be using database software?

Haven't got 2 new CD's recently have you? Razz


Shifty
____________________
Sheep Shagger
 Back to top
View user's profile Send private message Visit poster's website You must be logged in to rate posts

Kickstart
The Oracle



Joined: 04 Feb 2002
Karma :
PostPosted: 13:14 - 23 Nov 2007    Post subject: Reply with quote
Hi

"Is Null" and "Is Not Null" are SQL, while "IsNull" is VBA. IIF if VBA code so presume you would need the VBA check within there rather than the SQL check.

All the best

Keith
____________________
Traxpics, track day and racing photographs - Bimota Forum - Bike performance / thrust graphs for choosing gearing
 Back to top
View user's profile Send private message Send e-mail You must be logged in to rate posts
Old Thread Alert!

The last post was made 18 years, 186 days ago. Instead of replying here, would creating a new thread be more useful?
  Display posts from previous:   
This page may contain affiliate links, which means we may earn a small commission if a visitor clicks through and makes a purchase. By clicking on an affiliate link, you accept that third-party cookies will be set.

Post new topic   Reply to topic    Bike Chat Forums Index -> The Geek Zone All times are GMT + 1 Hour
Page 1 of 1

 
 You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You cannot attach files in this forum
You cannot download files in this forum

Read the Terms of Use! - Powered by phpBB © phpBB Group
 
Debug Mode: ON - Server: birks (www) - Page Generation Time: 0.34 Sec - Server Load: 5.32 - MySQL Queries: 13 - Page Size: 49.71 Kb