Syntax for Is Not Null ?

When one of our forms is opened (Current event) I want a message box fired by 
certain conditions (that the Ref control does have a value, but the Country 
control is empty). I have tried various bits of code along the lines shown 
below, but keep getting runtime errors. Wot's wrong with the following, 
please?

If [Me.Ref] is not null and [Me.Country] = "" Then MsgBox "Please enter the 
country!"

Many thanks
CW

0
Utf
11/27/2009 1:45:01 PM
access 16762 articles. 3 followers. Follow

5 Replies
1046 Views

Similar Articles

[PageSpeed] 29

CW -

Try this:

If (not isnull([Me.Ref])) AND IsNull([Me.Country]) Then MsgBox "Please enter 
the 
country!"


-- 
Daryl S


"CW" wrote:

> When one of our forms is opened (Current event) I want a message box fired by 
> certain conditions (that the Ref control does have a value, but the Country 
> control is empty). I have tried various bits of code along the lines shown 
> below, but keep getting runtime errors. Wot's wrong with the following, 
> please?
> 
> If [Me.Ref] is not null and [Me.Country] = "" Then MsgBox "Please enter the 
> country!"
> 
> Many thanks
> CW
> 
0
Utf
11/27/2009 2:01:01 PM
CW wrote:

> When one of our forms is opened (Current event) I want a message box fired
> by certain conditions (that the Ref control does have a value, but the
> Country control is empty). I have tried various bits of code along the
> lines shown below, but keep getting runtime errors. Wot's wrong with the
> following, please?
> 
> If [Me.Ref] is not null and [Me.Country] = "" Then MsgBox "Please enter
> the country!"
> 
> Many thanks
> CW

These are for queries...
[SomeField] Is Null 
[SomeField] Is Not Null

In VBA Code use...
IsNull(SomeValue)
Not IsNull(SomeValue)

0
Rick
11/27/2009 2:13:43 PM
Thanks - that seems to work, except that the MsgBox appears before the form 
opens, and seems to be modal i.e. prevents the form actually opening until 
the msg box is closed. I am using On Current, but I have tried On Load too - 
is there some other event to which I could attach this code and it would then 
not fire the message until the form was actually open?
Many thanks
CW

"Rick Brandt" wrote:

> CW wrote:
> 
> > When one of our forms is opened (Current event) I want a message box fired
> > by certain conditions (that the Ref control does have a value, but the
> > Country control is empty). I have tried various bits of code along the
> > lines shown below, but keep getting runtime errors. Wot's wrong with the
> > following, please?
> > 
> > If [Me.Ref] is not null and [Me.Country] = "" Then MsgBox "Please enter
> > the country!"
> > 
> > Many thanks
> > CW
> 
> These are for queries...
> [SomeField] Is Null 
> [SomeField] Is Not Null
> 
> In VBA Code use...
> IsNull(SomeValue)
> Not IsNull(SomeValue)
> 
> .
> 
0
Utf
11/27/2009 3:04:02 PM
CW -

You can try the GotFocus event of the first control (in tab order) on your 
form.
-- 
Daryl S


"CW" wrote:

> Thanks - that seems to work, except that the MsgBox appears before the form 
> opens, and seems to be modal i.e. prevents the form actually opening until 
> the msg box is closed. I am using On Current, but I have tried On Load too - 
> is there some other event to which I could attach this code and it would then 
> not fire the message until the form was actually open?
> Many thanks
> CW
> 
> "Rick Brandt" wrote:
> 
> > CW wrote:
> > 
> > > When one of our forms is opened (Current event) I want a message box fired
> > > by certain conditions (that the Ref control does have a value, but the
> > > Country control is empty). I have tried various bits of code along the
> > > lines shown below, but keep getting runtime errors. Wot's wrong with the
> > > following, please?
> > > 
> > > If [Me.Ref] is not null and [Me.Country] = "" Then MsgBox "Please enter
> > > the country!"
> > > 
> > > Many thanks
> > > CW
> > 
> > These are for queries...
> > [SomeField] Is Null 
> > [SomeField] Is Not Null
> > 
> > In VBA Code use...
> > IsNull(SomeValue)
> > Not IsNull(SomeValue)
> > 
> > .
> > 
0
Utf
11/27/2009 5:09:01 PM
You are always seeing this message probably because the recordset is empty 
or that you are on a new record.  You should check for these two 
possibilities before testing for individual values.

-- 
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server 
(French)


"CW" <CW@discussions.microsoft.com> wrote in message 
news:F68A2546-3EDE-4AAD-957A-641FD6F86304@microsoft.com...
> Thanks - that seems to work, except that the MsgBox appears before the 
> form
> opens, and seems to be modal i.e. prevents the form actually opening until
> the msg box is closed. I am using On Current, but I have tried On Load 
> too -
> is there some other event to which I could attach this code and it would 
> then
> not fire the message until the form was actually open?
> Many thanks
> CW
>
> "Rick Brandt" wrote:
>
>> CW wrote:
>>
>> > When one of our forms is opened (Current event) I want a message box 
>> > fired
>> > by certain conditions (that the Ref control does have a value, but the
>> > Country control is empty). I have tried various bits of code along the
>> > lines shown below, but keep getting runtime errors. Wot's wrong with 
>> > the
>> > following, please?
>> >
>> > If [Me.Ref] is not null and [Me.Country] = "" Then MsgBox "Please enter
>> > the country!"
>> >
>> > Many thanks
>> > CW
>>
>> These are for queries...
>> [SomeField] Is Null
>> [SomeField] Is Not Null
>>
>> In VBA Code use...
>> IsNull(SomeValue)
>> Not IsNull(SomeValue)
>>
>> .
>> 


0
Sylvain
11/27/2009 7:01:08 PM
Reply:

Similar Artilces:

Syntax for testing the value of a boolean in integration manager
Hi All I'm imnporting customers to GP10 with IM and I want to set the address ID of the billing address based on a bit field in my DB. I have a field called "InvoiceAddress" which has a user-defined name (e.g. "Billing") I want to put a script on the Bill To Address filed, that checks if the InvoiceAddress field is true. If it is, it sets the address id to the the user defined name (e.g. Billing). I can't figure out how to test if the bit field is true. I've tried: If SourceFields(ImportSource.InvoiceAddress) = True Then .... End if Also tried = -1 = '...

Syntax to check protected status of a worksheet?
I've used ActiveWorkbook.ProtectStructure to return a True/False value indicating that a workbook is password protected. Does similar syntax exist for the active sheet? I have tried a number of variations but can't find it, and for some reason (reason conversion to XL 2003?) I don't get those popup windows that suggest my next choice of code words. Any ideas? Thanks. You can protect a few different things on a worksheet. Dim wks as worksheet set wks = activesheet If wks.ProtectContents _ Or wks.ProtectDrawingObjects _ Or wks.Prote...

Conditional formating (null) cell?
Hi, Is it possible to format a blank(null) cell so that it shows the text "Not valid!" in red, when no value has been entered. //Cassini --- Message posted from http://www.ExcelForum.com/ Format the cell to be filled in red if it is blank (I don't know how to get the text to appear without a lot of ugly programming). Either of these formulas set it off: =ISBLANK(A1) =LEN(A1)=0 - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Cassini < wrote: > Hi, > Is it possible to f...

when writing an IF statement what is the syntax for "Not Equal to.
If statement for greater than would be =IF(B12>0,B12) What would it be for NOT EQUAL to ? one way: =IF(B12<>0,B12) which will return FALSE if B12 = 0 In article <2A4F6074-C4A9-4617-8D8E-A4C011B35B6E@microsoft.com>, "NEEDTOKNOW" <NEEDTOKNOW@discussions.microsoft.com> wrote: > If statement for greater than would be =IF(B12>0,B12) > > What would it be for NOT EQUAL to ? ...

how to make sure a xsl document has valid xsl syntax? i tried loading it into an xml document but that doesnt show syntax errors inside attributes such as "foo/bar" vs "bar\foo"
how to make sure a xsl document has valid xsl syntax? i tried loading it into an xml document but that doesnt show syntax errors inside attributes such as "foo/bar" vs "bar\foo" Daniel wrote: > how to make sure a xsl document has valid xsl syntax? i tried loading it > into an xml document but that doesnt show syntax errors inside attributes > such as "foo/bar" vs "bar\foo" System.XML performs only checks for the well-formedness of Documents, not for validity. You need a Schema for XSLT wich defines Regular Expressions to check for "sy...

OR syntax
I want to insert 1 in a cell if the value in another cell matches one or more of 5 other cells. I have tried =IF(OR(C20=C5,D5,E5,F5,G5),1,"-") but does not seem to work. Is this syntax is incorrect? May be there is a better way to do this? Your help will be much appreciated. Try it this way: =3DIF(OR(C20=3DC5,C20=3DD5,C20=3DE5,C20=3DF5,C20=3DG5),1,"-") Hope this helps. Pete On Jan 14, 4:00=A0pm, "Dave" <dave@dave> wrote: > I want to insert 1 in a cell if the value in another cell matches one or > more of 5 other cells. =A0I have tried =...

Empty (NUll) value in cell, not empty string?
I have cell (D1) with this formula: =IF(ISNA(VLOOKUP(C1,Navne,2,FALSE)),"";VLOOKUP(C1,Navne,2,FALSE)) If C1 is N/A, it returns an empty string. The problem is, you cant multiply an empty string. So my next formula =D1*10 it gives me a VALUE error. I would like to give me a 0 in stead. I know I can change ti formula to insert a zero instead of the "", but is there other way you can do it? I would like to avouid the zero-value in D1. I know I can make conditonal formatting of D1, or suppress the showing of zeros. Jan I forgot to say, the reason I cannot use a zero in ...

open a message box if a query is null during an autoexec macro
Hope someone can help with this. I have a query that runs using an AutoExec macro when the database opens. What I want is for a message box to open instead of the query results window, telling me if the query produced any records. I can do this easily enough through a command button on a form, but cant figure out how to do it through an autoexec macro at startup. Any suggestions? You need to have your Autoexec macro run a VB function (macro action RunCode)that executes the query and captures the count of records retrieved. Dim strSQL as string Dim k as integer Dim rs a...

invalid syntax
Help please. Access 97 query expression. SSD and PSD are date fields in my database. If SSD is not null and SSD>PSD, I want to display PSD. Otherwise I want to display SSD. I have tried several versions of the following: iif(and(ssd is not null,ssd>psd),psd,ssd) but I continue to receive the "invalid syntax...." error message. Thanks in advance. CHW On Thu, 6 Mar 2008 16:38:18 -0500, CHW wrote: > Help please. > > Access 97 query expression. > SSD and PSD are date fields in my database. > > If SSD is not null and SSD>PSD, I want to display PSD...

Modify POSH script to quit if null value is returned
The following article provides a POSH script to bulk enable agent proxy on systems within SCOM. The only problem I am having is that if a null value is returned then it bombs and goes into an infinite loop. How can I modify so that if a null value is returned the script just quits? For example when I have the script identify systems with a class of "Microsoft.Exchange.ServerRole.2003" and I don't have any Exchange 2003 servers in our environment then I get a message "Get-MonitoringObject : Cannot bind arguement to parameter 'Monitoring class' because...

Formula Syntax
I know how to test if a field is EQUAL to something, how do I test to see if a field contains something? So if I have 3 tasks with Descriptions "HAT" and "TOP HAT" and "HAT GREEN" How do make an Iif statement true by searching for anything with HAT in it? Hi, Experiment with the Instr function. This worked for me: IIf(InStr(1,[Text2],"Hat"),Yes,No) -- Rod Gill Microsoft MVP for Project - http://www.project-systems.co.nz Author of the only book on Project VBA, see: http://www.projectvbabook.com "koolkat" <koolk...

i get a message that says outlook addins are missing-syntax prob
Wehn I open outlook I get a series of pop-up messages that say various extension configuration files cannot be opened. They all seem to be add-ins-it goes on to say that there is an error or format prob in the syntax of the files. I am having a general prob with outlook-if I try to add items to calendar they cannot be saved-says low memory or system resources. But lots of memory available bilbobo <bilbobo@discussions.microsoft.com> wrote: > Wehn I open outlook I get a series of pop-up messages that say various > extension configuration files cannot be opened. They all seem ...

Correct Syntax 10-06-07
Is this correct in refering to a table in an external database? Thanks DS Me.TxtVoids = Nz(DCount("VoidID", "\\Backoffice\Warehouse\History.mdb\tblVoids","VoidID>1") I doubt that would work. Perhaps you could use a query such as this: SELECT Count("*") AS HowMany FROM tblVoids IN "\\Backoffice\Warehouse\History.mdb" WHERE VoidID > 1; -- 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. "DS" <bootybox...

Syntax
What is the correct syntax of the Excel formula for this expression: If A1="DOG" or if B1="CAT" or if C1="POSSUM" or if D1="FERRET" then "YES" else "NO". =IF(OR(A1="Cat",B1="Dog",C1="Possum",D1="Ferret"),"Yes","No") -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) "gary" <anonymous@discussions.microsoft.com> wrote in message news:2db201c45c46$8e0b7c40$3a01280a@phx.gbl... > ...

Why is Access VBA syntax different from Excel syntax?
I just started using Access VBA 2003 and I came across 2 very odd things: Why is the syntax different from Excel? In my Excel macros I use the Microsoft ActiveX Data Objects 2.8 Library. Here is a code sample in Excel: Dim cnn As New Connection Dim rst As New Recordset Dim strSQL As String cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=test.mdb" Set rst = Nothing strSQL = "SELECT * FROM Table1" rst.Open strSQL, cnn When I tried this in Access it didn't like the "New" qualifier and Open was not a function of the recordset class. W...

Rules Syntax
I have been trying to eliminate some junk emails by writing rules with wildcards in them. Example: to send dzqjnl235@canada.com to the deleted items folder I made a rule that looks for *@canada.com. The rule does nothing and I can't find specific help. Is there something wrong with the syntax or is there something that I have to do to turn the rules on? ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ...

Syntax
Can someone help me to figure what is wrong with my syntax? Thanks... How to open with this ( " ) and sometimes with this ( ' )? SQLString = "insert into WIPRawDetails (JobNumber, PartNumber, W_KittedQty) " & _ "select '" & Me.txtJobNumber & "', " & _ '"& Me.txtPartNumber & "', " &_ '" & Me.I_Qty & "' , " &_ from WIPRawDetails " -- Message posted via http://www.accessmonster.com For one thing, using the single quotes around y...

Syntax error when running SQL Insert query
Hi, When I run the code below I receive an error message of Incorrect syntax near the keyword 'primary' after executenonquery is run. Primary being my table name for my database. I've tried to simplify this code down as much as I can, but I can't seem to fix the syntax issue. If you see what I did wrong please let me know, I'm at whits end. As for my DB table, all I have is one field which is set to text, so the data being inserted should work fine. This isn't being rejected by the SQL server itself since nothing shows in the logs. Database connection is func...

Syntax for ()
I want this to read out as (1) No Sale right now its 1 No Sale Where or how do I put the () around the number? Thanks DS =Count(*) & " " & IIf(Count(*)=1,"No Sale","No Sales") On Thu, 10 May 2007 13:22:52 -0500, DS wrote: > I want this to read out as (1) No Sale > right now its 1 No Sale > > Where or how do I put the () around the number? > > Thanks > DS > > =Count(*) & " " & IIf(Count(*)=1,"No Sale","No Sales") ="(" & Count(*) & ") &qu...

Syntax error in DLookup expression
I am getting a syntax error that there's a missing operator in query expression 'ShopID ='. From this it almost seems that it isn't reading the True statement in our expression which should disregard that "false" part of the expression altogether. Any ideas how to fix this? Here's the expression: IIf(IsNull(Me.ShopName), "The owner has not yet chosen a repair shop.", _ DLookup("Expr1", "qryShopReturn", "ShopID = " & Me!ShopName) & " "). ShopName is a cbo that is populated by tblShop where Sho...

Syntax problem.
I have a syntax problem that is confusing me. I am using a deque of pointers-to-a-class. In my code I use an iterator to the deque. I want to modify a value within the class. Normally I would use something like itMyClassDeque-> to modify the value. But itMyClassDeque-> isn't accessing a class its accessing a pointer-to-a-class. So my syntax isn't right. I'm just confused. How do I do this? class MyClass { public: int m_MyInt; }; std::deque<MyClass *> MyClassDeque; ProcessData() { std::deque<MyClass *>::iterator itMyclassDeque; .... itMyClas...

Syntax with "IF" statement...
Trying to resolve a situation that I've got posted elsewhere on here maybe I can do it this way if someone can help me with the syntax fo using "IF". Say that column G is numbered 1-5, and column H has values of 100, 133 322, 440, 560. I'll enter a number (1-5) in col A, and I want this to happen in col B and I'll express it in human language: If A2=3 then B2=322...If A1= then B1=560. In other words, I post a number in A, then go search G for the sam number, then post the corresponding H value into B. I've tried the syntax every way but the right way, I guess...

Syntax error in sub
Converted to 2000 and get a syntax error in this sub at this line: strSQLRestore = "UPDATE Employees " & _ "SET Country = 'USA' " & _ "WHERE Country = 'United States'" Here is the full sub... Sub RecordsAffectedX() Dim dbsNorthwind As Database Dim qdfTemp As QueryDef Dim strSQLChange As String Dim strSQLRestore As String Set dbsNorthwind = OpenDatabase("Northwind.mdb") With dbsNorthwind ' Print report of contents of the Employees ' table. Debug...

Help on syntax
I am very new to VBA and am looking for any help with this problem. I will try and be as clear as possible. First, here is the initial code. Sub calc() i = 35 x = 1 Do Sheets("sheet1").Range("G97").Offset(x - 1, 0) = Sheets("sheet1"). Range("F97").Value Sheets("sheet1").Range("H97").Offset(x - 1, 0) = Sheets("sheet1"). Range("F98").Value Sheets("sheet1").Range("B97") = i - x + 1 x = x + 1 Loop Until i - x + 1 < -35 End Sub This works very w...

LDAP Syntax
Hi! I want that my IMAP users (Outlook 2003 clients) can access Global Address List in Exchange 2003. I read that I should create a LDAP address book connection in Outlook. my ldap server: gc my domain: mycompany.net my organizational unit: myusers my user: muster What ist the exactly syntax and configuration for that user in Outlook 2003? Is it: uid=muster,ou=myusers,dc=gc,dc=mycompany,dc=net? How about with search base? Should I use for it dc=root? Best Regards Mustafa ...