dynamictiger
Registered User.
- Local time
- Today, 07:55
- Joined
- Feb 3, 2002
- Messages
- 270
I have a fairly complex situation where I need to get monthly historic data for comparison in a report. The complexity is the month is not fixed. Our month rolls from Tuesday closest to the 17th of the month to the following months Tuesday closest to the 17th except where there is 5 or more working days after the following Tuesday to end of the month. It works practically allowing time for paperwork processing however it is presenting an interesting challenge in historical data compilation.
We use month names so for clarity June 2019 started on Tuesday 21st of May and will end on Tuesday the 18th of June.
As I have the data collected into jobs by month names I thought the simplest way to sort this would be to get the job name remove the 2019 for example and replace with 2018 and its there the issue shows up.
I am using the following for the test of this concept:
I am assuming this will return the 2019 portion of MjDescrip however the outcome is:
Not expected output. I don't do this sort of work often enough to recall what the issue is?
We use month names so for clarity June 2019 started on Tuesday 21st of May and will end on Tuesday the 18th of June.
As I have the data collected into jobs by month names I thought the simplest way to sort this would be to get the job name remove the 2019 for example and replace with 2018 and its there the issue shows up.
I am using the following for the test of this concept:
Code:
SELECT qryCrownJobDescriptionSearch.MJDescrip, Right(Trim([MjDescrip]),4) AS Expr1
FROM qryCrownJobDescriptionSearch
GROUP BY qryCrownJobDescriptionSearch.MJDescrip, Right(Trim([MjDescrip]),4);
I am assuming this will return the 2019 portion of MjDescrip however the outcome is:
Code:
MJDescrip Expr1
"CT Male Vitality Chemical Only May 2019" "9"
"CT Plunge Pool Chemical Only May 2019" "19"
"CT Port Cochere Wf Chemical Only May 2019" ""
Not expected output. I don't do this sort of work often enough to recall what the issue is?