Prompt user for date

[SaraMegan]

I am trying to create a query which will generate a report based on a date entered by the user. I can't, however, get the formatting right... When I create a prompt, the calculations in other fields return #ERROR. When I enter a date manually into the query, they return working fine. I'm sure this is just a stupid issue I'm having with my query, but as I'm the eternal newbie, I just can't figure it out. I'll post the SQL I have right now:

SELECT [tblEmployees].[Last] & ", " & [First] AS Employee, tblEvaluations.HireDate, tblEvaluations.ProbDate, [Enter Date of Report:] AS Today, Format([Today]-[ProbDate],"m") AS DiffProbation, IIf([DiffProbation]<=12,Yes,No) AS ProbationStatus
FROM tblEvaluations INNER JOIN tblEmployees ON tblEvaluations.TDNo = tblEmployees.TDNo
WHERE (((tblEvaluations.ProbDate) Is Not Null));

Also, how can I change it so that ProbationStatus turns up No if the ProbDate field is null? I know I'll have to take the
WHERE (((tblEvaluations.ProbDate) Is Not Null));
out of it... but when I had all the dates in, all the results with nothing in the ProbDate field turned up #ERROR.

Thanks in advance.

--Sara

 

[SaraMegan]

Thanks, Pat! That's helpful. But I have a brand new problem now.

It's now returning a "No" for all results, except those whose date is after today. (ie they haven't started work at the new position yet.)

Employees are on probation for one year after their ProbDate.

Any further suggestions?

Thanks again!

--Sara

 

[SaraMegan]

I was just able to try this... and it returned the same result. All dates prior to the report date came up "no" and dates in the future came up "yes."

Any other thoughts?

Thanks again.

--Sara