Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 255
  • Last Modified:

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.
0
robhas
Asked:
robhas
  • 2
  • 2
  • 2
  • +2
1 Solution
 
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
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
jforbesCommented:
Glad I could be of assistance, QueryDef is ugly, but fast.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now