Solved Dynamic Report problem (over 10 years) (1 Viewer)

Manos39

Registered User.
Local time
Today, 16:01
Joined
Feb 14, 2011
Messages
248
Hello
Having this problem over 10 years i need to ask again having simplyfied my database to a small sample
if you would be kind to assist me on this:

in a crosstab report called "RptsavvataepilogicrossA4"
The report uses vba code to produse labels for dates (Saturdays) employees
who have worked in a month. The source is a crosstab query "Qrsavvataepilogicross", and my criteria is put in a report "Qrsavvataepilogi"

Query working fine, if criteria is put in 1st field of it (for year) and the 2nd (for month), maually. All Suturdays worked by employees are shown by "1" in the query and also my report works well with the VBA Code on open (to have labels captioned).

Sample db is having data in years 2022 and month 1, (January), for testing.

Problem is that when criteria is put in a form, Access does not populate the report, although I have also declaired query parameters.
When put manually, then report "RptsavvataepilogicrossA4" produces desired data.

Would you test my sample?
 

Attachments

  • SERVICE.mdb
    1 MB · Views: 189

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:01
Joined
May 7, 2009
Messages
19,169
i tested but unable to open the report, maybe because of non-english alphabet.
 

Manos39

Registered User.
Local time
Today, 16:01
Joined
Feb 14, 2011
Messages
248
i tested but unable to open the report, maybe because of non-english alphabet.
Maybe. but what might be the case here
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 19:01
Joined
May 21, 2018
Messages
8,463
I cannot really test it, there are so many issues with the language conversion that does not convert into english well for VBA and SQL. But looking at this code I very much doubt it would work with the parameterized query. And you already said it does work manually. So get rid of the parameterized query and dynamically build the query string

Code:
Set db = CurrentDb
Set Qrydef = db.QueryDefs("Qrsavvataepilogicross")
fldcount = Qrydef.Fields.Count - 1

If fldcount > 6 Then
   MsgBox "Maximoum date fields exceeds number of (5) fields and first five fields are shown"
   fldcount = 6
End If

Set ctrl = Me.Controls("date1")
Set ctrl2 = Me.Controls("total1")
If fldcount >= 2 Then
    ctrl.ControlSource = Qrydef.Fields(2).Name
    Me("date1_Label").Caption = Qrydef.Fields(2).Name
    ctrl2.ControlSource = "=SUM([" & Qrydef.Fields(2).Name & "])"
End If
....


Set ctrl = Me.Controls("date5")
Set ctrl2 = Me.Controls("total5")
If fldcount = 6 Then
    ctrl.ControlSource = Qrydef.Fields(6).Name
    Me("date5_Label").Caption = Qrydef.Fields(6).Name
    ctrl2.ControlSource = "=SUM([" & Qrydef.Fields(6).Name & "])"
End If

I do not think those field names can be pulled from the query definition.
Instead of having the query reference the form fields, I would dynamically build the qsf string. When you click the button.

Something like
Code:
[code]

dim strSql as string

strSql = "SELECT Year([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ]) AS [ΑΝΑ ΕΤΟΣ3], Month([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ]) AS [ΑΝΑ ΜΗΝΑ3], [ΕΠΩΝΥΜΟ] & " " & [ΟΝΟΜΑ] AS
ΟΝΟΜΑΤΕΠΩΝΥΜΟ, ypiresiestbl.ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ AS ΗΜΕΡΟΜΗΝΙΑ, IIf(Weekday([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ])=7 And Nz([ΩΡΑΡΙΟID])<>5 And Nz([ΩΡΑΡΙΟID])<>0
And [ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ]<>Nz([ΑΡΓΙΑ]) And [ΧΡΕΩΣΗID]=4,1) AS [ΧΡ ΣΑΒΒΑΤΟ], [ΧΡ ΣΑΒΒΑΤΟ]*8 AS [ΧΡ ΣΑΒΒΑΤΟΥ]
FROM ypaliloitbl INNER JOIN (eidiyphresiontbl INNER JOIN (eidikeshreoseistbl INNER JOIN (ypiresiestbl
LEFT JOIN argiestbl ON ypiresiestbl.ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ = argiestbl.ΑΡΓΙΑ) ON eidikeshreoseistbl.ΕΙΔΙΚΗΧΡΕΩΣΗID = ypiresiestbl.ΧΡΕΩΣΗID)
ON eidiyphresiontbl.ΕΙΔΟΣΥΠΗΡΕΣΙΑΣID = ypiresiestbl.ΕΙΔΟΣΥΠΗΡΕΣΙAΣID) ON ypaliloitbl.ΥΠΑΛΛΗΛΟΣID = ypiresiestbl.ΕΠΩΝΥΜΟID
GROUP BY Year([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ]), Month([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ]), [ΕΠΩΝΥΜΟ] & " " & [ΟΝΟΜΑ], ypiresiestbl.ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ,
ypaliloitbl.ΕΠΩΝΥΜΟ, ypaliloitbl.ΟΝΟΜΑ, ypiresiestbl.ΧΡΕΩΣΗID, ypiresiestbl.ΩΡΑΡΙΟID, ypaliloitbl.ΥΠΑΛΛΗΛΟΣID, argiestbl.ΑΡΓΙΑ "

'Set your parameters
strSql = strSql & "HAVING (((Year([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ]))= "& me.FrmSelect.YEAR & " ) And ((Month([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ]))= " & me.FrmSelect.MONTH "
strSql = strSql & ") And ((ypaliloitbl.ΥΠΑΛΛΗΛΟΣID)<>58 And (ypaliloitbl.ΥΠΑΛΛΗΛΟΣID)<>60) And ((Weekday([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ]))=7))
ORDER BY Year([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ]), Month([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ]), [ΕΠΩΝΥΜΟ] & " " & [ΟΝΟΜΑ], ypiresiestbl.ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ;"

currentdb.querydefs("Qrsavvataepilogi").sql = strSql
[/CODE]
 

Manos39

Registered User.
Local time
Today, 16:01
Joined
Feb 14, 2011
Messages
248
I cannot really test it, there are so many issues with the language conversion that does not convert into english well for VBA and SQL. But looking at this code I very much doubt it would work with the parameterized query. And you already said it does work manually. So get rid of the parameterized query and dynamically build the query string

Code:
Set db = CurrentDb
Set Qrydef = db.QueryDefs("Qrsavvataepilogicross")
fldcount = Qrydef.Fields.Count - 1

If fldcount > 6 Then
   MsgBox "Maximoum date fields exceeds number of (5) fields and first five fields are shown"
   fldcount = 6
End If

Set ctrl = Me.Controls("date1")
Set ctrl2 = Me.Controls("total1")
If fldcount >= 2 Then
    ctrl.ControlSource = Qrydef.Fields(2).Name
    Me("date1_Label").Caption = Qrydef.Fields(2).Name
    ctrl2.ControlSource = "=SUM([" & Qrydef.Fields(2).Name & "])"
End If
....


Set ctrl = Me.Controls("date5")
Set ctrl2 = Me.Controls("total5")
If fldcount = 6 Then
    ctrl.ControlSource = Qrydef.Fields(6).Name
    Me("date5_Label").Caption = Qrydef.Fields(6).Name
    ctrl2.ControlSource = "=SUM([" & Qrydef.Fields(6).Name & "])"
End If

I do not think those field names can be pulled from the query definition.
Instead of having the query reference the form fields, I would dynamically build the qsf string. When you click the button.

Something like
Code:
[code]

dim strSql as string

strSql = "SELECT Year([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ]) AS [ΑΝΑ ΕΤΟΣ3], Month([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ]) AS [ΑΝΑ ΜΗΝΑ3], [ΕΠΩΝΥΜΟ] & " " & [ΟΝΟΜΑ] AS
ΟΝΟΜΑΤΕΠΩΝΥΜΟ, ypiresiestbl.ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ AS ΗΜΕΡΟΜΗΝΙΑ, IIf(Weekday([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ])=7 And Nz([ΩΡΑΡΙΟID])<>5 And Nz([ΩΡΑΡΙΟID])<>0
And [ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ]<>Nz([ΑΡΓΙΑ]) And [ΧΡΕΩΣΗID]=4,1) AS [ΧΡ ΣΑΒΒΑΤΟ], [ΧΡ ΣΑΒΒΑΤΟ]*8 AS [ΧΡ ΣΑΒΒΑΤΟΥ]
FROM ypaliloitbl INNER JOIN (eidiyphresiontbl INNER JOIN (eidikeshreoseistbl INNER JOIN (ypiresiestbl
LEFT JOIN argiestbl ON ypiresiestbl.ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ = argiestbl.ΑΡΓΙΑ) ON eidikeshreoseistbl.ΕΙΔΙΚΗΧΡΕΩΣΗID = ypiresiestbl.ΧΡΕΩΣΗID)
ON eidiyphresiontbl.ΕΙΔΟΣΥΠΗΡΕΣΙΑΣID = ypiresiestbl.ΕΙΔΟΣΥΠΗΡΕΣΙAΣID) ON ypaliloitbl.ΥΠΑΛΛΗΛΟΣID = ypiresiestbl.ΕΠΩΝΥΜΟID
GROUP BY Year([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ]), Month([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ]), [ΕΠΩΝΥΜΟ] & " " & [ΟΝΟΜΑ], ypiresiestbl.ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ,
ypaliloitbl.ΕΠΩΝΥΜΟ, ypaliloitbl.ΟΝΟΜΑ, ypiresiestbl.ΧΡΕΩΣΗID, ypiresiestbl.ΩΡΑΡΙΟID, ypaliloitbl.ΥΠΑΛΛΗΛΟΣID, argiestbl.ΑΡΓΙΑ "

'Set your parameters
strSql = strSql & "HAVING (((Year([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ]))= "& me.FrmSelect.YEAR & " ) And ((Month([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ]))= " & me.FrmSelect.MONTH "
strSql = strSql & ") And ((ypaliloitbl.ΥΠΑΛΛΗΛΟΣID)<>58 And (ypaliloitbl.ΥΠΑΛΛΗΛΟΣID)<>60) And ((Weekday([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ]))=7))
ORDER BY Year([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ]), Month([ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ]), [ΕΠΩΝΥΜΟ] & " " & [ΟΝΟΜΑ], ypiresiestbl.ΗΜΕΡΑΥΠΗΡΕΣΙΑΣ;"

currentdb.querydefs("Qrsavvataepilogi").sql = strSql
[/CODE]
Thank you i shall try soon
 

bastanu

AWF VIP
Local time
Today, 16:01
Joined
Apr 13, 2010
Messages
1,401
Please have a look at the update file.

Cheers,
 

Attachments

  • SERVICE_Vlad.zip
    159 KB · Views: 249

Manos39

Registered User.
Local time
Today, 16:01
Joined
Feb 14, 2011
Messages
248
Thank you for your help ;)
I have tested the updated file and the report opens for year 2022 and month January (1) populated (very good!) from the command button on the form as i wanted in the first place!
But.. when i add some Saturdays that employees have worked in the next month (February) as a test, then hit the command button on form again, there comes a notice message 'Microsoft database engine does not recognize '' as a valid field or expression'.
Both queries alone work well on month February, (showing 2 employees having worked) but report does not open.

I attach modified database for test if you would please assist me on that?
 

Attachments

  • SERVICE_Vlad.zip
    166.7 KB · Views: 216
Last edited:

bastanu

AWF VIP
Local time
Today, 16:01
Joined
Apr 13, 2010
Messages
1,401
Here you are Manos, please review this version.

Crossposting in itself is not the problem, but you need to let us know you do it so we do not waste time if the post was answered somewhere else :).

Cheers,
Vlad
 

Attachments

  • SERVICE_Vlad_LatestFixed.zip
    161.5 KB · Views: 233

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:01
Joined
May 7, 2009
Messages
19,169
here is another.
 

Attachments

  • SERVICE.mdb
    1 MB · Views: 247

SHANEMAC51

Active member
Local time
Tomorrow, 02:01
Joined
Jan 28, 2022
Messages
310
Having this problem over 10 years i need to ask again having simplyfied my database to a small sample
I slightly corrected the names in the request, form, report, because I thought the Greek letters were to blame

but the main error is in the report setup code, which is in the form
 

Attachments

  • Screenshot_11-08.png
    Screenshot_11-08.png
    23.2 KB · Views: 224
  • SERVICE23_Vlad23mm0311.mdb
    1.1 MB · Views: 239

SHANEMAC51

Active member
Local time
Tomorrow, 02:01
Joined
Jan 28, 2022
Messages
310
sorry but not following you here, what exactly have you fixed and why?
I didn 't find the error right away , so I first removed it from requests and forms
1 reserved words YEAR and MONTH
2 then I entered aliases in the queries so that the Greek letters in the names did not get into the form

when the report didn't open after that, I started checking the code(for each data field)
 

Attachments

  • Screenshot_11-09.png
    Screenshot_11-09.png
    132 KB · Views: 243
  • Screenshot_11-41.png
    Screenshot_11-41.png
    25.4 KB · Views: 214
  • Screenshot_11-42.png
    Screenshot_11-42.png
    27.7 KB · Views: 223
  • Screenshot_11-46.png
    Screenshot_11-46.png
    36.2 KB · Views: 160
  • Screenshot_11-0940.png
    Screenshot_11-0940.png
    36.5 KB · Views: 162

Manos39

Registered User.
Local time
Today, 16:01
Joined
Feb 14, 2011
Messages
248
I shall try your solutions and let you know soon. Thank you all. The problem isnt solved or i havent got any suggestion anywhere else. Thank you all
 

SHANEMAC51

Active member
Local time
Tomorrow, 02:01
Joined
Jan 28, 2022
Messages
310
I shall try your solutions and let you know soon. Thank you all. The problem isnt solved or i havent got any suggestion anywhere else. Thank you all
PERHAPS IT IS ENOUGH TO FIX ONLY the CODE
I will try on the source base, without my other fixes
 

Manos39

Registered User.
Local time
Today, 16:01
Joined
Feb 14, 2011
Messages
248
Your solutions work great yet.. when taking everything you ve changed hopefully to work in my original db, i get this.. Maybe i miss something Shanemac51
Hope you could take a look in my DB as it is difficult to manage changes with my knowledge
 

Attachments

  • Screenshot 2022-03-11 113546.png
    Screenshot 2022-03-11 113546.png
    41.6 KB · Views: 231
  • Screenshot 2022-03-11 120750 select form.png
    Screenshot 2022-03-11 120750 select form.png
    31.4 KB · Views: 219
  • Screenshot 2022-03-11 120906 select form.png
    Screenshot 2022-03-11 120906 select form.png
    64.4 KB · Views: 226
  • Screenshot 2022-03-11 134912 reference.png
    Screenshot 2022-03-11 134912 reference.png
    80 KB · Views: 163
Last edited:

bastanu

AWF VIP
Local time
Today, 16:01
Joined
Apr 13, 2010
Messages
1,401
Manos,
Have you tried my latest file from yesterday? What were the issues as it worked OK for me?

Cheers,
 

Manos39

Registered User.
Local time
Today, 16:01
Joined
Feb 14, 2011
Messages
248
Manos,
Have you tried my latest file from yesterday? What were the issues as it worked OK for me?

Cheers,Yes of course i did and despite the fact you might had a problem with Greek characters your solution was great. Put the code on click of the cmbutton in four button to call 4 sort of same queries and managed to go fine with it. I think it s finished with the problem. Enough years having this issue to go inside query and change manually criteria.. Thank you so much.
 

SHANEMAC51

Active member
Local time
Tomorrow, 02:01
Joined
Jan 28, 2022
Messages
310
Надеюсь, вы могли бы заглянуть в мою БД, так как трудно управлять изменениями с моими знаниями
the main thing that is practically necessary in such databases is the names of objects (tables, forms, reports, queries, fields)

1 english name - preferably
2 or at least the English transcription

moreover, without spaces and other separators, only the underscore character is allowed, for example, client_address, client_code

I'll take a look, a little later
 

Users who are viewing this thread

Top Bottom