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.
12  3061 
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 8,834
Recognized Expert Expert
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));
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 14,534
Recognized Expert Moderator MVP
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 8,834
Recognized Expert Expert
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
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.
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 14,534
Recognized Expert Moderator MVP
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?
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.
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: TS |
last post by:
Hi, i have a problem validating xml against schema. I used
http://apps.gotdotnet.com/xmltools/xsdvalidator/Default.aspx validator and
it says it is fine. Can you tell me why this doesn't work?
Thanks!
Schema:
<?xml version="1.0"?>
<xs:schema id="ReportInfo" targetNamespace="http://tempuri.org/Reports.xsd"
|
by: Andrew McNab |
last post by:
Hi folks,
I have a problem with an MS Access SQL query which is being used in an
Access Report, and am wondering if anyone can help.
Basically, my query (shown below) gets some records from a couple of
tables in my database using INNER JOINS and the WHERE clause to
specify the required constraints. However, I also want to read two
fields from a *single* record from a table called 'Locations' and then
apply one of these field's values...
|
by: Dica |
last post by:
i've used the sample code from msdn to create an encyption/decryption
assembly as found here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/SecNetHT10.asp
i'm able to encrypt and then decrypt data okay as in the following code:
// encrypt the data //
Encryptor enc = new Encryptor(EncryptionAlgorithm.TripleDes);
byte key = Encoding.ASCII.GetBytes("0123456789012345");
|
by: D. Shane Fowlkes |
last post by:
I'm using SQL Server 2000 and on my page, I'm simply creating a
SQLDataReader and filling in Labels with the retrieved (single) record.
However, how can I prevent from getting errors when a field is null?
I've tried something like this (experimenting with IsDBNull):
=========================================================
If dtrData.IsDBNull("Address2") = True Then
lblAddress2.Text = ""
Else
|
by: Dabbler |
last post by:
In my Registrant FormView I have a DropDownList which loads data from a
secondary SqlDataSource "sdsOfficeParks". I need the user to select an office
park but save the selected value in the FormView's ObjectDataSource
"odsRegistrant" .
The following solution can't work because I need the ddl selected value
bound to my odsRegistrant but both tables have the same column name for
primary key.
<asp:DropDownList ID="ddlOfficePark"...
| | |
by: Stewart |
last post by:
Hi there,
I would like to calculate a person's age in years (between 2 dates). I
am using the following function for this calculation (source:
http://www.mvps.org/access/datetime/date0001.htm)
_________________________________________
Function Age(DateOfBirth, DateToday) As Integer
' Returns the Age in years between 2 dates
|
by: MarkusR |
last post by:
If I run the stored proc in the Query Analyzer this works and I get the
expected result set back. However when I run it in my application I get
a results set of one row but when I try to access the values I get
"Invalid attempt to read when no data is present".
private void GetLotIDPriorityFromLot(string aLotDesc, out int aLotID,
out DateTime aPriorityDate)
{
SqlConnection Conn;
SqlDataReader reader = null;
|
by: Bill_DBA |
last post by:
I have the following stored procedure that is called from the source of
a transformation in a DTS package. The first parameter turns on PRINT
debug messages. The second, when equals 1, turns on the openning,
fetching, and closing of a cursor. Things are fine if only one of the
two parameters was set to 1.
When run with both parameters turned on, "dba_test_dts 1, 1", DTS
source (tab) preview fails because it thinks no row was returned....
|
by: 200dogz |
last post by:
Hi guys,
I want to have a button which opens up a new window when pressed.
<asp:Button ID="Button1" runat="server" Text="Open new window" />
...
Button1.Attributes.Add("OnClick",
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
