Solved

Get the keystrokes sum of 5 columns in a query

Posted on 2014-11-12
17
157 Views
Last Modified: 2014-11-12
Hi,

I created a simple query to test things out, but keep getting stuck.

I have the following in a Query:

TotalKS: Len([reference_1])+Len([reference_2])+Len([reference_3])+Len([reference_4])+Len([reference_5])

If all the fields are used (have text in them) it will return the correct keystroke count, however, if one of the fields is not used it will return nothing.

How do I deal with blank fields and is it possible to accomplish the same things but without having to create a query with the extra TotalKS column in it?  Could this be done all in VBA with an SQL Query?

Thanks,
0
Comment
Question by:anthonytr
  • 8
  • 8
17 Comments
 
LVL 33

Expert Comment

by:Mike Eghtebas
Comment Utility
TotalKS: NZ(Len([reference_1]))+NZ(Len([reference_2]))+NZ(Len([reference_3]))+NZ(Len([reference_4]))+NZ(Len([reference_5]))
0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
use the nz() function


TotalKS: Len(Nz([reference_1],0))+Len(Nz([reference_2],0))+Len(Nz([reference_3],0))+Len(Nz([reference_4],0))+Len(Nz([reference_5,0]))

or

TotalKS: Len(Nz([reference_1]))+Len(Nz([reference_2]))+Len(Nz([reference_3]))+Len(Nz([reference_4]))+Len(Nz([reference_5]))
0
 

Author Comment

by:anthonytr
Comment Utility
That's great - thanks.

How would I achieve the following:

The Table is designed like this:

RefID
reference_1
reference_2
reference_3
reference_4
reference_5
KSTotal

In VBA code I need to run a query which returns records with a specific RefID and then add up the total Keystrokes form the KSTotal field.
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
Comment Utility
Change Table1 below to whatever your table name is.
Dim rs as DAO.Recordset

Set rs=CurrentDB.OpenRecordset("Select RefID, NZ(Len([reference_1]))+NZ(Len([reference_2]))+NZ(Len([reference_3]))+NZ(Len([reference_4]))+NZ(Len([reference_5])) As TotalKS From Table1")
If rs.Recordcount>0 then
rs.MoveFirst
Do Until rs.EOF
   Print.Debug rs!RefID ", " & rs!TotalKS 
rs.MoveNExt
Loop
Else
  MsgBox "Table has no records"
End if

rs.close

Open in new window


BTW, the second question should be posted as a new question because, you already have an answer to your original question.

Mike
0
 

Author Comment

by:anthonytr
Comment Utility
eghtenbas:

I'll look at this straight away, just one quick question.  How is the RefID being set so that I can pass this into the code?

This code will be invoked from a screen where I will pass in the RefID to filter/query on.

thanks
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
Comment Utility
Make a select query of your table and post its SQL here. I want to see what fields you have in your table. I hope it has a field called RefID, if so, then use:

Dim rs as DAO.Recordset

Set rs=CurrentDB.OpenRecordset("Select RefID, NZ(Len([reference_1]))+NZ(Len([reference_2]))+NZ(Len([reference_3]))+NZ(Len([reference_4]))+NZ(Len([reference_5])) As TotalKS From Table1 Where RefID=" & MyRefID)
If rs.Recordcount>0 then
rs.MoveFirst
Do Until rs.EOF
   Print.Debug rs!RefID ", " & rs!TotalKS 
rs.MoveNExt
Loop
Else
  MsgBox "Table has no records"
End if

rs.close

Open in new window

0
 

Author Comment

by:anthonytr
Comment Utility
Just looking through the code - it doesn't appear to be adding up the TotalKS field from each record returned.
0
 

Author Comment

by:anthonytr
Comment Utility
Here is the SQL query:

SELECT tbl_reference_data.id, tbl_reference_data.batch_reference, tbl_reference_data.reference_1, tbl_reference_data.reference_2, tbl_reference_data.reference_3, tbl_reference_data.reference_4, tbl_reference_data.reference_5, Nz(Len([reference_1]))+Nz(Len([reference_2]))+Nz(Len([reference_3]))+Nz(Len([reference_4]))+Nz(Len([reference_5])) AS TotalKS
FROM tbl_reference_data;

Open in new window


tbl_reference_data.batch_reference is RefID (i was just making it easier to type for the question.
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 33

Expert Comment

by:Mike Eghtebas
Comment Utility
Try with NZ() in new position:

Dim rs as DAO.Recordset

Set rs=CurrentDB.OpenRecordset("Select batch_reference, Len(nz([reference_1]))+Len(nz([reference_2]))+Len(nz([reference_3]))+Len(nz([reference_4]))+Len(nz([reference_5])) As TotalKS From tbl_reference_data Where batch_reference=" & MyRefID)
If rs.Recordcount>0 then
rs.MoveFirst
Do Until rs.EOF
   Print.Debug rs!batch_reference", " & rs!TotalKS 
rs.MoveNExt
Loop
Else
  MsgBox "Table has no records"
End if

rs.close

Open in new window


Please let me know the failure was because of the position of NZ() or because you were using RefID instead of batch_reference?
0
 

Author Comment

by:anthonytr
Comment Utility
Sorry, I think I'm confusing things.  The Keystrokes from each cell is being added up correctly.  There is no problem with this. :)

A RefID can have an unlimited number of records associated with it.  So, for example, RefID = 1A could have 3 records:
Record 1 TotalKS = 30
Record 2 TotalKS = 10
Record 3 TotalKS = 20

I need to now add these 3 records TotalKS fields together and return the answer: 60
0
 
LVL 33

Accepted Solution

by:
Mike Eghtebas earned 500 total points
Comment Utility
1. Make a button cmdFindSum on your form
Private Sub cmdFindSum_Click()   
       Dim i as integer
       strRefVal = "1A"
       i = fnFindSum()
       Print.Debug "Totla: " & cstr(i)
End Sub

Open in new window


2. Make a query called qryFilterSum with SQL like:
Select batch_reference, Len(nz([reference_1])) +Len(nz([reference_2])) +Len(nz([reference_3])) +Len(nz([reference_4])) +Len(nz([reference_5])) As TotalKS From tbl_reference_data Where batch_reference='" & fnMyRefID() & "'"

3. In a module, add the following functions:
Public strRefVal As String
Function fnMyRefID() As String
     fnMyRefID = strRefVal 
End Function
'---------
Function fnFindSum() as String
Dim rs as DAO.Recordset
Dim iSum as integer

Set rs=CurrentDB.OpenRecordset("Select Sum(TotalKS) As SumVal From qryFilterSum")
If rs.Recordcount>0 then
   iSum = rs!SumVal  
Else
  MsgBox "Table has no records"
End if

rs.close
fnFindSum =iSum
End Function

Open in new window

0
 

Author Comment

by:anthonytr
Comment Utility
I get a compile error at:

Print .Debug; "Total: " & CStr(i)

Invalid or unqualified reference
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
Comment Utility
Print .Debug "Total: " & i

or

Print .Debug "Total: " & cstr(i)
0
 

Author Comment

by:anthonytr
Comment Utility
I changed it to:

Debug.Print "Total" & i

But now get an error at:

iSum = rs!SumVal

Invalid use of Null
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
Comment Utility
First, let see if qryFilterSum is done right. To test this, run

Private Sub cmdFindSum_Click()   
      ' Dim i as integer
       strRefVal = "1A"
      ' i = fnFindSum()
       'Print.Debug "Totla: " & cstr(i)
      DoCmd.OpenQuery "qryFilterSum "
End Sub

Open in new window


This should show a result like:
Record 1 TotalKS = 30
Record 2 TotalKS = 10
Record 3 TotalKS = 20

Put break at line 3 and play detective. You better to take the ownership of your code and make it work. Find out the typos etc. What I have is just air code.
0
 

Author Comment

by:anthonytr
Comment Utility
I changed the SQL Query to:

SELECT tbl_reference_data.batch_reference, Len(Nz([reference_1]))+Len(Nz([reference_2]))+Len(Nz([reference_3]))+Len(Nz([reference_4]))+Len(Nz([reference_5])) AS TotalKS
FROM tbl_reference_data
WHERE (((tbl_reference_data.batch_reference)=fnMyRefID()));

Open in new window


Which has fixed things.

I also added:

me.keystrokes = i

Thanks for all your help!

A
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
Comment Utility
If you have a solution, please close the question by accepting the answers helped you.

Thanks.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This is an explanation of a simple data model to help parse a JSON feed
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

728 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

9 Experts available now in Live!

Get 1:1 Help Now