Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access query through VB

Posted on 1998-09-09
8
Medium Priority
?
254 Views
Last Modified: 2010-04-30
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
Comment
Question by:robhas
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 2

Expert Comment

by:dabellei
ID: 1433890
Post your code so we could look
0
 
LVL 2

Expert Comment

by:richtsteig
ID: 1433891
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
 

Author Comment

by:robhas
ID: 1433892
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.

 
LVL 1

Expert Comment

by:jforbes
ID: 1433893
If the query is working properly in Access, then use the QueryDef method in VB.

-John
0
 
LVL 2

Expert Comment

by:dabellei
ID: 1433894
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
 

Author Comment

by:robhas
ID: 1433895
could not find problem then tried jforbes and using a querydef was the answer. give jforbes an "A".  Thanks to all who helped.
0
 

Expert Comment

by:Lycanthrope
ID: 1433896
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
 
LVL 1

Accepted Solution

by:
jforbes earned 200 total points
ID: 1433897
Glad I could be of assistance, QueryDef is ugly, but fast.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question