Help with "Serialize" function in query

[Axel via AccessMonster.com]

Hello,
I am using the Stephen Lebans / Peter Schoeders serialize function as line
counter in a query. Once I apply a criteria in the query, the line counter is
not updated with the actual lines.

What am I doing wrong?

Example:

ID Text GroupID
2 aa 1
3 bb 2
6 aa 3
8 cc 4
12 aa 5

After "aa" criteria in the query I get the following:

ID Text GroupID
2 aa 1
6 aa 3
12 aa 5

and I would like to get

ID Text GroupID
2 aa 1
6 aa 2
12 aa 3

My command is looking like:
GroupID: Serialize("NameOfQuery";"ID";ID)

Any idea?

Thanks
Axel

 

[kc-mass]

Use 2 queries. Query 1 applies the "WHERE" clause to get the data down to
the final collection. Query 2 is based from query 1 and applies the
serialize function

Regards

Kevin

 

[Axel via AccessMonster.com]

Hi Kevin,
I did try this as well and I had the same result. May be I have to review my
serialize function?

Regards
Axel

Use 2 queries. Query 1 applies the "WHERE" clause to get the data down to
the final collection. Query 2 is based from query 1 and applies the
serialize function

Regards

Kevin

Hello,
I am using the Stephen Lebans / Peter Schoeders serialize function as line

[quoted text clipped - 34 lines]

Thanks
Axel

 

[kc-mass]

Why don't you post the actual SQL for the two queries

Hi Kevin,
I did try this as well and I had the same result. May be I have to review
my
serialize function?

Regards
Axel

Use 2 queries. Query 1 applies the "WHERE" clause to get the data down to
the final collection. Query 2 is based from query 1 and applies the
serialize function

Regards

Kevin

Hello,
I am using the Stephen Lebans / Peter Schoeders serialize function as
line

[quoted text clipped - 34 lines]

Thanks
Axel

 

[Axel via AccessMonster.com]

Please find here the SQL of the first query

SELECT DISTINCTROW Prestation.[Nr Prestation], Prestation.Ordonnance,
Prestation.TarifCode, Prestation.Date, Prestation.TarifAmount, Prestation.
Date, Prestation.Nombre, " " AS Brut, [tarifAmount]*[Nombre] AS Net,
Prestation.Heure, Prestation.Executant, Prestation.[Memoire ID]
FROM Prestation;

And here the second:

SELECT Int((Serialize("Prestation Query","Nr Prestation",[Nr Prestation])/10)
+0.9) AS GroupID, [Prestation Query].[Memoire ID], [Prestation Query].[Nr
Prestation], [Prestation Query].Date, [Prestation Query].[Memoire ID],
[Prestation Query].Ordonnance, [Prestation Query].TarifCode, [Prestation
Query].TarifAmount, [Prestation Query].Nombre, [Prestation Query].Heure,
[Prestation Query].Executant, [Prestation Query].Brut, [Prestation Query].Net
FROM [Prestation Query];

The purpose of "Int((Serialize("Prestation Query","Nr Prestation",[Nr
Prestation])/10)+0.9)" is to define groups by 10.

Do you see anything out there what could help me?
Thanks
Axel

Why don't you post the actual SQL for the two queries

Hi Kevin,
I did try this as well and I had the same result. May be I have to review

[quoted text clipped - 18 lines]

 

[kc-mass]

I do not see any "WHERE" clause, the "AA" in your question.

Please find here the SQL of the first query

SELECT DISTINCTROW Prestation.[Nr Prestation], Prestation.Ordonnance,
Prestation.TarifCode, Prestation.Date, Prestation.TarifAmount, Prestation.
Date, Prestation.Nombre, " " AS Brut, [tarifAmount]*[Nombre] AS Net,
Prestation.Heure, Prestation.Executant, Prestation.[Memoire ID]
FROM Prestation;

And here the second:

SELECT Int((Serialize("Prestation Query","Nr Prestation",[Nr
Prestation])/10)
+0.9) AS GroupID, [Prestation Query].[Memoire ID], [Prestation Query].[Nr
Prestation], [Prestation Query].Date, [Prestation Query].[Memoire ID],
[Prestation Query].Ordonnance, [Prestation Query].TarifCode, [Prestation
Query].TarifAmount, [Prestation Query].Nombre, [Prestation Query].Heure,
[Prestation Query].Executant, [Prestation Query].Brut, [Prestation
Query].Net
FROM [Prestation Query];

The purpose of "Int((Serialize("Prestation Query","Nr Prestation",[Nr
Prestation])/10)+0.9)" is to define groups by 10.

Do you see anything out there what could help me?
Thanks
Axel

Why don't you post the actual SQL for the two queries

Hi Kevin,
I did try this as well and I had the same result. May be I have to
review

[quoted text clipped - 18 lines]

Thanks
Axel

 

[Axel via AccessMonster.com]

The query is used in a sub-report opened by a report. I hope this is not too
much confusing and the proper way to do?
But I have the same issue when I add WHERE (((Prestation.[Memoire ID])=
[Reports]![Memoire]![Memoire ID])); to the query 1.

I do not see any "WHERE" clause, the "AA" in your question.

Please find here the SQL of the first query

[quoted text clipped - 31 lines]

 

[Axel via AccessMonster.com]

By double checking the WHERE clause I get know a Run Time error"91", Object
Variable or With block variable not set. By pushing on the debug button it
highlights the "rs.Close" line.(-> marked)

Here the complete code:
Function Serialize(qryname As String, keyname As String, keyvalue) As Long
Dim dbs As Database
Dim rs As Recordset
Set dbs = CurrentDb
On Error GoTo Err_Serialize
Set rs = dbs.OpenRecordset(qryname, dbOpenDynaset, dbReadOnly)
On Error GoTo Err_Serialize
'Find the current record.
Select Case rs.Fields(keyname).Type
' Find using numeric data type key value?
Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, _
DB_DOUBLE, DB_BYTE
rs.FindFirst "[" & keyname & "] = " & keyvalue
' Find using date data type key value?
Case DB_DATE
rs.FindFirst "[" & keyname & "] = #" & keyvalue & "#"
' Find using text data type key value?
Case DB_TEXT
rs.FindFirst "[" & keyname & "] = '" & keyvalue & "'"
Case Else
MsgBox "ERROR: Invalid key field data type!"
End Select
Serialize = Nz(rs.AbsolutePosition, 0) + 1
Err_Serialize:
'Add your own Error handler
-> rs.Close
dbs.Close
Set rs = Nothing
Set dbs = Nothing

End Function

The query is used in a sub-report opened by a report. I hope this is not too
much confusing and the proper way to do?
But I have the same issue when I add WHERE (((Prestation.[Memoire ID])=
[Reports]![Memoire]![Memoire ID])); to the query 1.

I do not see any "WHERE" clause, the "AA" in your question.

[quoted text clipped - 3 lines]