473,836 Members | 1,521 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

2003 - 2010 MDE problems

365 Contributor
Hi Fellas, its been a while,

Got a little problem with a project written in 2003 (access obviously).... we recently upgraded to 2010 at work, and now the MDE appears to not work (in 2010) however the MDB works fine so it seems.

I dont have the time right now to do any heavy work on this so i was hoping it would "just work", if i converted it to the new file formats would i encounter any difficulties?

the message i get (when running the MDE in 2010) is this:

Expand|Select|Wrap|Line Numbers
  1. Err.number = 3075
  2. Err.desc = Function is not available in query expression '(((tblStaff.Deleted)=False) AND ((tblAppraisal.AppMan)=1 AND ((Max(tblAppraisal.Date))<DateAdd('yyyy',-1,Date()))'.
Any ideas??
Apr 27 '12
22 5711
NeoPa
32,584 Recognized Expert Moderator MVP
Thank you Dan. I've looked through your posted SQL and see nothing that catches my attention other than the three function calls already commented on in post #6.

I would just make a further comment, not to be critical, but simply for you to bear in mind in future dealings with the site :
When posting SQL there are two fairly important points to remember :
  1. Post the actual SQL used rather than the VBA code which creates the SQL. I don't imagine it makes a lot of difference in this situation, but it means that anyone looking at it can concentrate on the SQL itself rather than using half a mind working out what it would look like when produced. This is particularly important when variables are used to create bits of the SQL (EG. LogStaffID).
  2. Display the SQL in human-readable form. A single line of text requiring continuous scrolling to read and make sense of, is fine for a SQL parser, but makes it hard to work with as a human. Very few members bother to think of this to be fair, so you can make your questions stand out by showing your SQL as below.
Expand|Select|Wrap|Line Numbers
  1. SELECT   tblAppraisal.AppID
  2.        , tblAppraisal.StaffID
  3.        , [Forename] & ' ' & [Surname] AS Name
  4.        , tblAppraisal.Date
  5. FROM     tblAppraisal
  6.          INNER JOIN
  7.          tblStaff
  8.   ON     tblAppraisal.StaffID = tblStaff.StaffID
  9. GROUP BY tblAppraisal.AppID
  10.        , tblAppraisal.StaffID
  11.        , [Forename] & ' ' & [Surname]
  12.        , tblAppraisal.Date
  13.        , tblStaff.Deleted
  14.        , tblAppraisal.AppMan
  15. HAVING   (((tblStaff.Deleted)=False)
  16.    AND   ((tblAppraisal.AppMan)=???)
  17.    AND   ((Max(tblAppraisal.Date))<DateAdd('yyyy',-1,Date())))
May 5 '12 #11
Dan2kx
365 Contributor
Noted, sorry again NeoPa, what do you think to the invalid reference theory i suggested? i havent tried it yet because it would mean quite a few UserForm modifications.
May 5 '12 #12
NeoPa
32,584 Recognized Expert Moderator MVP
Having now had a chance to see it in a more readable form it occurs to me that using Max() on a field which is included in the GROUP BY clause (tblAppraisal.D ate) can never work. I've never seen SQL such as this be accepted by Access (of any version). I suspect you want to lose the function call Max() from your HAVING clause. Try this :
Expand|Select|Wrap|Line Numbers
  1. SELECT   tblAppraisal.AppID
  2.        , tblAppraisal.StaffID
  3.        , [Forename] & ' ' & [Surname] AS [Name]
  4.        , tblAppraisal.Date
  5. FROM     tblAppraisal
  6.          INNER JOIN
  7.          tblStaff
  8.   ON     tblAppraisal.StaffID = tblStaff.StaffID
  9. WHERE    (NOT tblStaff.Deleted)
  10.   AND    (tblAppraisal.AppMan=???)
  11. GROUP BY tblAppraisal.AppID
  12.        , tblAppraisal.StaffID
  13.        , [Forename] & ' ' & [Surname]
  14.        , tblAppraisal.Date
  15. HAVING   (tblAppraisal.Date<DateAdd('yyyy',-1,Date()))
I expect your reporting this as working in an earlier version is an honest mistake. I'm confident you believed that when you posted it, but that rather misled the searches I expect. I'm sure it's not important now, but if you go back to an earlier version and try to enter your original SQL into a new QueryDef then I'm sure it will give you an error message. It might be interesting to hear how this came about though, if you find out.
May 5 '12 #13
NeoPa
32,584 Recognized Expert Moderator MVP
Dan2kx:
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.
No need to apologise Dan. I wasn't trying to criticise. Simply illustrate a better way forward for you ;-)

I suspect, now I've seen the SQL in its full glory, that the problem is identified and the invalid reference theory was a red herring. We only know that now, of course. Ideas are always worth checking, until you find an answer.
May 5 '12 #14
Dan2kx
365 Contributor
Hello again,

I have just tried the SQL as a new Query, and it works error free (in the MDB, 2010), i used MAX because in the lookup table there can be multiple dates per StaffID and i wanted the last one.

Expand|Select|Wrap|Line Numbers
  1. tblAppraisal:
  2. AppID, Auto, PK
  3. StaffID, Num, FK
  4. AppMan, Num, FK 
  5. Date, D/T
May 5 '12 #15
NeoPa
32,584 Recognized Expert Moderator MVP
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.
May 5 '12 #16
Dan2kx
365 Contributor
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
May 5 '12 #17
NeoPa
32,584 Recognized Expert Moderator MVP
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).
May 5 '12 #18
Dan2kx
365 Contributor
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

Expand|Select|Wrap|Line Numbers
  1. Function is not available in query expression '(((tblAppraisal.Date<DateAdd('yyyy',-1,Date()))'
May 5 '12 #19
NeoPa
32,584 Recognized Expert Moderator MVP
Interesting. Not what I expected, but try this for me if you would :
Expand|Select|Wrap|Line Numbers
  1. SELECT   tblAppraisal.AppID
  2.        , tblAppraisal.StaffID
  3.        , [Forename] & ' ' & [Surname] AS [Name]
  4.        , tblAppraisal.Date
  5. FROM     tblAppraisal
  6.          INNER JOIN
  7.          tblStaff
  8.   ON     tblAppraisal.StaffID = tblStaff.StaffID
  9. WHERE    (NOT tblStaff.Deleted)
  10.   AND    (tblAppraisal.AppMan=???)
  11.   AND    (tblAppraisal.Date<DateAdd('yyyy',-1,Date()))
  12. GROUP BY tblAppraisal.AppID
  13.        , tblAppraisal.StaffID
  14.        , [Forename] & ' ' & [Surname]
  15.        , 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).
May 5 '12 #20

Sign in to post your reply or Sign up for a free account.

Similar topics

3
237
by: Robert Scheer | last post by:
Hi. When installing a windows 2003 server, the option to install IIS 6.0 also shows an item called ASP.NET. What is the purpose of this option? I heard that windows 2003 already has the .NET Framework inside it. Can I install VS.NET 2003 without problems on a windows 2003 server machine, or will I overwrite the version that comes with windows 2003? Thanks,
1
1747
by: Philip Townsend | last post by:
I have a datagrid that contains a dropdown list that is part of an EditItemTemplate. I need to run some code when the dropdown list builds in order to preselect an item in the list. Preselecting the item is not the problem. The problem is that the dropdown object is not instantiated at the point at which the Edit button is clicked, or at any other obvious step in the process. In other words, when a user clicks the Edit (EditCommand)...
0
891
by: CharlieR | last post by:
Hi I have mangage to install the prerequisites disk for Vis Studio.net and am trying to now install VB.net 2003 Standard edition, using the setup wizard. When I click on the setup link the product opens my printer/scaner utility. I have no other options and if I close this window the setup also closes. Tried to bypass this by going via vs_setup file, but this insists I use the other setup and I go round the loop again.
1
2178
by: ripvonboomer | last post by:
I am fairly new to programming, and have purchased and installed VS 2003. No problems with windows apps, but I tried to create a new ASP.Net web app, and it won't even build the solution. I am getting the following error: "Visual Studio .NET cannot create the application <application>. No Web server was detected at this URL: <URL> Internet Information Services (IIS) is not installed on this computer. You need to install IIS and then...
5
5082
by: Chris Gage | last post by:
I'm a longtime *nix guy and am fairly comfortable "over there" although I am a developer and not a sysadmin, mostly J2EE stuff. I recently got the Microsoft "Action Pack" as a partner. It has legit licenses for a bunch of Microsft software for development/evaluation purposes. I decided to install Windows Server 2003 - the first time I have ever installed a Windows Server. I decided to install Apache rather than try to learn IIS, and...
2
1604
by: kashif73 | last post by:
Hi, I have a strange problem in displaying of reports on Windows XP and Vista. My crosstab query display mission records for a particular month, for employees of my organisation. For example if I search by month of "April", the crosstab query will display all staff ( of different departments) who were on mission in April. On Windows Vista the report displays fine, that is the query only fetches records for APRIL. However on an XP system,...
2
6718
by: sierra7 | last post by:
It seems Access 2010 is associating an 'input mask' or field type with a combo box when a form is opened, even though there is no Format setting on the control. I have a form which has been running OK for years in Access 2003; it adds an item selected from an inventory listing to either an existing Sales Order or Purchase Order. For historic reasons Purchase orders are alphanumeric and Sales orders are numeric (long integer). The RowSource...
1
2666
by: pokemon | last post by:
Friends, The error logs are pasted below. Kindly advise remedial steps. Thanks Log Name: Microsoft-Windows-Diagnostics-Performance/Operational Source: Microsoft-Windows-Diagnostics-Performance Date: 11-09-2010 11:26:45 Event ID: 100 Task Category: Boot Performance Monitoring Level: Critical
1
5273
by: Alan Yim | last post by:
Hi folks, My company recently upgraded our Office suite from 2003 to 2010. The problem in particular is with an Access database that was originally designed in Access 2003. The code in question used work in 2003 (see below code). Private Sub engSave_Click() Dim strSql3 As String 'Archive order number and cost data for engine.
1
2051
by: b wesenberg | last post by:
Good Morning, I am hoping that someone can assist me with a code problem. Here is the history of the issue. We had a database that was created in Access 2003 the database had a module in it called Median: Public Function MedianOfRst(RstName As String, Company_T As String, Company_V As String, State_T As String, State_V As String, Group_T As String, Group_V As String, Sub_Group_T As String, Sub_Group_V As String, Band_T As String,...
0
9816
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9668
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9371
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7790
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5647
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5823
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4448
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4013
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3112
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.