Solved

Get the keystrokes sum of 5 columns in a query

Posted on 2014-11-12
17
163 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 34

Expert Comment

by:Mike Eghtebas
ID: 40437982
TotalKS: NZ(Len([reference_1]))+NZ(Len([reference_2]))+NZ(Len([reference_3]))+NZ(Len([reference_4]))+NZ(Len([reference_5]))
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40437987
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
ID: 40438007
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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40438039
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
ID: 40438055
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 34

Expert Comment

by:Mike Eghtebas
ID: 40438068
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
ID: 40438071
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
ID: 40438079
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
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40438082
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
ID: 40438102
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 34

Accepted Solution

by:
Mike Eghtebas earned 500 total points
ID: 40438171
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
ID: 40438246
I get a compile error at:

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

Invalid or unqualified reference
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40438250
Print .Debug "Total: " & i

or

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

Author Comment

by:anthonytr
ID: 40438276
I changed it to:

Debug.Print "Total" & i

But now get an error at:

iSum = rs!SumVal

Invalid use of Null
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 40438306
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
ID: 40438319
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 34

Expert Comment

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

Thanks.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

821 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