2003 - 2010 MDE problems

[NeoPa]

Dan2kx:
I have just tried the SQL as a new Query, and it works error free (in the MDB, 2010)

Very strange. Perhaps you might clarify exactly what you tried. Are you saying that the same SQL you ran before, which threw up the error, is now running perfectly without errors after you put it in a new QueryDef? This is quite a statement.

Can you appreciate that Max([X]) is fairly meaningless when there is only one possible value of [X] (as ensured by GROUP BY [X])? That is why it will never be accepted by Jet (Access SQL).

Dan2kx:
I used MAX because in the lookup table there can be multiple dates per StaffID and i wanted the last one.

That indicates you have more work to do with your query to get it to work as intended. None of the SQL discussed so far will give you that result (Neither yours nor mine).

I suggest we get to the bottom of this seeming anomaly with using Max() on a field which is also included in the GROUP BY clause first, and then you may want to ask a separate question about how to select only the item that matches the maximum date within a group. It would be pointless to do that at this stage as your understanding of the issue would not be clear enough now to ask the question clearly. The primary problem would have to be cleared up first for it to make contextual sense.

[Dan2kx]

Very strange. Perhaps you might clarify exactly what you tried. Are you saying that the same SQL you ran before, which threw up the error, is now running perfectly without errors after you put it in a new QueryDef? This is quite a statement.

The error only occurs in the MDE file, and only if the MDE file is run in 2010, the MDB file works in all versions, and the Same SQL as a new Query also works and returns results.

Can you appreciate that Max([X]) is fairly meaningless when there is only one possible value of [X] (as ensured by GROUP BY [X])? That is why it will never be accepted by Jet (Access SQL).

As i see it, if grouping by date, it will list all the variable dates, if selecting the Max of date, and then grouping, i will only get one date, the maximum, and that also appears to work as expected.

That indicates you have more work to do with your query to get it to work as intended. None of the SQL discussed so far will give you that result (Neither yours nor mine).

I suggest we get to the bottom of this seeming anomaly with using Max() on a field which is also included in the GROUP BY clause first, and then you may want to ask a separate question about how to select only the item that matches the maximum date within a group. It would be pointless to do that at this stage as your understanding of the issue would not be clear enough now to ask the question clearly. The primary problem would have to be cleared up first for it to make contextual sense.

As above, the problem is only with MDE, i cannot reproduce the error code that the MDE provides anywhere else, other than in the MDE, but as you know, that is not for degugging.

PS, how do you quote proberly on here? lol

[NeoPa]

Dan2kx:
The error only occurs in the MDE file, and only if the MDE file is run in 2010, the MDB file works in all versions, and the Same SQL as a new Query also works and returns results.

I'll assume this is an answer of "Yes". I just tested it out myself, and to my great surprise, this illogical SQL is allowed by Jet in MDB format.

Dan2kx:
As i see it, if grouping by date, it will list all the variable dates, if selecting the Max of date, and then grouping, i will only get one date, the maximum, and that also appears to work as expected.

I'm afraid that illustrates some flawed logic. The GROUP BY clause occurs before the HAVING clause, so only one date is ever available to it. The Max() of [X] therefore, is simply [X]. Including Max() is thus illogical. Something I guess 2010 MDE files notice, even if MDB files don't.

Have you tried the changed SQL yet, that I suggested in post #13? Posting how you got on with that may well save further time, and will definately help the understanding of the situation.

Dan2kx:
PS, how do you quote properly on here? lol

Here is an example from post #14 :

NeoPa:
[quote=Dan2kx][highlight]Dan2kx:[/highlight]
Noted, sorry again NeoPa, what do you think to the invalid reference theory I suggested? I haven't tried it yet because it would mean quite a few UserForm modifications.[/quote]
No need to apologise Dan. I wasn't trying to criticise. Simply illustrate a better way forward for you ;-)

I have a macro to help me do it in TextPad (My preferred text editor).

[Dan2kx]

NeoPa
Have you tried the changed SQL yet, that I suggested in post #13? Posting how you got on with that may well save further time, and will definately help the understanding of the situation.

Just tried (with your SQL from #13), now in the MDE i get the same error, but with the reduced parameters

Function is not available in query expression '(((tblAppraisal.Date<DateAdd('yyyy',-1,Date()))'

[NeoPa]

Interesting. Not what I expected, but try this for me if you would :

SELECT   tblAppraisal.AppID
       , tblAppraisal.StaffID
       , [Forename] & ' ' & [Surname] AS [Name]
       , tblAppraisal.Date
FROM     tblAppraisal
         INNER JOIN
         tblStaff
  ON     tblAppraisal.StaffID = tblStaff.StaffID
WHERE    (NOT tblStaff.Deleted)
  AND    (tblAppraisal.AppMan=???)
  AND    (tblAppraisal.Date<DateAdd('yyyy',-1,Date()))
GROUP BY tblAppraisal.AppID
       , tblAppraisal.StaffID
       , [Forename] & ' ' & [Surname]
       , tblAppraisal.Date

I'd overlooked the fact that tblAppraisal.Da te, without the Max() call, could be checked in the WHERE clause (and this is a better idea where possible - even though Access defaults to using the HAVING clause for criteria in any aggregate query).

[Dan2kx]

NeoPa (et al), i have solved the problem, (apart from the obvious MAX logic you pointed out), and it was to do with the MSCAL.OCX file, i checked the reference again, and it was pointing to the C:\program..... ...\office11\.. ..

Office 11 as you will know will not exist on a 2010 install

i had repointed it to the network location within the MDB from 2010, but everytime i re-made the MDE on another PC (in 2003) it adjusted the reference back to the local version, hence the problem. Strange presentation though i'm sure you will agree.

Next question... how to overcome the Max problem...

Non of the SQL variations give me the last date, including mine (whoops) as you said, is the only way to do this with two seperate queries?

[NeoPa]

Dan2kx:
Next question... how to overcome the Max problem...

Absolutely.

I'll look out for this when you post it (Separate thread as I mentioned before).

PS. Well done for finding that. I admit it surprises me, but you checked it anyway, so well done. Which function was effected? I wouldn't expect either Date() or DateAdd() to be found in there. A real surprise.

PPS. Typically MS Office products replace any missing reference to older MS Office libraries with the one for the latest version. It's quite unclear how you would arrive at this problem.

[Dan2kx]

NeoPa
absolutely.

I'll look out for this when you post it (Separate thread as I mentioned before).

I will put it in a new thread as you request, i will do that soon (nearly hometime).

NeoPa
PS. Well done for finding that. I admit it surprises me, but you checked it anyway, so well done. Which function was effected? I wouldn't expect either Date() or DateAdd() to be found in there. A real surprise.

PPS. Typically MS Office products replace any missing reference to older MS Office libraries with the one for the latest version. It's quite unclear how you would arrive at this problem.

Well i found from that link (from my post #8) that the MSCAL.OSX function/feature has been been specifically removed from 2010 to allow for their date selector tool (which i havent tried).

I doubt that any of the functions were particularly affected, and up until that break in the code i dont think it was even used (its just a custom date picker in effect), maybe it was just the first piece of SQL code to be executed.

Thanks anyway NeoPa, you have obviously pointed out something i had overlooked.