Run-time error 94, invalid use of null

[ylp]

HI,

I need read data from a table, my code :

strSQL = "SELECT time,Amount," _
& " Where sampleDateTime = " & "#" & lastDayOfPreviousMonth & "# ;"

Set rs = CurrentDb().OpenRecordset(strSQL)
If rs.EOF Then
'MsgBox .Show(no data)
Else
mTime = rs.Fields("time")
mAmount = rs.Fields("Amount")
End If

rs.Close
Set rs = Nothing

The strSQL in immediate window:
SELECT time,Amount from tblTest Where sampleDateTime = #2/28/2010# ;

And all rs field get null value, but I do have value in the database, what is wrong?

Thanks.

 

[pbaldy]

Does the SQL produce a result if you copy it from the Immediate window to a new query? I note you have a comma after amount while building the SQL, but not in the string from the Immediate window. One of those is wrong. If sampleDateTime contains a time component, you'll need to account for that.

 

[SOS]

Also TIME is an Access Reserved Word (not a good idea to use for a field or object name). You need to enclose it in square brackets in order for it to have a chance at working.

 

[ylp]

Hi, thanks for all your reply.

I check the query and found no data returned, after add one more criteria, got the data. Thanks so much!

Another problem:
I have a continuous form which displays one month's data, but always got one more empty row for every month, how to delete this additional row?

Many thanks.

 

[pbaldy]

It sounds like the row for a new record. Try setting the Allow Additions property to No.

 

[ylp]

HI,
After I set AllowAdditions to No, got an error:

Run-time error 2763, DTPicker returned the error: property is read-only.

And no data displayed at all on the form.

Many thanks!!

 

[ylp]

Hi,
Got same error but in differenct situation, there is nothing in txt1( it holds decimal value), but it runs to Else, then got the same error,thank for reply.

If Me.txt1.Value = "" Then ' Also trie =null, same error
var = 0
Else
var = Me.txt1
End If

 

[SOS]

How about this:

If Len(Me.txt1 & "") = 0 Then 
   var = 0
Else
   var = Me.txt1
End If

 

[ylp]

Thanks very much, it works great!

Any suggestion about allowAdditions?