If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Function isn't available in expressions in query expression
Hi NG
i got the following code running perfect under Access97 full version. if i let it run under Access97 runtime version i receive the error message mentioned above at the line Mid([Objekt],9) & "" "" & [Kostenart]. SQL = "" SQL = SQL & " INSERT INTO [" & xTab1 & "] " SQL = SQL & " ( Contract, [Key SAP], [Key SAP Original], CY, CM, CHF )" SQL = SQL & " SELECT " SQL = SQL & " A.Projektdefinition AS Contract, " SQL = SQL & " Mid([Objekt],9) & "" "" & [Kostenart] AS [Key SAP], " SQL = SQL & " Mid([Objekt],9) & "" "" & [Kostenart] AS [Key SAP Original], " SQL = SQL & " Year([Buch#dat#]) AS CY, " SQL = SQL & " Month([Buch#dat#]) AS CM, " SQL = SQL & " Sum(A.[Wert/KWähr]) AS CHF" SQL = SQL & " FROM [EP Import EXCEL] AS A" SQL = SQL & " WHERE " SQL = SQL & " (((A.KWähr) = ""CHF""))" SQL = SQL & " GROUP BY " SQL = SQL & " A.Projektdefinition, " SQL = SQL & " Mid([Objekt],9) & "" "" & [Kostenart], " SQL = SQL & " Year([Buch#dat#]), " SQL = SQL & " Month([Buch#dat#])" SQL = SQL & " HAVING " SQL = SQL & " (((Sum(A.[Wert/KWähr]))=0.01 " SQL = SQL & " OR (Sum(A.[Wert/KWähr]))=-0.01));" DoCmd.RunSQL SQL i tried to use with Left([Objekt],9)... but with the same result. can anyone tell the me where the problem is to be found? Thanks a lot Reiner |
#2
|
|||
|
|||
Function isn't available in expressions in query expression
The problem could be with References.
Press Ctrl+G to open the Immediate Window. Choose References from the Tools menu. Look for anything marked "MISSING. More info: http://allenbrowne.com/ser-38.html Alternatively, you may have to get rid of sandbox mode: http://office.microsoft.com/assistan...roduct=acc2003 If you have recently converted from A97 to A2002, this article may also save you some grief: Converting from A97 at: http://allenbrowne.com/ser-48.html -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Reiner Harmgardt" wrote in message ... Hi NG i got the following code running perfect under Access97 full version. if i let it run under Access97 runtime version i receive the error message mentioned above at the line Mid([Objekt],9) & "" "" & [Kostenart]. SQL = "" SQL = SQL & " INSERT INTO [" & xTab1 & "] " SQL = SQL & " ( Contract, [Key SAP], [Key SAP Original], CY, CM, CHF )" SQL = SQL & " SELECT " SQL = SQL & " A.Projektdefinition AS Contract, " SQL = SQL & " Mid([Objekt],9) & "" "" & [Kostenart] AS [Key SAP], " SQL = SQL & " Mid([Objekt],9) & "" "" & [Kostenart] AS [Key SAP Original], " SQL = SQL & " Year([Buch#dat#]) AS CY, " SQL = SQL & " Month([Buch#dat#]) AS CM, " SQL = SQL & " Sum(A.[Wert/KWdhr]) AS CHF" SQL = SQL & " FROM [EP Import EXCEL] AS A" SQL = SQL & " WHERE " SQL = SQL & " (((A.KWdhr) = ""CHF""))" SQL = SQL & " GROUP BY " SQL = SQL & " A.Projektdefinition, " SQL = SQL & " Mid([Objekt],9) & "" "" & [Kostenart], " SQL = SQL & " Year([Buch#dat#]), " SQL = SQL & " Month([Buch#dat#])" SQL = SQL & " HAVING " SQL = SQL & " (((Sum(A.[Wert/KWdhr]))=0.01 " SQL = SQL & " OR (Sum(A.[Wert/KWdhr]))=-0.01));" DoCmd.RunSQL SQL i tried to use with Left([Objekt],9)... but with the same result. can anyone tell the me where the problem is to be found? |
#3
|
|||
|
|||
Function isn't available in expressions in query expression
Hi Allen, thank you for your instant response. i forgot to say, that i use access97. the runtime version with which i have the problem is running under Win2000. the full version which works fine is running under NT4.0. may it has something to do with that. "Allen Browne" wrote in message ... The problem could be with References. Press Ctrl+G to open the Immediate Window. Choose References from the Tools menu. Look for anything marked "MISSING. More info: http://allenbrowne.com/ser-38.html i checked this point. it is not the case. Alternatively, you may have to get rid of sandbox mode: http://office.microsoft.com/assistan...010504321033&C TT=8&Origin=EC011081751033&Product=acc2003 couldn't find this menue-point. maybe it's not existing in access97. If you have recently converted from A97 to A2002, this article may also save you some grief: Converting from A97 at: http://allenbrowne.com/ser-48.html no conversions so far. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Reiner Harmgardt" wrote in message ... Hi NG i got the following code running perfect under Access97 full version. if i let it run under Access97 runtime version i receive the error message mentioned above at the line Mid([Objekt],9) & "" "" & [Kostenart]. SQL = "" SQL = SQL & " INSERT INTO [" & xTab1 & "] " SQL = SQL & " ( Contract, [Key SAP], [Key SAP Original], CY, CM, CHF )" SQL = SQL & " SELECT " SQL = SQL & " A.Projektdefinition AS Contract, " SQL = SQL & " Mid([Objekt],9) & "" "" & [Kostenart] AS [Key SAP], " SQL = SQL & " Mid([Objekt],9) & "" "" & [Kostenart] AS [Key SAP Original], " SQL = SQL & " Year([Buch#dat#]) AS CY, " SQL = SQL & " Month([Buch#dat#]) AS CM, " SQL = SQL & " Sum(A.[Wert/KWdhr]) AS CHF" SQL = SQL & " FROM [EP Import EXCEL] AS A" SQL = SQL & " WHERE " SQL = SQL & " (((A.KWdhr) = ""CHF""))" SQL = SQL & " GROUP BY " SQL = SQL & " A.Projektdefinition, " SQL = SQL & " Mid([Objekt],9) & "" "" & [Kostenart], " SQL = SQL & " Year([Buch#dat#]), " SQL = SQL & " Month([Buch#dat#])" SQL = SQL & " HAVING " SQL = SQL & " (((Sum(A.[Wert/KWdhr]))=0.01 " SQL = SQL & " OR (Sum(A.[Wert/KWdhr]))=-0.01));" DoCmd.RunSQL SQL i tried to use with Left([Objekt],9)... but with the same result. can anyone tell the me where the problem is to be found? |
#4
|
|||
|
|||
Function isn't available in expressions in query expression
One of the libraries is not registered correctly on the Win 2000 machine.
Either reinstall the runtime, or use regedit to re-register the libraries, as suggested in the link. Actually, it could also have to do with the Win2000 user not having adequate permissions to use the libraries. Try running as Administrator to test that idea. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Reiner Harmgardt" wrote in message ... Hi Allen, thank you for your instant response. i forgot to say, that i use access97. the runtime version with which i have the problem is running under Win2000. the full version which works fine is running under NT4.0. may it has something to do with that. "Allen Browne" wrote in message ... The problem could be with References. Press Ctrl+G to open the Immediate Window. Choose References from the Tools menu. Look for anything marked "MISSING. More info: http://allenbrowne.com/ser-38.html i checked this point. it is not the case. Alternatively, you may have to get rid of sandbox mode: http://office.microsoft.com/assistan...010504321033&C TT=8&Origin=EC011081751033&Product=acc2003 couldn't find this menue-point. maybe it's not existing in access97. If you have recently converted from A97 to A2002, this article may also save you some grief: Converting from A97 at: http://allenbrowne.com/ser-48.html no conversions so far. |
#5
|
|||
|
|||
Function isn't available in expressions in query expression
Hi Allen,
thanks a lot for your help. In fact the da0-lib on the Win 2000 machine(from 2002) was different to the one on NT40 (from 1997). With your helpful links i found a routine to fix up the ref. on (http://support.microsoft.com/?kbid=194374) Thanks again and have a nice day! Reiner "Allen Browne" wrote in message ... One of the libraries is not registered correctly on the Win 2000 machine. Either reinstall the runtime, or use regedit to re-register the libraries, as suggested in the link. Actually, it could also have to do with the Win2000 user not having adequate permissions to use the libraries. Try running as Administrator to test that idea. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Reiner Harmgardt" wrote in message ... Hi Allen, thank you for your instant response. i forgot to say, that i use access97. the runtime version with which i have the problem is running under Win2000. the full version which works fine is running under NT4.0. may it has something to do with that. "Allen Browne" wrote in message ... The problem could be with References. Press Ctrl+G to open the Immediate Window. Choose References from the Tools menu. Look for anything marked "MISSING. More info: http://allenbrowne.com/ser-38.html i checked this point. it is not the case. Alternatively, you may have to get rid of sandbox mode: http://office.microsoft.com/assistan...010504321033&C TT=8&Origin=EC011081751033&Product=acc2003 couldn't find this menue-point. maybe it's not existing in access97. If you have recently converted from A97 to A2002, this article may also save you some grief: Converting from A97 at: http://allenbrowne.com/ser-48.html no conversions so far. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Switch function in crosstab query. | Karen | Running & Setting Up Queries | 1 | July 13th, 2004 08:36 PM |
Hidden files in Ms-Query cause ODBC connect errors or Query is wac | needyourhelp | General Discussion | 4 | July 12th, 2004 09:38 PM |
Use of Last function in a query | Andy | Running & Setting Up Queries | 7 | June 24th, 2004 09:41 AM |
Using Split function in a query. | Randal | Running & Setting Up Queries | 2 | June 11th, 2004 04:22 PM |
query field reference help | -dch | Running & Setting Up Queries | 4 | June 2nd, 2004 07:30 PM |