473,320 Members | 1,969 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Invalid Use Of Null - No Null Values in table

8
I have a query that looks like this:

SELECT qryPhysicals.NAME, qryPhysicals.UPC, qryPhysicals.SSN, qryPhysicals.PhysDate, qryPhysicals.YearMo, qryPhysicals.TimeElapsed
FROM qryPhysicals
order by qryPhysicals.TimeElapsed desc

This works fine.

However, if I add a where clause,

SELECT qryPhysicals.NAME, qryPhysicals.UPC, qryPhysicals.SSN, qryPhysicals.PhysDate, qryPhysicals.YearMo, qryPhysicals.TimeElapsed
FROM qryPhysicals
Where qryPhysicals.timeelapsed is not null (or any other value)
order by timeelapsed desc

I get "invalid use of null"

I looked at all of the records, all of them contain values.

Any pointers are greatly appreciated.

Thanks

JM420A
Nov 25 '08 #1
3 2253
JM420A
8
I created a make table query, from the values then ran the query from the tbl, it worked out well.
Nov 25 '08 #2
Krandor
50
@JM420A
I don't generally use IS NULL or IS NOT NULL because there are instances where you won't get the results you expect. Example: if the record has an empty string ("") that is not a null so if you are testing for null, you will miss it.

I found it is better to do something like this:
Where Len(qryPhysicals.timeelapsed ) > 0

That way you can catch nulls and empty strings.
Nov 25 '08 #3
rsmccli
52
Indeed. Another trick you can do is to add a null string to whatever value you are testing like so...

If Len(strStringField & "") > 0 Then

OR

If Not Len(strStringField & vbNullString) > 0

...et cetera. One of the Access MVPs (don't remenber which one o_O) told me that doing this makes the Null/Empty String check work in basically every situation.
Nov 26 '08 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: | last post by:
When I execute the following (with an OleDBDataAdapter), wanting to add a row to a visual foxpro table: myrow= datasetTarget.Tables(0).NewRow 'fill all columns here like.. row(i)= myvalue '...
26
by: Agoston Bejo | last post by:
I want to enforce such a constraint on a column that would ensure that the values be all unique, but this wouldn't apply to NULL values. (I.e. there may be more than one NULL value in the column.)...
1
by: Patrick Dunnigan | last post by:
Hi, I am attempting a bulk copy from a c program into SQL Server 2000 using DBLib in freeTDS 0.63 RC11 (gcc 3.4.3, RH 9). I am getting an error message that I cannot find any documentation on. ...
0
by: Antonio Gallardo | last post by:
psql returns: Invalid UNICODE character sequence found(0xc000) in a valid query. Steps to reproduce: 1. createdb -E UNICODE mydbname. 2. create a table with some varchar inside, we will...
6
by: Martin Lacoste | last post by:
Ok, before I headbutt the computer... don't know why when I add criteria in a query, I get an 'invalid procedure call'. I also don't know why after searching the help in access, the various access...
15
by: David | last post by:
Hi, I have built a web application that will be a very high profile application. We had tested it, demonstrated it and shown that it all works. On a dress rehearsal run through, it failed...
9
by: John Sidney-Woollett | last post by:
Hi I'm building a web app where changes to customer orders are logged in the following table, and I worried about the time that it will take to locate records that need further...
2
by: sree reddy | last post by:
..cs using System; using System.Data; using System.Configuration; using System.Collections; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls;
4
by: mattehz | last post by:
Hey there, I am trying to upload old source files and came across these errors: Warning: Invalid argument supplied for foreach() in /home/mattehz/public_html/acssr/trunk/inc_html.php on line 59...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.