Using IfNull as criteria for Recordset

[Cosmonaut_99]

Hi,

I have a table called "CRP_Match_Master", where one of the fields called "861_Reference" contains some null entries. I want to use VBA to display the table records on a form, where "861_Reference" has Null entries.

I have tried to achieve this using a DAO recordset, where the recordset criteria is based on IsNull. However, I'm having problems referring field "861_Reference" into the recordset - due to syntax issues, which I can't figure out.

(I'm also not sure whether I can actually use IfNull to create a criterion.)


If anybody could offer any pointers, that would be great.
Cheers.

C_99

======================================================

Private Sub Form_Open()
Dim dbs As DAO.Database
Set dbs = CurrentDb
Dim CRPMatchMaster As TableDef
Set CRPMatchMaster = CurrentDb.TableDefs("CRP_Match_Master")
Dim CRPMatch As DAO.Recordset
Dim CRPMatchFiltered As DAO.Recordset
CRP_Match_Master.Fields ("861_Reference")
Dim 861_Reference As DAO.Field

Set CRPMatch = dbs.OpenRecordset("SELECT * FROM CRP_Match_Master WHERE ((IfNull(861_Reference)) = True) ")
Set CRPMatchFiltered = CRPMatch.OpenRecordset

Do While Not CRPMatchFiltered.EOF
CRPMatchFiltered.Edit
CRPMatchFiltered.Update
CRPMatchFiltered.MoveNext
Loop

Exit Do
CRPMatch.MoveNext

Loop

CRPMatchFiltered.Close
CRPMatch.Close
Set CRPMatchFiltered = Nothing
Set CRPMatch = Nothing
End Sub

 

[pr2-eugin]

Completely missed a point, did a read through....

(I'm also not sure whether I can actually use IfNull to create a criterion.)

No, you cannot use IfNull as a Criteria, it is IsNull.. and you have used it right (except for the Is instead of a If.. What I do not understand is, why have you used.

        CRPMatchFiltered.Edit
        CRPMatchFiltered.Update

When you are not editing anything? Also there is a free Exit Do and Move Next hanging outside a While.

    Exit Do
    CRPMatch.MoveNext
    Loop

Could you give some pointers of what you are doing? Or answer this question, you want a Form to display all records that have a Null Value in the specified field?

 

[Cosmonaut_99]

Thanks for pointing this out.

I'm working with some EDI interface files- which are sent between 2 sites.
Site 1 sends an interfaced message (when a shipment is despatched). When the shipment arrives at Site 2, a new interface message is sent back to Site 1 confirming receipt of the shipment.

If Site 2 doesn't send a message back to Site 1 after a shipment arrives (in my table = a null record), then we may have a problem. However, usually there is a short-time gap of 1 or 2 days between messages, so not all Nulls are necessarily problems.


I'm simply trying to create a form which will monitor all Nulls, to ensure we keep control of the situation. I would like the form to include all fields in the table.. but if I can get the IfNull bit working, I think I can manage to add them in.


I started off by borrowing an example of a DAO recordset, (which used a select criteria and a separate string filter).. and then attempted to wipe out the sections I didn't need. Evidently I should have removed the edit & updates lines too.

So I've removed the edit and update lines..

CRPMatchFiltered.Edit CRPMatchFiltered.Update
... and the Exit Do and Movenext lines are gone also.

Exit Do CRPMatch.MoveNext Loop

However, I still have problems with line :
CRP_Match_Master.Fields ("861_Reference")
...which I don't understand.

Thanks.
C_99
======================================================
Private Sub Form_Open()
Dim dbs As DAO.Database
Set dbs = CurrentDb
Dim CRPMatchMaster As TableDef
Set CRPMatchMaster = CurrentDb.TableDefs("CRP_Match_Master")
Dim CRPMatch As DAO.Recordset
Dim CRPMatchFiltered As DAO.Recordset
CRP_Match_Master.Fields ("861_Reference")

Set CRPMatch = dbs.OpenRecordset("SELECT * FROM CRP_Match_Master WHERE ((Nullcheck(861_Reference)) = True) ")
Set CRPMatchFiltered = CRPMatch.OpenRecordset

Do While Not CRPMatchFiltered.EOF
CRPMatchFiltered.MoveNext

Loop
CRPMatchFiltered.Close
CRPMatch.Close
Set CRPMatchFiltered = Nothing
Set CRPMatch = Nothing
End Sub

 

[pr2-eugin]

Well I could understand what you are trying to do.. so what you now have is a Form that will be used to see if you have any Null's in the table?? Your code will be as simple as..

Private Sub Form_Open(Cancel As Integer)
    Dim dbs As DAO.Database
    Dim strSQL As String
    
    Set dbs = CurrentDb
    strSQL = "SELECT * FROM CRP_Match_Master WHERE (861_Reference Is Null);"
    Me.RecordSource = strSQL
    [COLOR=Green]'Me.RecordSource tells the form that its Source would be the Query..[/COLOR]
    Set dbs = Nothing
End Sub

If I am wrong please let me know..

 

[Cosmonaut_99]

Hi,

Thanks for that.

I copied your new code into a new module, and tried to run it.
However, I got the error message "The Expression you entered has a function name that Microsoft Access cannot find."

I had read somewhere that VBA can't handle the term Null or Is Null... hence I went down the route of using the function IsNull. Also that VBA can't manage pure Select queries - unless there is an action involved, (e.g. Add / Edit / Delete). This is why I resorted to the recordset.

Thanks
C_99

 

[pr2-eugin]

I copied your new code into a new module, and tried to run it.

Well you have to copy the code into the Form's Module not a common Module.. the Form that you have designed to show all Null data..

I had read somewhere that VBA can't handle the term Null or Is Null... hence I went down the route of using the function IsNull. Also that VBA can't manage pure Select queries - unless there is an action involved, (e.g. Add / Edit / Delete). This is why I resorted to the recordset.

Really? I use Is Null in my Form_Open method as well, I do not get the error..Infact I even have the exact format in my code..