473,320 Members | 1,936 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Like Statements within IIF statement Help...

I am trying to use the IIF statement to look for a null value. That seems to be working fine..

The problem occurs when I use a LIKE Statement inside the IIF statement.
Here are some examples:
Expand|Select|Wrap|Line Numbers
  1. Like "*" & [Forms]![Search Form]![Title] & "*"    This works fine
but the above statement used inside an IIF statements returns nothing as in the statement below.
Expand|Select|Wrap|Line Numbers
  1. IIf(IsNull([Forms]![Search Form]![Title]),Null,Like "*" & [Forms]![Search Form]![Title] & "*")            
  2. The above statement does not work and I know the IsNull expression is working,
For some reason the LIKE statement doesn't seem to work when used inside the IIF statement...

ANY help would be appreciated. Thank you..
Feb 11 '10 #1
3 2634
missinglinq
3,532 Expert 2GB
I'm sorry, but your logic here really doesn't make any sense at all! What you're saying here is:

If [Forms]![Search Form]![Title] is empty (Null) you want some value to be Null.

If [Forms]![Search Form]![Title] is not empty (Null) you want some value to be

Like "*" & [Forms]![Search Form]![Title] & "*"!

This makes no sense!

Like is typically used when you are searching for a string within a larger string. It is not used to assign a value to something.

Exactly what, in plain language, are you trying to do here?

Welcome to Bytes!

Linq ;0)>
Feb 12 '10 #2
Sorry for the confusion. I am trying to have users enter in a search value in a form to search for an item in the database. I do not want it to be an exact value but use wildcards to make it so that if their search value is any part of the text field it will return that record..

I would like to use a form with 5 text boxes that the user can enter search values in one or up to all five text boxes and then conduct a search. If they leave the text field blank I want it to not pull any records.

I am sure I am missing something.. Thanks for any help..
Feb 12 '10 #3
NeoPa
32,556 Expert Mod 16PB
What you're missing is the ability to express your question clearly. You're not alone in this. Don't worry.

It seems you are trying to set up a SQL string, but you chopped off too much of the code around that would have given the context within which we could more easily have understood what you were asking for.

Your problem is a simple and oft repeated one. You are not creating a string value to return as you need. Check out below for help with this :
One of the most popular (frequently occurring rather than best liked) problems we get is with SQL strings being manipulated in VBA code.

The reason this is so difficult is that all the work is being done at a level of redirection. What I mean by this is that the coder is never working directly with the SQL itself, but rather with code which in turn, is relied on to produce the SQL that they are envisaging is required. It's rather similar to the problems coders have historically had dealing with pointers.

Anyway, a technique I often suggest to coders struggling with this (at any level. This happens to experienced coders too.) is to use either the MsgBox() function, or Debug.Print into the Immediate Pane of the debugger window, to display the value of the SQL in the string before using it (That's assuming you're not good with debugging generally. Personally I would trace to the line, then display the value prior to allowing execution of the string - See Debugging in VBA). It's really much easier to appreciate what a SQL string is meant to do, and where there may be problems, when you can see it in its entirety, and in its true form, rather than as the code is about to create it.
Let us know how you get on with this.
Feb 12 '10 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Rookie | last post by:
I have done a lot of programming some time ago using Fortran and various varieties of the Basic programming languages, Hewlett Packard Technical Basic, QBasic, Quick Basic. With each of these...
11
by: dmbkiwi | last post by:
I am new to this group, and relatively new to python programming, however, have encountered a problem I just cannot solve through reading the documentation, and searching this group on google. I...
1
by: davidgordon | last post by:
Hi, Quick question: How can I link 2 SQL statements in my asp page. Originally I had 1 statement, but because it was fairly complex, it took too long to parse. SQL statement 1 strQuery =...
1
by: jan | last post by:
Hello, I am new to this list and would appreciate some help with Access 2000 and Access 2003. I need to export our access database table statements by access database to place into our database...
5
by: Neil Zanella | last post by:
Hello, Unlike in pre-C99 versions of C where variables can only be defined at the beginning of blocks, C99 allows variables to be defined in arbitrary places inside blocks. However, gcc 3.2.2...
3
by: tpcolson | last post by:
I'm not what you'd call a "programmer" of any sort, so perhaps this question may seem arcane and result in a plethora of "you idiot" threads, but here goes: ArcGIS 9.1 has a neat interface with...
20
by: Neroku | last post by:
Hello, i would like to know what the serious definition of statements and expressions is: i know an expression are evaluated to a value, i.e: 1 == 2 5+7 foo( 1,2) and a statement is...
26
by: brenocon | last post by:
Hi all -- Compared to the Python I know and love, Ruby isn't quite the same. However, it has at least one terrific feature: "blocks". Whereas in Python a "block" is just several lines of...
14
by: Ben | last post by:
I don't know whether anyone can help, but I have an odd problem. I have a PSP (Spyce) script that makes many calls to populate a database. They all work without any problem except for one...
5
by: sam_cit | last post by:
Hi Everyone, I read somewhere that there are some compile time operations behind switch-case, which is why it can work for cases which evaluates to an integer or character and not strings and...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.