Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Get the keystrokes sum of 5 columns in a query

Posted on 2014-11-12
17
Medium Priority
?
169 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
No other job is as rewarding and demanding as building an iPhone app is. It is not really in the hands of the developer for the success of an iPhone app. Many factors operate jointly for every iOS application's success in the market.
Six Sigma Control Plans
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

571 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