I have a query with a numeric field called BALBOOK. I have a form
(we'll call it Form1) that runs this query. On this form is a text
box (we'll call it Text1). When the query is run, I want is to use
all the records from a file where BALBOOK is equal to Text1. However,
if Text1 is blank (or null) I want the query to use all the records.
I don't know how to write the IIf statement. It would look something
like this:
IIf(Isnumeric([Forms].[Form1].[Text1]),CInt(([Forms].[Form1].[Text1])),"???")
The "???" is where I am stuck. I have tried ">0" (without the
quotes). I have tried leaving it blank. It works of Text1 is
numeric. It does not work if it is left blank.
Any suggestions?
4  2573  pw****@hotmail. com (Paul) wrote: IIf(Isnumeric([Forms].[Form1].[Text1]),CInt(([Forms].[Form1].[Text1])),"
???")
Try this (untested):
IIf(Not IsNull([Forms]![Form1]![Text1]),CInt([Forms]![Form1]![Text1]),Null)
If there's something in the text box it will be used as the criteria. If
the text box is null then so will the criteria.
Regards,
Keith.
Paul wrote: I have a query with a numeric field called BALBOOK. I have a form
(we'll call it Form1) that runs this query. On this form is a text
box (we'll call it Text1). When the query is run, I want is to use
all the records from a file where BALBOOK is equal to Text1. However,
if Text1 is blank (or null) I want the query to use all the records.
I don't know how to write the IIf statement. It would look something
like this:
IIf(Isnumeric([Forms].[Form1].[Text1]),CInt(([Forms].[Form1].[Text1])),"???")
The "???" is where I am stuck. I have tried ">0" (without the
quotes). I have tried leaving it blank. It works of Text1 is
numeric. It does not work if it is left blank.
Any suggestions?
Hae a look at http://www.mvps.org/access/queries/qry0001.htm
Keith Wilby <ke*********@Aw ayWithYerCrap.c om> wrote in message news:<Xn******* *************** *@10.15.188.42> ... pw****@hotmail. com (Paul) wrote:
IIf(Isnumeric([Forms].[Form1].[Text1]),CInt(([Forms].[Form1].[Text1])),"
???")
Try this (untested):
IIf(Not IsNull([Forms]![Form1]![Text1]),CInt([Forms]![Form1]![Text1]),Null)
If there's something in the text box it will be used as the criteria. If
the text box is null then so will the criteria.
Maybe I'm a little confused, but I'm thinking that if this works at
all it will at best limit your results to those records where this
field is Null rather than applying no criterion to this field at all.
In situations like this I usually build an SQL string in the form
itself and then run it or, if you need compiled query performance,
just build two separate queries, one with the criterion based on the
text box defined, and one without.
Bruce br***@aristotle .net (Bruce) wrote: I'm thinking that if this works at
all it will at best limit your results to those records where this
field is Null rather than applying no criterion to this field at all.
I would have thought that to return null instances, the 'false' part would
have to read "Is Null". Isn't by having just 'Null' in there the same as
not entering any criteria, ie leave it null? This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: nick_faye |
last post by:
hi,
hope someone can help me. i am a newbie in creating queries and i
want to create a query wherein i only get entries from my table where
values of fields 2, 3 and 4 are not zeros. for example:
field1 field2 field3 field4
one 1 0 0
two 2 0 3
three 0 0 0
my query should only get entries one and two. can somebody show me
|
by: Steve Elliott |
last post by:
I have a query set up to gather together data between two specified dates.
Shown in the query column as:
Between #24/09/2004# And #01/10/2004#
Is it possible to enter several different date ranges, ie between 24/09/2004
and 01/10/2004 together with 05/10/2004 and 07/10/2004 ?
If I enter the "Between" criteria on different lines it returns no data.
|
by: Matthew |
last post by:
Hey ,
I have built a query which takes values from unbounded fields on a
form, and it all works except for one thing. I have a few fields in
my query that are dates. I also have a start and end date and time on
my form. So, a user will specify the start and end date, and also,
the user has the option of checking which of the time fields from the
query will have these dates as criteria.
So, one of the date fields in my query is...
|
by: pelcovits |
last post by:
I am trying to set up an unbound form to enter report criteria.
I've followed the MS Office Assistance document: "Create a form to
enter report criteria" which describes how to enter data (such as
dates) in a text box. This procedure works fine. However, I also need
to enter data from a combo box and I cannot get this to work. On my
form I've created an unbound combo box named "Name". In the query bound
to the report I've entered the...
|
by: MLH |
last post by:
I have an A97 query (qryVehiclesNowners2) that has a table
field in it named . Depending on the selections made
in a number of criteria choices on a form, a field on the form
will have string values in it like...
Between #12/1/2004# And #12/31/2004# - or -
>= #6/10/2005# - or -
Between #6/1/2005# And #6/30/2005#
I want to use them as criteria for the query.
| | |
by: MLH |
last post by:
Am repeating question with different subject heading, perhaps
stating more clearly my problem...
I have an A97 query (qryVehiclesNowners2) that has a table
field in it named . Depending on the selections made
in a number of criteria choices on a form, a field on the form
will have string values in it like...
Between #12/1/2004# And #12/31/2004# - or -
>= #6/10/2005# - or -
|
by: martlaco1 |
last post by:
Trying to fix a query that (I thought) had worked once upon a time, and
I keep getting a Data Type Mismatch error whenever I enter any criteria
for an expression using a Mid function. Without the criteria, the Mid
function returns the values when I run the query. So if one of the
values is a "t" (no quotes), can I not ask to isolate that record by
putting "t" as a criteria? Nope - error, error.
If I put it within the expression itself...
|
by: Darin |
last post by:
I have a table that I want to delete specific records from based on
data in other tables. I'm more familiar with Access '97, but am now
using 2003, but the database is in 2000 format. In '97, I think I
could have easily done this using joins, but I kept getting "could not
delete from specified tables" errors. Some google searching has
indicated I need to use a subquery. After many failed attempts with
different approaches, I finally...
|
by: tomlebold |
last post by:
Having problems displaying query results from combo boxes on a sub
form, which is on the same form that is used to select criteria.
This has always worked form me when displaying query results on
another main and sub form.
The requery on the sub form and refresh comands on the main form do
not work when the form is first displayed and when the selection
criteria is changed.
Should I be doing a refresh and then repaint of the sub form.
...
|
by: Robertf987 |
last post by:
Hi,
Yet another thing I need help with I'm affraid. I'll first explain what I want, then I'll try to explain what I have. I'm using Microsoft Access 2000.
What I want is to be able to do a search, taking it's criteria fro a text box on a form. This bit is easy, done it a jazillion times. But I want it to be an "Or" search. If I type in it "Cakes, Cheese", I don't want it to bring up results that contain cakes AND cheese, I want it to bring...
|
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, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| | |
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,...
|
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...
|
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,...
|
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...
|
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...
|
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...
| | |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
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
| |
