Dsum in report syntax error?

[Manos39]

I know i would be pleased to know access like you and maybe you to know greek like me. I ll try that when i ll undreastand which would be the right names

 

[vbaInet]

Not alot was changed. If you compare it to the other statements it will be this:

=Nz(DSum("[ΔΙΑΦΟΡΑ ΣΕ ΩΡΕΣ]";"[Qrevdomada]";"[ΕΠΩΝΥΜΟ] = '" & [ΕΠΩΝΥΜΟ] & "' AND CLng(Replace([ΑΝΑ ΕΤΟΣ-ΜΗΝΑ], '-', '')) <= " & CLng(Format(DateAdd("m";-1;"28/" & [ΑΝΑ ΕΤΟΣ-ΜΗΝΑ]);"yyyymm"))))

=Nz(DSum("[ΔΙΑΦΟΡΑ ΣΕ ΩΡΕΣ]";"[Qrevdomada]";"[ΕΠΩΝΥΜΟ] = '" & [ΕΠΩΝΥΜΟ] & "' AND CLng(Replace([ΑΝΑ ΕΤΟΣ-ΜΗΝΑ], '-', '')) <= " & CLng(Format(DateAdd('m';-1;'28/' & [ΑΝΑ ΕΤΟΣ-ΜΗΝΑ]);'yyyymm'))))

AllocationID and Allocations was just a typo.

 

[Manos39]

It refuses.. and insists.. thank you VbInet i ll try to think of another way two days is enough..

 

[vbaInet]

Meaning you still see #Error?

Let's see your database.

 

[Manos39]

Here it is.. query is SynolaOron
i want to have a result like report Qrminas the workhours of employees but for spesific month for each employee
and to bring the previous month of his so then in the same report to be able to sum previus, due. and have a result because dayoffs are calculated by (totalworkhous till that day/8.5) and so on..

 

[vbaInet]

Here:

=Nz(DSum("[ΔΙΑΦΟΡΑ ΣΕ ΩΡΕΣ]";"[Qrevdomada]";"[ΕΠΩΝΥΜΟ] = " & [ΕΠΩΝΥΜΟ] & " AND CLng(Replace([ΑΝΑ ΕΤΟΣ-ΜΗΝΑ]; '-'; '')) <= " & CLng(Format(DateAdd("m";-1;[ΑΝΑ ΕΤΟΣ-ΜΗΝΑ] & "-28");"yyyymm"))))

By the way [ΕΠΩΝΥΜΟ] is a Number data type and numbers don't need to be surronded in single quotes.

 

[Manos39]

It is not working.. error again..

 

[Manos39]

I utilised Stephen Lebans / Peter Schoeders serialize function to number
query row [ΑΝΑ ΕΤΟΣ-ΜΗΝΑ]
and with code:
=Nz(DSum("[ΔΙΑΦΟΡΑ ΣΕ ΩΡΕΣ]";"[Qrevdomada]";"[ΕΠΩΝΥΜΟ] = " & [ΕΠΩΝΥΜΟ] & " AND ([Serialize]) < " & ([Serialize])))
in my report was a go..
Thank you all for your help. Probably it should work with your great help it is tricky in the greek because of regional settings i dont know what i was doing wrong..

 

[vbaInet]

I tested it on your db before sending it to you and it was working, and of course I changed the commas to semi-colons too.

Glad to see you found a working solution.