Link to home
Start Free TrialLog in
Avatar of anthonytr
anthonytrFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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,
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

TotalKS: NZ(Len([reference_1]))+NZ(Len([reference_2]))+NZ(Len([reference_3]))+NZ(Len([reference_4]))+NZ(Len([reference_5]))
Avatar of Rey Obrero (Capricorn1)
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]))
Avatar of anthonytr

ASKER

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

Just looking through the code - it doesn't appear to be adding up the TotalKS field from each record returned.
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.
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?
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
ASKER CERTIFIED SOLUTION
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I get a compile error at:

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

Invalid or unqualified reference
Print .Debug "Total: " & i

or

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

Debug.Print "Total" & i

But now get an error at:

iSum = rs!SumVal

Invalid use of Null
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.
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
If you have a solution, please close the question by accepting the answers helped you.

Thanks.