Problem with record counts and "Invalid Use of Null"

[JHNielson]

I have a simple question, but I can't seem to find the answer.


I have this code to count the number of records that pass through a set of processes:

It counts how many records go through INPUT, VALIDATED, ERRORS

This is the code to get the numbers:

Function Val_Message()
 
Dim Inp_Count As Integer
Dim Val_Count As Integer
Dim ERR_Count As Integer
Dim VAL_Response As Integer
 
DoCmd.OpenQuery "CLR_16001 - Clear Status_Counts Table", acViewNormal, acEdit
DoCmd.OpenQuery "STAT_10001 - Count Imp Records", acViewNormal, acEdit
DoCmd.OpenQuery "STAT_10002 - Count Val Records", acViewNormal, acEdit
DoCmd.OpenQuery "STAT_10003 - Count Err Records", acViewNormal, acEdit
 
 
Inp_Count = DLookup("[RECORD_COUNT]", "[STATUS_COUNTS]", "[COUNT_TYPE]='INPUT'")
Val_Count = DLookup("[RECORD_COUNT]", "[STATUS_COUNTS]", "[COUNT_TYPE]='VALIDATED'")
ERR_Count = DLookup("[RECORD_COUNT]", "[STATUS_COUNTS]", "[COUNT_TYPE]='ERRORS'")
 
    If Inp_Count = "" Then
    Inp_Count = 0
    End If
 
    If Val_Count = "" Then
    Val_Count = 0
    End If
 
    If ERR_Count = "" Then
    ERR_Count = 0
    End If
 
If ERR_Count = 0 Then
        VAL_Response = MsgBox("All Records Passed Validation." & Chr(10) & Inp_Count & " Records were Loaded" & Chr(10) & Val_Count & " Records Passed Validation" & Chr(10) & "The records are now ready to be processed", vbOKOnly + vbApplicationModal + vbInformation, "Validation Complete")
        DoCmd.RunMacro "RUN_Validate_2", , ""
 
Else
    VAL_Response = MsgBox("All Records Have Been VALD_CT." & Chr(10) & Inp_Count & " Records were Loaded" & Chr(10) & Val_Count & " Records Passed Validation" & Chr(10) & ERR_Count & " Records Failed Validation" & Chr(10) & "Would you like to complete review the error records before proceeding?", vbYesNo + vbApplicationModal + vbQuestion, "Validation Complete")
 
    If VAL_Response = vbYes Then
        DoCmd.OpenForm "Review Current Error Records", acNormal, "", "", , acNormal
    Else
        Call ERR_OVERRD_Message
        DoCmd.RunMacro "RUN_Validate_2", , ""
 
    End If
End If
End Function

As you can see, I call on three queries to get those numbers. The queries each do a count of the number of records, and adds that and a description (i.e. VALIDATED, INPUT, etc.) to a Table (STATUS_COUNTS). The Dlookups you see in the code.

The queries look like this:

INSERT INTO STATUS_COUNTS ( RECORD_COUNT, COUNT_TYPE )
SELECT Count(ETL_TBL.INPT_IND) AS CountOfSTTS_INPT, "INPUT" AS expr1
FROM ETL_TBL
HAVING (((Count(ETL_TBL.INPT_IND))=Yes));

What happens is that if no records are found in any of these processes, a record doesn't get written with [COUNT_TYPE], and so I get an "Invalid use of Null" error.

Thanks for any help I can get.

[JHNielson]

One other thing, one idea I had to address this is change the queries for Append Queries to Update queries, and then simply update the fields in the table by Matching the descriptions, - that way there would already be a record with "Validated" and a value of 0 in the table, unless it got updated. But I can't seem to get an update query to count based on criteria.

Thanks again

[ADezii]

I have a simple question, but I can't seem to find the answer.


I have this code to count the number of records that pass through a set of processes:

It counts how many records go through INPUT, VALIDATED, ERRORS

This is the code to get the numbers:

Function Val_Message()
 
Dim Inp_Count As Integer
Dim Val_Count As Integer
Dim ERR_Count As Integer
Dim VAL_Response As Integer
 
DoCmd.OpenQuery "CLR_16001 - Clear Status_Counts Table", acViewNormal, acEdit
DoCmd.OpenQuery "STAT_10001 - Count Imp Records", acViewNormal, acEdit
DoCmd.OpenQuery "STAT_10002 - Count Val Records", acViewNormal, acEdit
DoCmd.OpenQuery "STAT_10003 - Count Err Records", acViewNormal, acEdit
 
 
Inp_Count = DLookup("[RECORD_COUNT]", "[STATUS_COUNTS]", "[COUNT_TYPE]='INPUT'")
Val_Count = DLookup("[RECORD_COUNT]", "[STATUS_COUNTS]", "[COUNT_TYPE]='VALIDATED'")
ERR_Count = DLookup("[RECORD_COUNT]", "[STATUS_COUNTS]", "[COUNT_TYPE]='ERRORS'")
 
    If Inp_Count = "" Then
    Inp_Count = 0
    End If
 
    If Val_Count = "" Then
    Val_Count = 0
    End If
 
    If ERR_Count = "" Then
    ERR_Count = 0
    End If
 
If ERR_Count = 0 Then
        VAL_Response = MsgBox("All Records Passed Validation." & Chr(10) & Inp_Count & " Records were Loaded" & Chr(10) & Val_Count & " Records Passed Validation" & Chr(10) & "The records are now ready to be processed", vbOKOnly + vbApplicationModal + vbInformation, "Validation Complete")
        DoCmd.RunMacro "RUN_Validate_2", , ""
 
Else
    VAL_Response = MsgBox("All Records Have Been VALD_CT." & Chr(10) & Inp_Count & " Records were Loaded" & Chr(10) & Val_Count & " Records Passed Validation" & Chr(10) & ERR_Count & " Records Failed Validation" & Chr(10) & "Would you like to complete review the error records before proceeding?", vbYesNo + vbApplicationModal + vbQuestion, "Validation Complete")
 
    If VAL_Response = vbYes Then
        DoCmd.OpenForm "Review Current Error Records", acNormal, "", "", , acNormal
    Else
        Call ERR_OVERRD_Message
        DoCmd.RunMacro "RUN_Validate_2", , ""
 
    End If
End If
End Function

As you can see, I call on three queries to get those numbers. The queries each do a count of the number of records, and adds that and a description (i.e. VALIDATED, INPUT, etc.) to a Table (STATUS_COUNTS). The Dlookups you see in the code.

The queries look like this:

INSERT INTO STATUS_COUNTS ( RECORD_COUNT, COUNT_TYPE )
SELECT Count(ETL_TBL.INPT_IND) AS CountOfSTTS_INPT, "INPUT" AS expr1
FROM ETL_TBL
HAVING (((Count(ETL_TBL.INPT_IND))=Yes));

What happens is that if no records are found in any of these processes, a record doesn't get written with [COUNT_TYPE], and so I get an "Invalid use of Null" error.

Thanks for any help I can get.

Try this:

INSERT INTO STATUS_COUNTS ( RECORD_COUNT, Nz(COUNT_TYPE) AS COUNT_TYPE_NEW  )
SELECT Count(ETL_TBL.INPT_IND) AS CountOfSTTS_INPT, "INPUT" AS expr1
FROM ETL_TBL
HAVING (((Count(ETL_TBL.INPT_IND))=Yes));

[JHNielson]

Try this:

INSERT INTO STATUS_COUNTS ( RECORD_COUNT, Nz(COUNT_TYPE) AS COUNT_TYPE_NEW  )
SELECT Count(ETL_TBL.INPT_IND) AS CountOfSTTS_INPT, "INPUT" AS expr1
FROM ETL_TBL
HAVING (((Count(ETL_TBL.INPT_IND))=Yes));

thank for the help. Sorry, but I get a:

Syntax Error in INSERT INTO Statement

I tried to trouble shoot it, but it seems to have a prblem wiht the ...as COUNT TYPRE.... part or the ....Nz... part.

[MMcCarthy]

Try this:

INSERT INTO STATUS_COUNTS ( RECORD_COUNT, COUNT_TYPE ) 
SELECT nz(Count(ETL_TBL.INPT_IND),0) AS CountOfSTTS_INPT, "INPUT" AS expr1
FROM ETL_TBL
WHERE ETL_TBL.INPT_IND = -1;
 

[ADezii]

thank for the help. Sorry, but I get a:

Syntax Error in INSERT INTO Statement

I tried to trouble shoot it, but it seems to have a prblem wiht the ...as COUNT TYPRE.... part or the ....Nz... part.

Did you use this exact syntax?

Nz(COUNT_TYPE) AS COUNT_TYPE_NEW 

[JHNielson]

Did you use this exact syntax?

Nz(COUNT_TYPE) AS COUNT_TYPE_NEW 

Yes I copied the SQL in directly. Here it is:

INSERT INTO STATUS_COUNTS ( RECORD_COUNT, Nz(COUNT_TYPE) AS COUNT_TYPE_NEW  )
SELECT Count(ETL_TBL.INPT_IND) AS CountOfSTTS_INPT, "INPUT" AS expr1
FROM ETL_TBL
HAVING (((Count(ETL_TBL.INPT_IND))=Yes));

I;m not positive what is causing the problem, it's just that when I get the error and click ok - it sticks the cursor right between the Nz and (. SO I assume that is the source of the problem.


Thanks for all your help.

[JHNielson]

Would it be easier to use the VB code for Recordcount?
I'm not sure what the easiest way to do this is...

Thanks

[MMcCarthy]

Would it be easier to use the VB code for Recordcount?
I'm not sure what the easiest way to do this is...

Thanks

What happened when you tried my suggestion?

[JHNielson]

I hate to be a pest but I have been back and forth trying to solve this.

I got this query to find the number of records:

SELECT ([STAT_01003 - Count Err Records].CountOfERR_DESC)-([STAT_01001 - Count Imp Records].Count1) AS Rec_Count, "Validated" AS Rec_Field
FROM [STAT_01001 - Count Imp Records], [STAT_01003 - Count Err Records];
 

And then I use this query to update the table [STATUS_COUNTS] with that number/ I got it so that it returns a 0 using the NZ method mentioned earlier. But, I still get a prompt for a value for [STG_IMPT] if it is null.

UPDATE STATUS_COUNTS SET STATUS_COUNTS.VAL_COUNT = Format(nz([STAT_02002 - Count Numberof Validated].[Rec_Count]),0);

Thanksfor any help anyone can provide with either fixing this process or using the VB Record count method. I'm open to any suggestions. Thanks.

[JHNielson]

Can anybody provide some help? I have racked my brain and can't seem to solve tis problem.

[MMcCarthy]

Can anybody provide some help? I have racked my brain and can't seem to solve tis problem.

As you haven't answered any of my posts I don't know what happened when you tried the code I provided.

[NeoPa]

JHNielson,
Please respond to all attempts to help you in your thread. To fail to respond is actually an exhibition of very poor manners and our members are likely to treat your requests with disdain unless you treat them with at least a basic level of respect.

There is an answer to this but I doubt you'll see it if you fail to communicate in an acceptable way.

MODERATOR.

As you haven't answered any of my posts I don't know what happened when you tried the code I provided.

Mary,
What would have happened is that no record was produced for that WHERE clause so no record would be added. Essentially an empty recordset. But don't worry - I get the point you were making.