Get the keystrokes sum of 5 columns in a query

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,
LVL 1
anthonytrAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
TotalKS: NZ(Len([reference_1]))+NZ(Len([reference_2]))+NZ(Len([reference_3]))+NZ(Len([reference_4]))+NZ(Len([reference_5]))
0
Rey Obrero (Capricorn1)Commented:
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
anthonytrAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Mike EghtebasDatabase and Application DeveloperCommented:
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
anthonytrAuthor Commented:
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
Mike EghtebasDatabase and Application DeveloperCommented:
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
anthonytrAuthor Commented:
Just looking through the code - it doesn't appear to be adding up the TotalKS field from each record returned.
0
anthonytrAuthor Commented:
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
Mike EghtebasDatabase and Application DeveloperCommented:
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
anthonytrAuthor Commented:
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
Mike EghtebasDatabase and Application DeveloperCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
anthonytrAuthor Commented:
I get a compile error at:

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

Invalid or unqualified reference
0
Mike EghtebasDatabase and Application DeveloperCommented:
Print .Debug "Total: " & i

or

Print .Debug "Total: " & cstr(i)
0
anthonytrAuthor Commented:
I changed it to:

Debug.Print "Total" & i

But now get an error at:

iSum = rs!SumVal

Invalid use of Null
0
Mike EghtebasDatabase and Application DeveloperCommented:
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
anthonytrAuthor Commented:
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
Mike EghtebasDatabase and Application DeveloperCommented:
If you have a solution, please close the question by accepting the answers helped you.

Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.