469,097 Members | 1,549 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,097 developers. It's quick & easy.

Problem with record counts and "Invalid Use of Null"

121 100+
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:
Expand|Select|Wrap|Line Numbers
  1. Function Val_Message()
  2.  
  3. Dim Inp_Count As Integer
  4. Dim Val_Count As Integer
  5. Dim ERR_Count As Integer
  6. Dim VAL_Response As Integer
  7.  
  8. DoCmd.OpenQuery "CLR_16001 - Clear Status_Counts Table", acViewNormal, acEdit
  9. DoCmd.OpenQuery "STAT_10001 - Count Imp Records", acViewNormal, acEdit
  10. DoCmd.OpenQuery "STAT_10002 - Count Val Records", acViewNormal, acEdit
  11. DoCmd.OpenQuery "STAT_10003 - Count Err Records", acViewNormal, acEdit
  12.  
  13.  
  14. Inp_Count = DLookup("[RECORD_COUNT]", "[STATUS_COUNTS]", "[COUNT_TYPE]='INPUT'")
  15. Val_Count = DLookup("[RECORD_COUNT]", "[STATUS_COUNTS]", "[COUNT_TYPE]='VALIDATED'")
  16. ERR_Count = DLookup("[RECORD_COUNT]", "[STATUS_COUNTS]", "[COUNT_TYPE]='ERRORS'")
  17.  
  18.     If Inp_Count = "" Then
  19.     Inp_Count = 0
  20.     End If
  21.  
  22.     If Val_Count = "" Then
  23.     Val_Count = 0
  24.     End If
  25.  
  26.     If ERR_Count = "" Then
  27.     ERR_Count = 0
  28.     End If
  29.  
  30. If ERR_Count = 0 Then
  31.         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")
  32.         DoCmd.RunMacro "RUN_Validate_2", , ""
  33.  
  34. Else
  35.     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")
  36.  
  37.     If VAL_Response = vbYes Then
  38.         DoCmd.OpenForm "Review Current Error Records", acNormal, "", "", , acNormal
  39.     Else
  40.         Call ERR_OVERRD_Message
  41.         DoCmd.RunMacro "RUN_Validate_2", , ""
  42.  
  43.     End If
  44. End If
  45. 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:
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO STATUS_COUNTS ( RECORD_COUNT, COUNT_TYPE )
  2. SELECT Count(ETL_TBL.INPT_IND) AS CountOfSTTS_INPT, "INPUT" AS expr1
  3. FROM ETL_TBL
  4. 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.
Apr 19 '07 #1
12 2741
JHNielson
121 100+
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
Apr 19 '07 #2
ADezii
8,800 Expert 8TB
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:
Expand|Select|Wrap|Line Numbers
  1. Function Val_Message()
  2.  
  3. Dim Inp_Count As Integer
  4. Dim Val_Count As Integer
  5. Dim ERR_Count As Integer
  6. Dim VAL_Response As Integer
  7.  
  8. DoCmd.OpenQuery "CLR_16001 - Clear Status_Counts Table", acViewNormal, acEdit
  9. DoCmd.OpenQuery "STAT_10001 - Count Imp Records", acViewNormal, acEdit
  10. DoCmd.OpenQuery "STAT_10002 - Count Val Records", acViewNormal, acEdit
  11. DoCmd.OpenQuery "STAT_10003 - Count Err Records", acViewNormal, acEdit
  12.  
  13.  
  14. Inp_Count = DLookup("[RECORD_COUNT]", "[STATUS_COUNTS]", "[COUNT_TYPE]='INPUT'")
  15. Val_Count = DLookup("[RECORD_COUNT]", "[STATUS_COUNTS]", "[COUNT_TYPE]='VALIDATED'")
  16. ERR_Count = DLookup("[RECORD_COUNT]", "[STATUS_COUNTS]", "[COUNT_TYPE]='ERRORS'")
  17.  
  18.     If Inp_Count = "" Then
  19.     Inp_Count = 0
  20.     End If
  21.  
  22.     If Val_Count = "" Then
  23.     Val_Count = 0
  24.     End If
  25.  
  26.     If ERR_Count = "" Then
  27.     ERR_Count = 0
  28.     End If
  29.  
  30. If ERR_Count = 0 Then
  31.         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")
  32.         DoCmd.RunMacro "RUN_Validate_2", , ""
  33.  
  34. Else
  35.     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")
  36.  
  37.     If VAL_Response = vbYes Then
  38.         DoCmd.OpenForm "Review Current Error Records", acNormal, "", "", , acNormal
  39.     Else
  40.         Call ERR_OVERRD_Message
  41.         DoCmd.RunMacro "RUN_Validate_2", , ""
  42.  
  43.     End If
  44. End If
  45. 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:
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO STATUS_COUNTS ( RECORD_COUNT, COUNT_TYPE )
  2. SELECT Count(ETL_TBL.INPT_IND) AS CountOfSTTS_INPT, "INPUT" AS expr1
  3. FROM ETL_TBL
  4. 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:
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO STATUS_COUNTS ( RECORD_COUNT, Nz(COUNT_TYPE) AS COUNT_TYPE_NEW  )
  2. SELECT Count(ETL_TBL.INPT_IND) AS CountOfSTTS_INPT, "INPUT" AS expr1
  3. FROM ETL_TBL
  4. HAVING (((Count(ETL_TBL.INPT_IND))=Yes));
Apr 19 '07 #3
JHNielson
121 100+
Try this:
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO STATUS_COUNTS ( RECORD_COUNT, Nz(COUNT_TYPE) AS COUNT_TYPE_NEW  )
  2. SELECT Count(ETL_TBL.INPT_IND) AS CountOfSTTS_INPT, "INPUT" AS expr1
  3. FROM ETL_TBL
  4. 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.
Apr 19 '07 #4
MMcCarthy
14,534 Expert Mod 8TB
Try this:
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO STATUS_COUNTS ( RECORD_COUNT, COUNT_TYPE ) 
  2. SELECT nz(Count(ETL_TBL.INPT_IND),0) AS CountOfSTTS_INPT, "INPUT" AS expr1
  3. FROM ETL_TBL
  4. WHERE ETL_TBL.INPT_IND = -1;
  5.  
Apr 19 '07 #5
ADezii
8,800 Expert 8TB
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?
Expand|Select|Wrap|Line Numbers
  1. Nz(COUNT_TYPE) AS COUNT_TYPE_NEW 
Apr 19 '07 #6
JHNielson
121 100+
Did you use this exact syntax?
Expand|Select|Wrap|Line Numbers
  1. Nz(COUNT_TYPE) AS COUNT_TYPE_NEW 

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

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO STATUS_COUNTS ( RECORD_COUNT, Nz(COUNT_TYPE) AS COUNT_TYPE_NEW  )
  2. SELECT Count(ETL_TBL.INPT_IND) AS CountOfSTTS_INPT, "INPUT" AS expr1
  3. FROM ETL_TBL
  4. 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.
Apr 19 '07 #7
JHNielson
121 100+
Would it be easier to use the VB code for Recordcount?
I'm not sure what the easiest way to do this is...

Thanks
Apr 19 '07 #8
MMcCarthy
14,534 Expert Mod 8TB
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?
Apr 19 '07 #9
JHNielson
121 100+
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:

Expand|Select|Wrap|Line Numbers
  1. SELECT ([STAT_01003 - Count Err Records].CountOfERR_DESC)-([STAT_01001 - Count Imp Records].Count1) AS Rec_Count, "Validated" AS Rec_Field
  2. FROM [STAT_01001 - Count Imp Records], [STAT_01003 - Count Err Records];
  3.  
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.

Expand|Select|Wrap|Line Numbers
  1. 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.
Apr 19 '07 #10
JHNielson
121 100+
Can anybody provide some help? I have racked my brain and can't seem to solve tis problem.
Apr 20 '07 #11
MMcCarthy
14,534 Expert Mod 8TB
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.
Apr 20 '07 #12
NeoPa
32,159 Expert Mod 16PB
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.
Apr 25 '07 #13

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

9 posts views Thread by D. Shane Fowlkes | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.