Function isn't available in expressions in query expression

R

Reiner Harmgardt

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
 
A

Allen Browne

R

Reiner Harmgardt

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 said:
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
Click to expand...


i checked this point.
it is not the case.



Alternatively, you may have to get rid of sandbox mode:

http://office.microsoft.com/assistance/preview.aspx?AssetID=HP010504321033&C
TT=8&Origin=EC011081751033&Product=acc2003
Click to expand...

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
Click to expand...

no conversions so far.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Reiner Harmgardt said:
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?
Click to expand...
Click to expand...
 
A

Allen Browne

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.
 
R

Reiner Harmgardt

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
 
Top