using access 2000. Have a form based on a couple of controls, it
finds the last used Inquiry number in a table. So it finds the
highest Inquiry number for the "Office" , "Company" and "YearStamp"
selected by the user.
Code below:
=IIf(IsNull(DMa x("[Inquiry Number]","Inquiry" ,"[Office] = '" &
[Form]![Office Code] & "' and" & "[Company] = '" & [Form]![Company
Code] & "' and " & "[YearStamp] = " &
[Form]![Year])),0,DMax("[Inquiry Number]","Inquiry" ,"[Office] = '" &
[Form]![Office Code] & "' and" & "[Company] = '" & [Form]![Company
Code] & "' and " & "[YearStamp] = " & [Form]![Year]))
I use to just use the DMax but had to change it to an IIF because of
nulls. But this code simply places #ERROR in the control when it is
null.
Am I doing something wrong? Wait obviously I am doing something
wrong. Can someone lead me in the right direction.
I'm doing this iif statement in the control source property.
2  2529 
just off the cuff, have you tried using NZ(dmax etc.) instead of the iif
rdshultz <rd******@noote r.com> posted in
news:87******** *************** ***@posting.goo gle.com using access 2000. Have a form based on a couple of controls, it
finds the last used Inquiry number in a table. So it finds the
highest Inquiry number for the "Office" , "Company" and "YearStamp"
selected by the user.
Code below:
=IIf(IsNull(DMa x("[Inquiry Number]","Inquiry" ,"[Office] = '" &
[Form]![Office Code] & "' and" & "[Company] = '" & [Form]![Company
Code] & "' and " & "[YearStamp] = " &
[Form]![Year])),0,DMax("[Inquiry Number]","Inquiry" ,"[Office] = '" &
[Form]![Office Code] & "' and" & "[Company] = '" & [Form]![Company
Code] & "' and " & "[YearStamp] = " & [Form]![Year]))
I use to just use the DMax but had to change it to an IIF because of
nulls. But this code simply places #ERROR in the control when it is
null.
Am I doing something wrong? Wait obviously I am doing something
wrong. Can someone lead me in the right direction.
I'm doing this iif statement in the control source property.
--
Phil
how about using NZ(<<expression >>) which will convert the Null value
to zero for you?
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Max Sandman |
last post by:
I'm getting increasingly frustrated with C# and its exceptions on null
values. Rather than try to deal with it on a hit-or-miss basis as
exceptions pop up, I thought I should try to learn exactly how C# deals
with null. Of course, there's nothing obvious in the docs like "Dealing
with Null Values" and a search on "null" yielded 500 results, most of
which don't apply. Can anybody point me in the right direction? Or offer
some general...
|
by: Michael |
last post by:
Hi all,
I would like to get people's opinion about executing SQL statements in C#
(or any other .NET language really). I used to create my SQL statement by
building a string and replacing single quote with two single quotes.
Sometimes, I used SqlParameter. Maybe, I'm a bit lazy when I build the SQL
string. Should I always use SqlParameters? What are the
advantages/disadvantages between building SQL string and using SqlParameter?
Does...
|
by: Brian Basquille |
last post by:
Hello all,
Bit of a change of pace now. As opposed to the typical questions regarding
my Air Hockey game, am also working on a Photo Album which uses an Access
Database to store information about photos. This information is held inside
the database (photoDB.mdb) in a table called 'photos' - information being
recorded in there are photo information (photoID, location, phDate,
category, caption).
I have inputted my own photos (not via...
|
by: Angela |
last post by:
Hi
I am building a very simple data entry system with many forms and
input textboxes.
When I leave any part of the form empty (it allows nulls) it seems to
touch the field in the DB and the field is no longer NULL.
I have written a script to test for empty textboxes and insert DB.NULL
which is fine but when I want to load the data back into the form
|
by: Robert Dufour |
last post by:
I have a sub to update a record in a sql server 2000 table.
There's a field FK, which is defined to allow nulls in the table definition,
the field type is integer.
How do I write the sub's definition where the parameter FK is of type
integer but it's value can be NULL?
UpdateDB(ByVal ID as Integer,Optional Byval FK as integer = ? )
Any help would be greatly appreciated
Thanks
| | |
by: rdemyan via AccessMonster.com |
last post by:
I have the following SQL statement in code:
strSQL = "SELECT * FROM TABLEA WHERE F2 = 'SITE'
This returns the record that I expect.
However: the following does not
strSQL = "SELECT * FROM TABLEA WHERE F2 <'SITE'
|
by: Jimmy |
last post by:
If the user dbl clicks the field "vendor" I want it to open frmContacts to a
blank record if there is nothing in the current field and if there is, open
frm Contacts to that record. I have the following code...
If Me.Vendor = Null Then
DoCmd.OpenForm "frmContacts", , , , acFormAdd
Else
DoCmd.OpenForm "frmContacts", , , "ClientID = " & Me!Vendor
End If
|
by: BXB |
last post by:
I have a number in a table that starts off as currency. There are nulls in the data so I'm trying to convert the nulls to zeros and then wind up back in currency, or in fact in any numeric format. I've tried both of these.
Add: Format(nz(,0),"Standard")
Add: Format(nz(,0),"$#,##0.00")
With this I wind up with what looks like numbers but when I export to Excel or try to subtotal at the bottom of the query I don't have numbers that...
|
by: xlar54 |
last post by:
I am writing a parametrized query, where Im building an UPDATE
statement through concatenation, and then adding the parameters such
as:
UPDATE myTable SET = @newcolumna, = @newcolumnb
WHERE = @origcolumna
AND = @origcolumnb
This works fine, unless one of the original database values are a
NULL. Im creating the parameters as:
|
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: 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 tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
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: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
