Finding the anniversary date for the year

[perryc]

So, I have this formula that gives me the date, but, the problem is, it says the query is too complex.



DueDate: DateSerial(Year(date()),Month([DateOfHire]),Day([DateOfHire]))

Detail:

SELECT ClinicianSupervisory.SID, ClinicianSupervisory.EmployeeName, ClinicianSupervisory.Discipline, ClinicianSupervisory.DateOfHire, ClinicianSupervisory.OnsiteDate, ClinicianSupervisory.PerformedBy, DateSerial(2019,Month([DateOfHire]),Day([DateOfHire])) AS DueDate, [OnsiteDate]+365 AS NextOnsite, [EmployeeName] & " " & [Discipline] AS Emp
FROM ClinicianSupervisory
WHERE ((([OnsiteDate]+365)>=[From] And ([OnsiteDate]+365)<=[Thru])) OR (((DateSerial(2019,Month([DateOfHire]),Day([DateOfHire])))>=[From] And (DateSerial(2019,Month([DateOfHire]),Day([DateOfHire])))<=[Thru]));


Thanks in advance.

Perry

[rpeare]

I would guess there is a piece of data missing which is why it's choking. Are you sure all of your records have a value in the field in question?

what does [from] and [thru] represent in your statement? are they being correctly valued?

if they are supposed to reference a value on a form they should be in the format [forms]![formname]![fieldname]

[perryc]

They work with a simple calculation, but with DueDate: DateSerial(Year(date()),Month([DateOfHire]),Day([DateOfHire])), it choks.

[perryc]

If I excluded the WHERE filter, then, it will run. However, on the report side, how can I just display those records that falls between the two From and Thru date?

[Micron]

The answer to the last question may be to filter the report on Load.
However, to resolve these situations the best approach is to eliminate variables one by one; for example having WHERE ((([OnsiteDate]+365)>= #05/20/2019# rather than
WHERE ((([OnsiteDate]+365)>=[From]. Obviously you have to use a properly formatted date that works for the expression.

Keep eliminating unknowns until something works. Your date inputs might not be formatted or entered correctly. Or they could be Null. Or the OnsiteDate is Null, etc. etc.

[perryc]

Micron, ty as usual.

I am not very good at Access programming. So, I discovered this "DueDate: DateSerial(Year(Date()),Month([DateOfHire]),Day([DateOfHire]))" and put a date filter, say, >#5/20/2019# yields a type mismatch error. Why?

[perryc]

Never mind... I had a work-around. Just filter in the Query the month only, then, it is much simplier.

[Micron]

I can't see your data no know your regional settings, which often come in to play when using dates. But I think the issue might lie elsewhere and have to wonder if underlying data requires DateSerial or why you can't simplify and do away with the expression.

F'rinstance, what value is Year being applied to, a year value or a date value? Documentation on the Year function will tell you that it is to be applied to a complete date (it is meant to extract the year from a date). If you had the entire date, why pull out the individual month, day and year values only to string them back into some valid date? That makes me think you are trying to pass the year function a year value. If you Year(2019), the result will probably be 1905. I bet you have no records with that date but that might be the year value that you're assembling and passing.

If you are not completely aware of the behaviour and application of a function, do careful research as it will never be a waste of time - especially if you're stringing several of them together and passing that to yet another one. Plus, examine why you have need the expression in that sql statement in the first place. You might have table design/data issues.

[rpeare]

I'm getting stuck on why that formula will choke. The only things I can think of are:

1. DateofHire is not actually being stored as a date, but as a text value
2. Some of your records do not have a DateofHire, I would put in a criteria on your query of IS NOT NULL on the DateofHire field to see if it functions correctly.

Something in that DateofHire field what's forcing the error.