Solved

Access query through VB

Posted on 1998-09-09
8
247 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
  • 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
 
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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 50 total points
ID: 1433897
Glad I could be of assistance, QueryDef is ugly, but fast.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

706 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now