report problem with underlying query? (1 Viewer)

[SaraMegan]

Hey everyone,

I've searched the forums, and found some stuff on this topic, but somehow it doesn't seem to quite answer my question.

I'm creating a db in two parts. The users have part One already and use it. It's been going pretty well, but then this problem occured.

I made a copy of the db to work on the second part, which i plan to export to the first part when it is completed.

Anyway, yesterday the first part did something strange. A user was trying to run a report from a combo box I'd created. The report prompts you to type in a date for the report, and calculates information based on that date. She typed in the date 11/1/02 and got this error message:

"This expression is typed incorrectly or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables"

So I thought something must be going on with the query. I ran the query the report is based on and it first displays the results, then an error message pops up immediately that says "Data type mismatch in criteria expression."

I tried the same query and report on the part of the db I was working on, and it worked fine, so I exported it to the first db. Same error. I tried running the queries that are part of the structure of the query the report is based on, and that worked fine.

The sql for the query the report is based on is:

SELECT qryProbations.DateToday, qryProbations.Employee, tblEmployees.PositionNo, tblPositions.ClassCode, qryProbations.DateHired, qryProbations.ProbationStatus, qrySupervisors.Supervisor,
IIf([qryProbations].[ProbationStatus]="no",IIf([qryProbations].[mdatetoday]-[qryProbations].[mhire]<=0,12+([qryProbations].[mdatetoday]-[qryProbations].[mhire]),[qryProbations].[mdatetoday]-[qryProbations].[mhire])) AS EvalAnnual,
IIf([EvalAnnual]=12,"Annual","Not Annual") AS EvalType
FROM qrySupervisors INNER JOIN (tblPositions RIGHT JOIN (qryProbations INNER JOIN tblEmployees ON qryProbations.TDNo = tblEmployees.TDNo) ON tblPositions.PositionNo = tblEmployees.PositionNo) ON qrySupervisors.TDNo = tblEmployees.TDNo
WHERE (((qryProbations.ProbationStatus)="No") AND ((IIf([qryProbations].[ProbationStatus]="no",IIf([qryProbations].[mdatetoday]-[qryProbations].[mhire]<=0,12+([qryProbations].[mdatetoday]-[qryProbations].[mhire]),[qryProbations].[mdatetoday]-[qryProbations].[mhire])))=12));


the SQL for qryProbations is:

PARAMETERS [Enter_Report_Date] DateTime;
SELECT [Enter_Report_Date] AS DateToday, tblEmployees.TDNo, tblEmployees.[Last] & ", " & [First] AS Employee, tblEmployees.HireDate AS DateHired, Format([DateHired],"m") AS Mhire, tblEmployees.ProbDate AS ProbationDate, IIf([ProbationDate] Is Null,[mDateToday],Format([ProbationDate],"m")) AS MProbation, Format([DateToday],"m") AS MDateToday, IIf([ProbationStatus]="Yes",IIf([MDateToday]-[MProbation]<0,12+([MDateToday]-[MProbation]),IIf([MDateToday]-[MProbation]=0,12,[MDateToday]-[MProbation])),"0") AS MonthProbation, IIf([ProbationDate]>[DateToday],"Probation date is after today",Format([DateToday]-[ProbationDate],"yy")) AS YearProbation, IIf([ProbationDate] Is Null,"No",IIf(IsNumeric([YearProbation]),IIf([YearProbation]>0,"No","Yes"),"No")) AS ProbationStatus, IIf([ProbationStatus]="no",IIf([mdatetoday]-[mhire]<=0,12+([mdatetoday]-[mhire]),[mdatetoday]-[mhire])) AS EvalAnnual
FROM tblEmployees;

I wish I knew more about sql... I don't, though... but i think it's really strange that the queries are exactly the same as the ones in my copy of the database which works.

Any help is greatly appreciated. Please let me know if I need to clarify anything.

Thanks in advance. If you've made it this far, wow.

--Sara

 

[aziz rasul]

Sara, try 11/01/02 instead of 11/1/02 and see if makes a difference.

 

[SaraMegan]

thanks for teh reply

We've tried that, though... we've also tried 11-01-02 and November 11, 2002 and 11/1/2002 (and all of their variants.)

 

[ColinEssex]

Hi Sara

Given that presumably the date entered is that funny way you Americans do dates (only joking ) should the format be mm/dd/yy? the date she typed was mm/d/yy - does that make a difference?

Also the date (if American) is in the future, is that ok too?

Hope you're well

Col

Edit

Oh blimey!!! everyone thought of that!

 

[SaraMegan]

Hi, Col!!

Yeah, the date is supposed to be in the future. It's for determining whose supervisors need to fill out evaluations in a given month. (annual and semi-annual) So they run the report in advance so they can send out the evaluations to the supervisors and (hopefully) get them back in a timely fashion.

Again, the stuff works on my copy... is there anywhere I'm neglecting to look?

 

[aziz rasul]

Are you running the queries on the same machine? If not, it might be due to how the date is set in one machine and on the other machine!

 

[SaraMegan]

Yeah, we're running both on the same machine. It does the same on every machine we've tried it on so far (5 of them...)

Such a strange problem...

 

[aziz rasul]

Perhaps the Boogie Man has entered your machine.

 

[gblack]

Type mismatch. . .

I've had problems like that too B4.

Most often its because a field in the table that you are running the query from has changed from a date field to a text or number field.

Whenever Access tells you type mismatch then you need to check the design of your tables and see if they match the type of values you are trying to enter.

Look at the Table that the query is based on and see if that field is still a Date/Time field or if its been changed to a number or text. . .

 

[SaraMegan]

In the tables, the dates are all defined as dates. Is there any way the calculated dates aren't formatted correctly? In the parameter part of the SQL I've put DateTime as the data type for what the user is supposed to enter. It won't accept things not in short date format...

And all the things I use to calculate stuff, I think are formatted all right. (We tried using DatePart but for some reason that didn't work with this, so we used Format instead.)

Any further thoughts from anyone?

Thanks.

--Sara

 

[gblack]

Hmm

OK did you try using the pound signs? #11/01/2002#

Also do this: leave the criteria blank and see what the query pulls up. Copy a cell and then go back and paste it as your criteria. . .does it run then, or give you an error message?

 

[SaraMegan]

I tried entering #11/1/02# and it said "The Value you entered isn't valid for this field."

I tried leaving it blank, then tried cutting and pasting in both the report and the query: here's what happened:

Report: in both, it gave the same message "This expression is typed incorrectly..."

Query: Leaving it blank gave me the "expression is typed incorrectly" and pasting a date gave me "Data Type mismatch" with two separate message boxes, and then the db froze.

I've tried running the repair database tool, just in case. No luck

I've tried compacting it also, just in case. No luck.

 

[gblack]

Leaving it blank should have pulled up all dates. . .go to your table and take out a date and paste it into your query. . .see if that does anything

If not, I would make another query and see if you can do anything with that date field. . .if you can. . rewrite the query and delete the other one, then give the new query that name. . .

What an annoying problem!

 

[SaraMegan]

The date I'm entering isn't really "criteria..." It's a variable. The query does a kind of datediff thing between a date and this date, and then the criteria is the result (difference in month) = 12. (I get the same set of problems with an identical query except the criteria is = 6. So it wouldn't bring up all dates.

But I agree. Very annoying problem.