473,412 Members | 1,944 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,412 software developers and data experts.

Handling null values in a query

Hi,

I have the following line in the critera of a query

Expand|Select|Wrap|Line Numbers
  1.   Like "*" & [forms]![main_report]![site] & "*"
This works fine when there is a value/data in each field, however when there are fields blank, it will not return the right results and skip the record altogether.

I have tried IsNull in the critera and Nz however does not seem to work. I have searched and searched but cant seem to make it work.

Any ideas?

Thanks in advance

Gareth
Apr 30 '11 #1

✓ answered by Stewart Ross

You say Nz did not work for you; I find that strange, as Nz will return an empty string by default when faced with a null value, which should work perfectly in your WHERE clause:

Expand|Select|Wrap|Line Numbers
  1. Like "*" & Nz([forms]![main_report]![site]) & "*"
-Stewart

3 2160
Stewart Ross
2,545 Expert Mod 2GB
You say Nz did not work for you; I find that strange, as Nz will return an empty string by default when faced with a null value, which should work perfectly in your WHERE clause:

Expand|Select|Wrap|Line Numbers
  1. Like "*" & Nz([forms]![main_report]![site]) & "*"
-Stewart
Apr 30 '11 #2
NeoPa
32,556 Expert Mod 16PB
What do you mean by "work"?

I'm fairly sure the SQL is working perfectly. However, this may be somewhat diffferent from what you expect, so I suspect we're going to need to know what you expect to happen if we're to help.
May 1 '11 #3
Its ok now, I got this to work with using Nz. It works fine by using the original code above.
May 10 '11 #4

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

Similar topics

9
by: Joshua Ruppert | last post by:
A section of the documentation for the isSet() function states: Also note that a NULL byte ("\0") is not equivalent to the PHP NULL constant. Where would you encounter a NULL byte? Is a null...
9
by: madsgormlarsen | last post by:
Hi I need to test a field(colum) in a SQL database for for NULL values, and have done so in this way. $query = "SELECT j FROM Andersen"; $result = mysql_query($query, $link_id); $query_data...
26
by: Agoston Bejo | last post by:
I want to enforce such a constraint on a column that would ensure that the values be all unique, but this wouldn't apply to NULL values. (I.e. there may be more than one NULL value in the column.)...
4
by: Brendan McLoughlin | last post by:
Hi, I am looking for opinions and alternatives for handling null values in a data object which reads a record from a database table. This object will have properties which will be populated...
6
by: AAVF | last post by:
Hi We have a problem with a query. An Access database links via ODBC to a UNIX server. To speed things, we use the ODBC to load the relevant tables to the local PC that runs Access so that...
3
by: JOEP | last post by:
What do I need to do to allow an append query to post null values to records in a field of the destination table? Basically I want to allow records with null values to post to the table. The append...
4
by: James Fisher | last post by:
How are people handling NULL values for value types. Descriptions of problem Say you have a typical method with the signature: public int InsertPerson(string firstName, string lastName, int...
1
by: DKode | last post by:
I find myself writing repetitive functions for handling null values from my DB like so: Private Function SetDateNull(ByVal p_date As Object) As Date If (TypeOf (p_date) Is System.DBNull) Then...
2
by: jehugaleahsa | last post by:
Hello: Is there a way to handle null values for ints, DateTime, etc. in generated DataTables? It seems it is not something the Microsoft crew thought of. That is hard for me to believe. ...
3
by: BurtonBach | last post by:
I previously wrote the following query which gives me that data I wanted and currently returns 436 records: SELECT qryTonnageSelect.Jobs_Job_ID, qryTonnageSelect.SumOfTransactions_TonsNetWeight,...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
0
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...

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.