Access query through VB

I have a query which I created in Access and it displays the correct data. Through VB I am trying to check to see if a particular record contains a null value in a specific field.  The IsNull works fine with a table but I am having trouble using it with a query.
robhasAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
jforbesConnect With a Mentor Commented:
Glad I could be of assistance, QueryDef is ugly, but fast.
0
 
dabelleiCommented:
Post your code so we could look
0
 
richtsteigCommented:
Interpreting your question, your problem may be the difference between the VisualBasic or Access function "IsNull" and the correct SQL-Syntax IS NULL

I persume, that you are using Isnull in the WHERE clause, so try changing your query from

SELECT....
WHERE IsNull(YourField)

to

SELECT ....
WHERE YourField IS NULL
 
 
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
robhasAuthor Commented:
Posting code I am using the IsNull in code not the query.
The query works excellent in access providing the correct data

CboCategory_click

'On Error GoTo err_Handler
   
    CategorySQL

    Set Data1.Recordset = rscategory
    dbgCurrentMonth.ReBind

    cboGroup.Clear
'Checks the value of groupview. If true then the group cbo is cleared and
'repopulates with the newest selection in the category cbo
    Dim sqlstr As String
    Dim bCheckEmptyGroup As Boolean
    Dim rstTest As Recordset
    'sqlstr = "SELECT GROUPNAME FROM CATEGORYGROUP where CATEGORYNAME = '" & _
    'cboCategory.Text & "'order by groupname;"
    sqlstr = "SELECT CATEGORYNAME, GROUPNAME FROM qryCatNoGroup WHERE CATEGORYNAME = '" & _
              cboCategory.Text & " ';"
    'Set rst = dbCurrent.OpenRecordset(sqlstr)
   
    qryCatNoGroup.SQL = sqlstr
    Set rstTest = qryCatNoGroup.OpenRecordset

   
   
    bCheckEmptyGroup = IsNull(qryCatNoGroup.Fields("GROUPNAME"))
             
    If bCheckEmptyGroup = True Then
        cboGroup.Clear
        cboGroup.Enabled = False
        lblGroup.Enabled = False
    Else
        cboGroup.Enabled = True
        lblGroup.Enabled = True
        rst.MoveFirst
        Do Until rst.EOF = True
            frmGrossRevenue.cboGroup.AddItem (rst.Fields("GROUPNAME"))
            rst.MoveNext
        Loop
    End If



***At the bCheckEmptyGroup = IsNull(qryCatNoGroup.Fields("GROUPNAME")) in debug mode qryCatNoGroup says Item not found in this collection then next step =
Runtime error:MS jet database engine cant find input table or query qryCatNoGroup.

MDatabase Module contains:
Option Explicit
Public dbCurrent As Database
Public rstGroup As Recordset
Public rstFigures As Recordset
Public rstCategoryAccntCode As Recordset
Public rst As Recordset
Public qryCatNoGroup As QueryDef

Public Sub mOpenDatabase()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Procedure Name:  Module level mOpenDatabase
'Author: Robert Hasselbach
'Date:
'Description: Opens Primary Plus Revenues database
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Set dbCurrent = OpenDatabase("C:\VB\projects\work\PPRevenues.mdb")
   
End Sub
Public Sub mSetRecordsets()
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Procedure Name:  Module level mSetRecordsets
'Author: Robert Hasselbach
'Date:
'Description: Sets recordset names to corresponding table
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Set rstGroup = dbCurrent.OpenRecordset("CATEGORYGROUP", dbOpenDynaset)
    Set rstFigures = dbCurrent.OpenRecordset("FIGURES", dbOpenDynaset)
    Set rstCategoryAccntCode = dbCurrent.OpenRecordset("CATEGORYACCNTCODE", dbOpenDynaset)
    Set qryCatNoGroup = dbCurrent.QueryDefs("CATEGORYACCNTCODENOCATEGORYGROUP")
End Sub

0
 
jforbesCommented:
If the query is working properly in Access, then use the QueryDef method in VB.

-John
0
 
dabelleiCommented:
it look like you don't have any record return that's why it said item not found in this collection.

Maybe you should test if your recordset is eof before doing any action in your code.
0
 
robhasAuthor Commented:
could not find problem then tried jforbes and using a querydef was the answer. give jforbes an "A".  Thanks to all who helped.
0
 
LycanthropeCommented:
So is this question done? If not, try IsNull(), IsEmpty() and = "" with rst.Fields("GROUPNAME") to determine a null value. One or more of those tests should work.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.