Combine and Count

[vbaInet]

I can't see your image. Just tell me what the error message is. You can upload images on here now because you've passed the 10 posts threshold. Click the "GO ADVANCED" button and you will see MANAGE ATTACHMENTS. Browse, Upload, Close and Reply.

 

[michibahn]

Hi vbaInet, here's the screenshot of the error..

 

[vbaInet]

You are using Expression under Field1. Change Expression to Group By.

 

[michibahn]

You are using Expression under Field1. Change Expression to Group By.

Thanks vbaInet.. after fixing that, here's the new error..

really appreciate your help...

 

[vbaInet]

Did you change anything in the code that you placed in a module?

Do you know what the Immediate Window is?

 

[michibahn]

Did you change anything in the code that you placed in a module?

Do you know what the Immediate Window is?

I didn't changed anything in the module I just copied and pasted it.

unfortunately in don't know what is immediate window. I know it where you can test your code but I totally have no idea on this.

 

[vbaInet]

Go back to the code and put the line highlighted in red between those lines:

    If strOrderBy <> vbNullString Then
        strSql = strSql & " ORDER BY " & strOrderBy
    End If
[COLOR=Red]    Msgbox strSql[/COLOR]
    Set rs = DBEngine(0)(0).OpenRecordset(strSql, dbOpenDynaset)

Run the query and show what the message box displays.

 

[michibahn]

Go back to the code and put the line highlighted in red between those lines:

    If strOrderBy <> vbNullString Then
        strSql = strSql & " ORDER BY " & strOrderBy
    End If
[COLOR=Red]    Msgbox strSql[/COLOR]
    Set rs = DBEngine(0)(0).OpenRecordset(strSql, dbOpenDynaset)

Run the query and show what the message box displays.

here's what it shows.. blank OK pop out window alternating with the previous error that I have.

 

[vbaInet]

Hmmm... I think it's about time I see a copy of your database

 

[michibahn]

Hmmm... I think it's about time I see a copy of your database

I was hoping you will say that here it is.. I'm working on the origTable and qryTotal to get my required output.

really appreciate all your help...

 

[vbaInet]

My mistake:

Expr1: ConcatRelated("FIELD2","origTABLE","[DATE] = [COLOR=Red]#[/COLOR]" & [DATE] & "[COLOR=Red]#[/COLOR] AND [FIELD1] = '" & [FIELD1] & "'")

 

[michibahn]

My mistake:

Expr1: ConcatRelated("FIELD2","origTABLE","[DATE] = [COLOR=Red]#[/COLOR]" & [DATE] & "[COLOR=Red]#[/COLOR] AND [FIELD1] = '" & [FIELD1] & "'")

wow it works!!.. however, the output seems to be incorrect. the output doubles in lines 2 and 3?

also, since I called the origTable in this new query, what is the role of the qryTotal wherein I need the summarized count as per my required output.

DATE | FIELD1 | FIELD2 | COUNT | Expr1
9/16/2011 10:17:27 AM | M114-106-172 | 1362 | 1 | 1362
9/16/2011 10:17:27 AM | M216-185090-144 | 20 | 1 | 20, 21
9/16/2011 10:17:27 AM | M216-185090-144 | 21 | 1 | 20, 21
9/16/2011 10:17:27 AM | TP259-147095-136 | 63 | 1 | 63
9/16/2011 10:24:48 AM | M216-185090-144 | 9 | 1 | 9

thanks for your help

 

[vbaInet]

You should not include Field2 in your query.

 

[michibahn]

You should not include Field2 in your query.

ok fixed that

one last thing.. how can I now show the COUNTS? in this case line 2 should have 2 COUNTS instead of 1..

 

[vbaInet]

You've done it the wrong way round. It should be as attached.

 

[michibahn]

You've done it the wrong way round. It should be as attached.

oh man!!! I can't thank you enough for your time and effort.. all is working as required!

can't believe you hang on this long to help me...

thanks a lot!!!

 

[vbaInet]

You're welcome

 

[michibahn]

You're welcome

Hi vbaInet, its me again... I'am being hit by the 255 character limitation bug in the ConcatRelated module. I know there is a workaround using a UNION query.. do you have any idea how to apply this in my requirement?

TIA!

 

[vbaInet]

Try putting the code in the Control Source of a textbox instead of inside the query.

 

[michibahn]

Try putting the code in the Control Source of a textbox instead of inside the query.

sorry.. can you point me where to set this up? thanks!