Null values in query - Microsoft Access / VBA

mar10

I have a form that has a drop down box on it which allows the user to
select a YEAR. If the user makes a selection I have a subform that is
driven by a query that will select the YEAR that the user picked. If
the user does not select a year I want to find all items on the
underlying subform. There are Null YEARs in the data base.

I thought if I had my query either select the YEAR picked or "*" it
would find everything, but I guess "*" does not find Null values.

I'm sure it's something simple that I'm just not thinking of, but
how do I have the underlying query select everything is the YEAR
dropdown is empty?

Thanks

Nov 13 '05 #1

Subscribe Reply

1642

Allen Browne

Presumably you have criteria in your query that looks something like this:
[Forms].[Form1].[txtYear]

Switch the query to SQL View (View menu), and change the WHERE clause to
something like this:
([MyYear] Is Null) OR ([MyYear] = [Forms].[Form1].[txtYear]
where "MyYear" represents the name of your field.

I suggest you don't use YEAR as a field name: there is a Year() function in
VBA, and so code can get confused about what you are talking about.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"mar10" <ma****@yahoo.c om> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.com.. .

I have a form that has a drop down box on it which allows the user to
select a YEAR. If the user makes a selection I have a subform that is
driven by a query that will select the YEAR that the user picked. If
the user does not select a year I want to find all items on the
underlying subform. There are Null YEARs in the data base.

I thought if I had my query either select the YEAR picked or "*" it
would find everything, but I guess "*" does not find Null values.

I'm sure it's something simple that I'm just not thinking of, but
how do I have the underlying query select everything is the YEAR
dropdown is empty?

Thanks

Nov 13 '05 #2

Similar topics

12211

Question about Null values

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 byte what is returned from a field in a database? Josh

PHP

1208

ON UPDATE CASCADE, is it correct that b remains NULL? even after update?

by: Morten Gulbrandsen | last post by:

-------------- SELECT Version() -------------- +--------------------+ | Version() | +--------------------+ | 5.0.0-alpha-max-nt | +--------------------+ 1 row in set (0.00 sec)

MySQL Database

2026

Returning # of columns with non-null value

by: Ellen Manning | last post by:

Using SQL2000. I want to return the # of columns with non-null values. Here's my query so far: select case when Dx1 is not null then 0 else 1 end + case when Dx2 is not null then 0 else 1 end + case when Dx3 is not null then 0 else 1 end + case when Dx4 is not null then 0 else 1 end as DxCount from tblClerkshipDataClean where PalmName = @PalmName

Microsoft SQL Server

5275

Criteria testing fields that may contain null values

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 all querying is done locally. One of the reports we run allows the user to list all invoices within a period. They are also allowed to select a customer code and a product set on

Microsoft Access / VBA

2488

Filtering Queries that use a fucntion to return a value (Null Values in a Date Datatype)

by: Ken | last post by:

I wrote a function to use in queries that takes a date and adds or subtracts a certain length time and then returns the new value. There are times when my function needs to return Null values. Function DateCalc (blah...) As Variant Do Stuff... If Not IsNull(varNewDate) Then DateCalc = varNewDate End If End Function

Microsoft Access / VBA

4016

Append Import Accept Null Values

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 query will not work unless there are values in the data i am attempting to send. I want the fields in the destination table to accept null and populated values. Any help here would be gladly accepted. thanks

Microsoft Access / VBA

6654

Crosstab report Null and Zeros

by: jkearns | last post by:

Hello, I made a report from a crosstab query following the steps onlined in MSDN's Solutions.mdb example. I now have a dynamic crosstab report (great!), but with one minor problem. I cannot get access to show NULL and 0 values seperately. I've tried the following Format properties in my text box: 0.0;;0;"" --> Both NULL and 0 values display as 0 0.0;;"";"" --> Both NULL and 0 values show up blank

Microsoft Access / VBA

1965

Concept about NULL and UNIQUE

by: Suyash Upadhyay | last post by:

Hi all, I want to ask whether MySQL also treat two NULL values as different values like Oracle. I write following commands but MySQL generate NULL value for all enrollment no field inspite of the fact that I have declared it as UNIQUE. What is the problem? mysql> create table student(enrollment_no varchar(10) unique,name varchar(10)); Query OK, 0 rows affected (0.02 sec) mysql> insert into student(name) values('Jhon');...

MySQL Database

3348

Executescalar pblm for null values

by: Manikandan | last post by:

Hi, I have a table with following data Tablename:details No(varchar) Name(varchar) Updated(Datetime) 1 mm 10/10/2006 2 nn 02/12/2005 3 kk NULL I'm using executescalar to get the updated values from the table

C# / C Sharp

12322

What is Null?

by: ADezii | last post by:

Null as it relates to database development is one of life's little mysteries and a topic of total confusion for novices who venture out into the database world. A Null Value is not zero (0), a zero (0) length string, an empty Field, or no value at all - so exactly what is Null? The purpose of this Topic is hopefully to explain what a Null Value is, discuss some peculiarities about Nulls, show how we can detect them, and finally, how to convert...

Microsoft Access / VBA

9595

Changing the language in Windows 10

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 effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...

Windows Server

10603

Problem With Comparison Operator <=> in G++

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, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

10356

The easy way to turn off automatic updates for Windows 10/11

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 Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

10099

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

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 protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...

General

7643

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...

Microsoft Access / VBA

6869

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

5536

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...

Networking - Hardware / Configuration

5675

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

4314

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp