NULL values

Working with a downloaded table form a Lotus Notes contact manager. Now in
access call tblclients.
I query certain info. to update the records.
BUT...'Is Null' does not work. It comes back empty and there are definitely
empty contents in the fields I chose. I know how to do this as it works in
my other tables on other databases.
The only difference I can see in this table as opposed to tables created
directly in Access is the Field Design property called Unicode Compression
saying No instead of the default Yes.

I changed the Unicode to Yes in case this had something to do with it and
that resulted in no change.

Any ideas? There are many records with empty contents and it is up to me to
update the contents. There are 60,000 records, I have to be able to query
for Null values.

Deborah


0
Deborah
8/25/2003 8:34:20 PM
access.conversion 3037 articles. 0 followers. Follow

6 Replies
1049 Views

Similar Articles

[PageSpeed] 47

"Deborah Mowry" <dmowry@sympatico.ca> wrote in message
news:mpu2b.1710$_F1.165593@news20.bellglobal.com...
> Working with a downloaded table form a Lotus Notes contact manager. Now in
> access call tblclients.
> I query certain info. to update the records.
> BUT...'Is Null' does not work. It comes back empty and there are definitely
> empty contents in the fields I chose. I know how to do this as it works in
> my other tables on other databases.
> The only difference I can see in this table as opposed to tables created
> directly in Access is the Field Design property called Unicode Compression
> saying No instead of the default Yes.
>
> I changed the Unicode to Yes in case this had something to do with it and
> that resulted in no change.
>
> Any ideas? There are many records with empty contents and it is up to me to
> update the contents. There are 60,000 records, I have to be able to query
> for Null values.

I may be that the fields contain zero length strings rather than Nulls.  Test for
both and see if it works.

WHERE SomeField Is Null OR SomeField = ""


0
Rick
8/25/2003 8:54:18 PM
"Rick Brandt" <RBrandt@Hunter.Com> wrote in message
news:bidt25$896q1$1@ID-98015.news.uni-berlin.de...
> "Deborah Mowry" <dmowry@sympatico.ca> wrote in message
> news:mpu2b.1710$_F1.165593@news20.bellglobal.com...
> > Working with a downloaded table form a Lotus Notes contact manager. Now
in
> > access call tblclients.
> > I query certain info. to update the records.
> > BUT...'Is Null' does not work. It comes back empty and there are
definitely
> > empty contents in the fields I chose. I know how to do this as it works
in
> > my other tables on other databases.
> > The only difference I can see in this table as opposed to tables created
> > directly in Access is the Field Design property called Unicode
Compression
> > saying No instead of the default Yes.
> >
> > I changed the Unicode to Yes in case this had something to do with it
and
> > that resulted in no change.
> >
> > Any ideas? There are many records with empty contents and it is up to me
to
> > update the contents. There are 60,000 records, I have to be able to
query
> > for Null values.
>
> I may be that the fields contain zero length strings rather than Nulls.
Test for
> both and see if it works.
>
> WHERE SomeField Is Null OR SomeField = ""

I've always preferred:

WHERE Len([SomeField] & "") = 0

or, even better

WHERE Len(Trim$([SomeField] & "")) = 0

(Believe it or not, it's faster to check for a length of zero than to
compare it to "". And it's faster to use vbNullString than "", but
unfortunately I don't believe you can use the constant in a query.)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele



0
Douglas
8/25/2003 9:19:26 PM
I did a query for "" and it most certainly worked. I don't understand why
though! I have never done it before, what is different in this table that
made it 'zero length strings' as you mentioned? I changed the Unicode thing
and there is nothing typed in those fields. How is the import being saved
and is there something I can change to put it the way I would have expected
it to work...meaning other users will expect to type Is Null.

Please advise and thank you very much for the answer.

"Rick Brandt" <RBrandt@Hunter.Com> wrote in message
news:bidt25$896q1$1@ID-98015.news.uni-berlin.de...
> "Deborah Mowry" <dmowry@sympatico.ca> wrote in message
> news:mpu2b.1710$_F1.165593@news20.bellglobal.com...
> > Working with a downloaded table form a Lotus Notes contact manager. Now
in
> > access call tblclients.
> > I query certain info. to update the records.
> > BUT...'Is Null' does not work. It comes back empty and there are
definitely
> > empty contents in the fields I chose. I know how to do this as it works
in
> > my other tables on other databases.
> > The only difference I can see in this table as opposed to tables created
> > directly in Access is the Field Design property called Unicode
Compression
> > saying No instead of the default Yes.
> >
> > I changed the Unicode to Yes in case this had something to do with it
and
> > that resulted in no change.
> >
> > Any ideas? There are many records with empty contents and it is up to me
to
> > update the contents. There are 60,000 records, I have to be able to
query
> > for Null values.
>
> I may be that the fields contain zero length strings rather than Nulls.
Test for
> both and see if it works.
>
> WHERE SomeField Is Null OR SomeField = ""
>
>


0
Deborah
8/25/2003 9:19:52 PM
There is a possible workaround, although not really a 
solution. I run into similar problems extracting data from 
an old, proprietory HP OS with a database written in 
FORTRAN. 

What I have to do in that case is to use the linked table 
in a make-table query, and create a new table. Then, I run 
all of the queries from the new table, with whatever 
filtering criteria that I want, and it seems to work fine. 
I use the autoexec macro sometimes to refresh the table 
when the database is opened. It's not exactly real-time 
data, but is a possible workaround for less-than-friendly 
ODBC links.

>-----Original Message-----
>Working with a downloaded table form a Lotus Notes 
contact manager. Now in
>access call tblclients.
>I query certain info. to update the records.
>BUT...'Is Null' does not work. It comes back empty and 
there are definitely
>empty contents in the fields I chose. I know how to do 
this as it works in
>my other tables on other databases.
>The only difference I can see in this table as opposed to 
tables created
>directly in Access is the Field Design property called 
Unicode Compression
>saying No instead of the default Yes.
>
>I changed the Unicode to Yes in case this had something 
to do with it and
>that resulted in no change.
>
>Any ideas? There are many records with empty contents and 
it is up to me to
>update the contents. There are 60,000 records, I have to 
be able to query
>for Null values.
>
>Deborah
>
>
>.
>
0
Joel
8/25/2003 9:23:25 PM
I appreciate any ideas; however, making a new table from my query did not
change anything about the field content. I still cannot query/filter on Null
values.
I also changed the Allow zero length and Unicode compression to every
possible combination and that also did nothing.

This is most frustrating. It can be done on any table in Access but not this
imported one.
Is there any chance it is the middle step - meaning, they first exported it
as a Lotus WK4 file then I made a table by importing the WK4 file. The
Contact manager does not go directly to Access.

Just a thought.
Deborah


"Joel Wiseheart" <joel.wiseheart@airshowinc.com> wrote in message
news:031601c36b4f$1e436eb0$a601280a@phx.gbl...
> There is a possible workaround, although not really a
> solution. I run into similar problems extracting data from
> an old, proprietory HP OS with a database written in
> FORTRAN.
>
> What I have to do in that case is to use the linked table
> in a make-table query, and create a new table. Then, I run
> all of the queries from the new table, with whatever
> filtering criteria that I want, and it seems to work fine.
> I use the autoexec macro sometimes to refresh the table
> when the database is opened. It's not exactly real-time
> data, but is a possible workaround for less-than-friendly
> ODBC links.
>
> >-----Original Message-----
> >Working with a downloaded table form a Lotus Notes
> contact manager. Now in
> >access call tblclients.
> >I query certain info. to update the records.
> >BUT...'Is Null' does not work. It comes back empty and
> there are definitely
> >empty contents in the fields I chose. I know how to do
> this as it works in
> >my other tables on other databases.
> >The only difference I can see in this table as opposed to
> tables created
> >directly in Access is the Field Design property called
> Unicode Compression
> >saying No instead of the default Yes.
> >
> >I changed the Unicode to Yes in case this had something
> to do with it and
> >that resulted in no change.
> >
> >Any ideas? There are many records with empty contents and
> it is up to me to
> >update the contents. There are 60,000 records, I have to
> be able to query
> >for Null values.
> >
> >Deborah
> >
> >
> >.
> >


0
Deborah
8/25/2003 11:01:18 PM
esque je peu recevoir du francais
"Deborah Mowry" <dmowry@sympatico.ca> a �crit dans le message de
news:mpu2b.1710$_F1.165593@news20.bellglobal.com...
> Working with a downloaded table form a Lotus Notes contact manager. Now in
> access call tblclients.
> I query certain info. to update the records.
> BUT...'Is Null' does not work. It comes back empty and there are
definitely
> empty contents in the fields I chose. I know how to do this as it works in
> my other tables on other databases.
> The only difference I can see in this table as opposed to tables created
> directly in Access is the Field Design property called Unicode Compression
> saying No instead of the default Yes.
>
> I changed the Unicode to Yes in case this had something to do with it and
> that resulted in no change.
>
> Any ideas? There are many records with empty contents and it is up to me
to
> update the contents. There are 60,000 records, I have to be able to query
> for Null values.
>
> Deborah
>
>


0
Germain
8/25/2003 11:29:24 PM
Reply:

Similar Artilces:

spin button value
Hi Does the value in spinbutton properties have to be numeric or can it be a day eg mon tues wed etc if so how do I dio this Thanks tina A Forms toolbar spinner control returns an integer. You can have another cell with a formula that references the cell linked to the spinner like =CHOOSE(A1,"Sun","Mon","Tues","Weds","Thurs","Fri","Sat") Here A1 is linked to the spinner. You'd want to limit the spinner's minimum value to 1 and maximum value to 7 in this case. -- Jim Rech Excel MVP "tina" <tina@...

if A1=Null and B1=A1 why is result in B1=0 ??
if A1=Null and B1=A1 why is result in B1=0 ?? because of this I get wrong result for average calculations: average for (6,8,0) <> average for (6,8,null) Use =IF(A1="","",A1) -- HTH RP (remove nothere from the email address if mailing direct) "WGeorg" <WGeorg@discussions.microsoft.com> wrote in message news:09FCCF25-D0DC-4B1E-BFA1-CEF9A9327C46@microsoft.com... > if A1=Null and B1=A1 why is result in B1=0 ?? > because of this I get wrong result for average calculations: average for > (6,8,0) <> average for (6,8,null) Use this ...

Null Is Null
I've got a text box in a report that's not returning properly. Here's its Control Source: =[Reports]![rptFGPackConfigsPKWeights]![srptFGPKConfigsSUPK_totalwt]! [SumWtg]+IIf(IsNull([Reports]![rptFGPackConfigsPKWeights]! [srptFGPKConfigsTPK_totalwt]![SumWtg]),Null,"") This returns #Error. It's definitely my IIf statement as it returns properly when removed however there are times when [srptFGPKConfigsTPK_totalwt].[SumWtg] will be null. In fact, this subreport will not appear if no records exist. I suspect that this is the problem. How can I write this Control Source t...

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 CW - Try this: If (not isnull([Me.Ref])) AND IsNull([Me.Country]) Then MsgBox "Please enter the country!" -- Daryl S ...

When is a Null not a Null?
I have a form which is used for adding and entering data depending on where it is called from. When called in add mode (acFormAdd) a blank form is openned. When the cursor enters the text box [Name] on the form field I am trying test whether it is empty so that a search form can be openned if a new name is to be added. (I don't want the search form to open in cases when the form already has data in and I am editing rather than adding data). The form is based on a query and the data displayed in [Name] is the result of a calculated field in the query which concatenates firstname and surn...

What is the Null character?
What is the character sequence that Excel uses to represent an empty cell? I am using a formula and when it returns false, I would like for it to represent the cell as if it was empty. I have tried using "" as an empty cell, but excel recognizes that this is a value, not an empty cell. You can't do what you want to do. A formula always returns *something*. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Quinn Ramsey" <Quinn Ramsey@discussions.microsoft.com> wrote in message news:D67F6311-0CCA-48B5-A...

retrieving the XmlEnumAttribute and the enum-values (2)
This post is a followup to the thread "retrieving the XmlEnumAttribute values for an Enum" in this same forum earlier this month, since my last query in that thread went unanswered. I have an enum defined (DotNet v2) as public enum velocityUom { [System.Xml.Serialization.XmlEnumAttribute("m/s")] ms = 21, [System.Xml.Serialization.XmlEnumAttribute("cm/a")] cma, ... } My original post was to ask how I could retrieve the XmlEnumAttribute values for this Enum so that I could create a combo box with values like "m/s", &qu...

Passing Values between forms
I am trying to link to forms. I have a button on the main form (frmCompanies) that needs to open another form when a button is clicked. The new form that opens is frmScheduledActivities. When it open it needs to check to see if there is an existing record (scheduled activity) for the company and if there is no scheduled activity be set to add a new record. When the button is clicked on the main company form (on the on click event) this code runs: Private Sub cmdCallBack_Click() On Error GoTo Err_cmdCallBack_Click Dim stDocName As String Dim stLinkCriteria As String...

Charting null values
I have a chart that is plotting null values. I tried the formula =if(C25="",N/A(),Sum(C22:C25)). I've also deleted the formula to create a completely null cell. I've been to Tools, Options, Chart and selected Plot empty cells as Not plotted, zero and Interpollated As well as turning Plot Visible Cells only (turned it on and off) Nothing seems to make a difference. Any suggestions? Stephanie, what kind of chart are you using? Can you give an example of how your data table looks like? br, Henk "StephanieH" wrote: > I have a chart that is plotting ...

How to Link a cell in an Excel spread sheet to a text value in Visio 2007
I would like to have a text value in Visio take the value of a cell in an Excell spread sheet. Is there a way to do this, at least on opening the Visio Document? Thanks in advance, Marco UCO Lick Observatory Laboratory for Adaptive Optics visio 2007 professional http://office.microsoft.com/en-us/visio/HA100518191033.aspx al "Marco" <null@null.net> wrote in message news:uCcafQkuKHA.1796@TK2MSFTNGP02.phx.gbl... > I would like to have a text value in Visio take the value of a cell in an > Excell spread sheet. Is there a way to do this, at leas...

NULL values
Working with a downloaded table form a Lotus Notes contact manager. Now in access call tblclients. I query certain info. to update the records. BUT...'Is Null' does not work. It comes back empty and there are definitely empty contents in the fields I chose. I know how to do this as it works in my other tables on other databases. The only difference I can see in this table as opposed to tables created directly in Access is the Field Design property called Unicode Compression saying No instead of the default Yes. I changed the Unicode to Yes in case this had something to do with it and ...

How can I check a combo box content or value or something
Hi. I need to check if my combobox has something on it, what I mean is if my users choose any value. I need something looked like: if combo1 then msgbox "you must choose an option on Combo1" exit sub end if How can I do something looked like? Regards, Marco Marco, In the AfterUpdate event of the combo type, if IsNull(combo1.text) then msgbox "you must choose an option on Combo1" End If "Marco" <Marco@discussions.microsoft.com> wrote in message news:92B57DBB-74BC-4177-AB68-A87153654F34@microsoft.com... > Hi. I need to check if my combobox h...

How to MATCH value up a column
I need to match/find the first matching cell up a column. Ideas? TIA! Hi I don't know what you are looking for, but see: MATCH(A2,A1:F1,0) or VLOOKUP(A3,Sheet2!A:B,2,FALSE) -- Please click "yes" if this post helped you! Greatly appreciated Eva "DevourU" wrote: > I need to match/find the first matching cell up a column. Ideas? TIA! Thankx for the reply. Here is my formula:=IF(A11>A10,C10,(IF(A11=A10,A10,"?"))). I need to have IF(A11<A10, search up column A for 1st match, example A4, display next column B4. Clear as mu...

Taking the Mode based on cell value
Could you provide a few more details about what you want to do? -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "sip8316" <sip8316@discussions.microsoft.com> wrote in message news:E3F4DE24-C5E8-4911-96E6-E61A1E56DEF0@microsoft.com... > ...

VLOOKUP: Retrieving Values
Vlookup: I am trying to lookup a value ( SCOTLAND ) on a spreadsheet called ( Data ) in order to populate ceratin cells on my other spreadsheet ( Spreadone ). If the value (SCOTLAND) is located in (Data) I want it to copy the values of specific cells (not all of them ) from the same row into ( Spreadone ). So : CHECK COLUMN N (CLASS) IN SPREADSHEET (DATA) FOR STRING (SCOTLAND) IF THE STRING = SCOTLAND THEN IN SPREADSHEET (Spreadone) POPULATE CELL A7:A25 = ACCOUNT NO (from Data) CELL B7:B25 = CLIENT (from Data) CELL C7:C25 = CUSIP (from Data) CELL D7:D25 = QTY (from Data) CELL E7:E25...

Can I set an audio alert that triggers as Excel cell value chgs?
I would like to add an audio alert to a spreadsheet cell whenever it changes to a specific value? Can this be done? ....I'm not familiar with any audio alerts but, you can use conditional formatting to the cell's format change when it value changes. Go to Format...Conditional Formatting. You can then either set a value or use a formula and set the format (what it will look like when the cell is the value that you set). "SellUnHi" wrote: > I would like to add an audio alert to a spreadsheet cell whenever it changes > to a specific value? Can this be done? L...

PrimaryContactID NULL
Hello, i have discovered a strange behavior of CRM regarding the relationship between accounts and contacts: Some of my accounts show up a referenced contact but do not have a PrimaryContactID GUID in the databasefield. Both where converted from a lead which is refrenced with the correct GUID in the OriginatingLeadID Field in the database? Any idea how this can happen? Regards Sebastian When you convert a lead into an account/contact the contact references the account throught the parent customer field. The account does not reference the contact (this would be the primary contact fi...

Using CORREL with arrays containing null values
XL Gurus... I'm using the CORREL function, but one of the arrays I'm comparing has null values (entered as #N/A), so my result is #N/A. How can I modify my formula to correlate all the points in the 2 arrays, apart from the null values and their corresponding entries in the other array? You might incorporate an IF function, along the lines of IF(ISNUMBER(your array function here),your array function here,0) It seems redundant, but it evaluates the array function to determine if it is numeric. ...

Delete Common Cell Values
I have Excel 2003. Columns A and B have invoice numbers from different time periods. I need a formula that will look in both columns and delete the invoice numbers that appear in both columns A and B. Any help is appreciated. -- Mike Mike, Use two columns of formulas. The first =NOT(ISERROR(MATCH(A2,B:B,False))) The second: =NOT(ISERROR(MATCH(B2,A:A,False))) Copy to match your columns, then copy both columns of formulas and paste special as values. Then sort all columns based on the first column of formulas-converted-to-values, and delete the values in column A where the value i...

Sheet Name Refering to Cell Value
I want to name a sheet to the cell value in A2. Cell A2 has an Alt-enter in the cell. I want the sheet name to be the cell value of the first line only. Can anybody help. TIA Greg One way: Option Explicit Sub testme01() Dim myStr As String Dim vbLFPos As Long With ActiveSheet myStr = .Range("a2").Value vbLFPos = InStr(1, myStr, vblf) If vbLFPos > 0 Then myStr = Left(myStr, vbLFPos - 1) On Error Resume Next .Name = myStr If Err.Number <> 0 Then MsgBox "couldn...

How to clear the lowest 10 values from a list of 30 values
Suppose I have a database of 50 students. Each row starts with the name of the student and then marks of 30 quizes for each student. e.g.- Jack- 10 7 8 5 10 9 8 7 3 6 4 7 3 ........ Millie- 2 3 10 9 8 2 7 1 2 9 2 0 7 9....... Ryan- 4 5 7 8 9 10 2 4 5 7 8 9............ ............................................................... .............................................................. Now I want to count only best 20 quiz marks out of 30 quizes and show the cells containing 10 lowest values as blank. e.g. if i want to clear lowest 5 values for jack- Jack- 10 7 8 _ 10 9 8 7 _ _ _ 7 _ ...

average values
hello, i am trying to calculate averages using excel's median and avarag calculations but after multiple attampts>with no luck!. I have values in 409 cells with only one or two gaps between cells when i use the two sums described above i get very different values and also i get a different answer when i manually calculate. any advice on the best and most accurate sum to use? Thanks for any help -- confusedexcele ----------------------------------------------------------------------- confusedexceler's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1658 Vie...

How to change the string value in the registry?
How to change the "string value" in the registry from the code? As there is a functionality in my application that the user can change the "string value" with some GUI. But I m not able to change it as there is a function RegReplaceKey but its only to take the backup of old file and to replace it with new one and even i dont know how to use this functin to suit my case. RegSetValue/Ex() should do it I think. -Seetharam Look at the Registry APIs. Also, take a look at my Registry class on my MVP Tips site. joe On Sun, 26 Aug 2007 23:59:33 -0700, HItz <hitesh_im...

Nulls in SP
I have a stored procedure (see below) that I need to make the last 2 parameters optional and place a NULL value when they are not sent. Can someone help show me how this is done? Thanks. David ALTER PROCEDURE [dbo].[mc_insHistoryDates] @PeopleLinkID int, @HistoryTypeID int, @HistoryReasonID int, @HistoryDate date, @HistoryDateTo date, @HistoryText varchar(1000) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; INSERT INTO [MCFICore].[dbo].[HistoryDates] ([PeopleLinkID] ...

Hide Sub Report(s) when records are null
I am running Access 2007 sp2 MSO. I have a report that has three (3) sub reports. There are times when not all three sub reports have values to display. Is there a way to code the given report to not be visible if there are no records to report? The second part of this question would be if there is a way to make the report invisible is it also possible to code so that the space where the report would normally reside would not be taken with a blank space (the foot print of where the report normally resides)? -- Bruce ...