Checking a value in a recordset

S

Stewart

I'm trying to test a 4 field, 1 row table, on form_open, to determine if
any one of the fields are empty or not.

But using this example, my If (Null or "")test is ignored:

Dim db As Database, RS As Recordset
Set db = DBEngine.Workspaces(0).Databases(0)
Set RS = db.OpenRecordset("SELECT * FROM tblSerial")
If RS.BOF And RS.EOF Then
'Do Error indication here
End If
Me!Testbox = RS![BaudRate] 'This does display value or blank
If RS![BaudRate]= Null Then 'Never happens regardless of value
MsgBox "Baud Rate empty",,"Missing data"
End If


Is there a quick way to test to see if any field is empty? Or just If
test each, one by one?

Any suggestions appreciated
 
J

John Spencer

Or another alternative to test for both null and zero-length strings

If Len(RS!BaudRate & vbNullString) = 0 Then

or even more paranoid - null, zero-length strings, strings that are all
spaces

If Len(Trim(RS!BaudRate & VbNullString)) = 0 Then


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Alex Dybenko said:
Hi,
try:
If isnull(RS![BaudRate]) Then

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

Stewart said:
I'm trying to test a 4 field, 1 row table, on form_open, to determine if
any one of the fields are empty or not.

But using this example, my If (Null or "")test is ignored:

Dim db As Database, RS As Recordset
Set db = DBEngine.Workspaces(0).Databases(0)
Set RS = db.OpenRecordset("SELECT * FROM tblSerial")
If RS.BOF And RS.EOF Then
'Do Error indication here
End If
Me!Testbox = RS![BaudRate] 'This does display value or blank
If RS![BaudRate]= Null Then 'Never happens regardless of value
MsgBox "Baud Rate empty",,"Missing data"
End If


Is there a quick way to test to see if any field is empty? Or just If
test each, one by one?

Any suggestions appreciated
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need to create a dynamic recordset using generic field names 8
Data type conversion error 0
traversing a large recordset 4
Compile Error - Don't know WHY? 6
do loop through a table 2
Iterate/loop through a form to copy value from one control to anot 2
Cannot update DAO Recordset 6
Upsize to SQL ODBC error on rs!update 3

Top