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

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?

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?

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.

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.