Hair Puller - Query Error

[Shane G. Brodie]

Intersting query problem
I have a relaively simple, if long query which operates from within an MS
Access 2000 application where the front-end database contains links to the
backend database residing on a file server. All queries used in the
application are defined on the front-end, all tables are on the backend.

One particular query runs fine if defined and executed from the back-end
database. The same query, when run from the front-end database fails with
the following error:

"Function is not available in query expression'[CompanyName] & Chr(13) &
[Address1] & " " & [Address2] & Chr(13) & [City] & ", " & [Province] & " " &
[PostalCode]'.

Here is the entire query:

PARAMETERS [ParamContractID] Long;
SELECT tblCompany.CompanyName, [CompanyName] & Chr(13) & [Address1] & " " &
[Address2] & Chr(13) & [City] & ", " & [Province] & " " & [PostalCode] AS
CompanyNameAddress, [CompanyName] & Chr(13) & [Address1] & Chr(13) &
[Address2] & Chr(13) & [City] & ", " & [Province] & " " & [PostalCode] AS
CompanyAddress1pl, tblCompany.Address1, tblCompany.Address2,
tblCompany.City, tblCompany.Province, tblCompany.PostalCode,
tblCompany.ContactName, [ContactName] & " - " & [CompanyName] & " - " &
[Address1] & " " & [Address2] & " " & [City] & ", " & [Province] & " " &
[PostalCode] AS CompanyContact, tblContractCompany.ContractID,
tblContractCompany.IncInContract, tblContractCompany.PrimaryContact
FROM tblContractCompany INNER JOIN tblCompany ON
tblContractCompany.CompanyID = tblCompany.CompanyID
WHERE (((tblContractCompany.ContractID)=[ParamContractID]) AND
((tblContractCompany.PrimaryContact)=True));

Be kind ... I didn't write the app ... I just have to fix it ...

--
Shane Brodie
IT Consultant
c/o MANITOBA FILM & SOUND
333-93 Lombard Avenue
Winnipeg MB R3B 1B3
tel 204-947-2040
fax 204-956-5261
website: http://www.mbfilmsound.mb.ca
e-mail: (e-mail address removed)

 

[Lynn Trapp]

Check your frontend for any broken references.

 

[Shane G. Brodie]

Done ... first thing I tried ... Linked Table Manager

Thanks

Shane

Check your frontend for any broken references.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Intersting query problem
I have a relaively simple, if long query which operates from within an MS
Access 2000 application where the front-end database contains links to the
backend database residing on a file server. All queries used in the
application are defined on the front-end, all tables are on the backend.

One particular query runs fine if defined and executed from the back-end
database. The same query, when run from the front-end database fails with
the following error:

"Function is not available in query expression'[CompanyName] & Chr(13) &
[Address1] & " " & [Address2] & Chr(13) & [City] & ", " & [Province] & "

"

&
[PostalCode]'.

Here is the entire query:

PARAMETERS [ParamContractID] Long;
SELECT tblCompany.CompanyName, [CompanyName] & Chr(13) & [Address1] & "

"

&
[Address2] & Chr(13) & [City] & ", " & [Province] & " " & [PostalCode] AS
CompanyNameAddress, [CompanyName] & Chr(13) & [Address1] & Chr(13) &
[Address2] & Chr(13) & [City] & ", " & [Province] & " " & [PostalCode] AS
CompanyAddress1pl, tblCompany.Address1, tblCompany.Address2,
tblCompany.City, tblCompany.Province, tblCompany.PostalCode,
tblCompany.ContactName, [ContactName] & " - " & [CompanyName] & " - " &
[Address1] & " " & [Address2] & " " & [City] & ", " & [Province] & " " &
[PostalCode] AS CompanyContact, tblContractCompany.ContractID,
tblContractCompany.IncInContract, tblContractCompany.PrimaryContact
FROM tblContractCompany INNER JOIN tblCompany ON
tblContractCompany.CompanyID = tblCompany.CompanyID
WHERE (((tblContractCompany.ContractID)=[ParamContractID]) AND
((tblContractCompany.PrimaryContact)=True));

Be kind ... I didn't write the app ... I just have to fix it ...

--
Shane Brodie
IT Consultant
c/o MANITOBA FILM & SOUND
333-93 Lombard Avenue
Winnipeg MB R3B 1B3
tel 204-947-2040
fax 204-956-5261
website: http://www.mbfilmsound.mb.ca
e-mail: (e-mail address removed)

 

[Lynn Trapp]

I'm referring to VBA references. Open your VBA Code Window (Ctrl + G).
Select Tools > References. See if any are marked missing. If they are you
need to delete the reference and put it back in.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Done ... first thing I tried ... Linked Table Manager

Thanks

Shane

Check your frontend for any broken references.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Intersting query problem
I have a relaively simple, if long query which operates from within an MS
Access 2000 application where the front-end database contains links to the
backend database residing on a file server. All queries used in the
application are defined on the front-end, all tables are on the backend.

One particular query runs fine if defined and executed from the back-end
database. The same query, when run from the front-end database fails with
the following error:

"Function is not available in query expression'[CompanyName] & Chr(13) &
[Address1] & " " & [Address2] & Chr(13) & [City] & ", " & [Province] &

"

"

&
[PostalCode]'.

Here is the entire query:

PARAMETERS [ParamContractID] Long;
SELECT tblCompany.CompanyName, [CompanyName] & Chr(13) & [Address1] &

"

"

&
[Address2] & Chr(13) & [City] & ", " & [Province] & " " &

[PostalCode]

AS

CompanyNameAddress, [CompanyName] & Chr(13) & [Address1] & Chr(13) &
[Address2] & Chr(13) & [City] & ", " & [Province] & " " &

[PostalCode]

AS

CompanyAddress1pl, tblCompany.Address1, tblCompany.Address2,
tblCompany.City, tblCompany.Province, tblCompany.PostalCode,
tblCompany.ContactName, [ContactName] & " - " & [CompanyName] & " - " &
[Address1] & " " & [Address2] & " " & [City] & ", " & [Province] & "

"

&

[PostalCode] AS CompanyContact, tblContractCompany.ContractID,
tblContractCompany.IncInContract, tblContractCompany.PrimaryContact
FROM tblContractCompany INNER JOIN tblCompany ON
tblContractCompany.CompanyID = tblCompany.CompanyID
WHERE (((tblContractCompany.ContractID)=[ParamContractID]) AND
((tblContractCompany.PrimaryContact)=True));

Be kind ... I didn't write the app ... I just have to fix it ...

--
Shane Brodie
IT Consultant
c/o MANITOBA FILM & SOUND
333-93 Lombard Avenue
Winnipeg MB R3B 1B3
tel 204-947-2040
fax 204-956-5261
website: http://www.mbfilmsound.mb.ca
e-mail: (e-mail address removed)

 

[John Vinson]

"Function is not available in query expression'[CompanyName] & Chr(13) &
[Address1] & " " & [Address2] & Chr(13) & [City] & ", " & [Province] & " " &
[PostalCode]'.

This appears to be the very common References bug. Open any
module in design view, or open the VBA editor by typing
Ctrl-G. Select Tools... References from the menu. One of the
..DLL files required by Access will probably be marked
MISSING. Uncheck it, recheck it, close and open Access.

If none are MISSING, check any reference; close and open
Access; then uncheck it again. This will force Access to
relink the libraries.

Note that if you want a new line, you really need to insert Chr(13) &
Chr(10) - just the <CR> by itself will NOT display correctly on
Windows.