Query Problem

[DCFujiwara]

I run the following query in an Access 2003 database (MDB) running under
Windows 2000:

SELECT S.ScheduleID, Format(S.GameDate,"dd-mmm-yyyy") & ": " & [V.TeamName]
& " vs " & [H.TeamName]
AS ScedEntry, S.WeekID, S.GameDate, V.TeamName
FROM
Teams AS V INNER JOIN (Teams AS H INNER JOIN Schedule AS S ON H.TeamID =
S.HomeID)
ON V.TeamID = S.VisitingID
WHERE
(((S.VisitingID)=[V].[TeamID]) AND ((S.HomeID)=[H].[TeamID]))
ORDER BY
S.WeekID, S.GameDate, V.TeamName;

If I copy the MDB file to a PC running Access 2003 under Windows XP SP2.
this query produces and error message indicating that the FORMAT function
cannot be used in the query, yet it runs OK running under Windows 2000.

Help would be appreciated.

 

[Michel Walsh]

Hi,

Does it SPECIFICALLY mentions the Format function? If not, you may have
a problem of reference, and the error then occur in the first external
function call, here Format. Check your project references.



Hoping it may help,
Vanderghast, Access MVP

 

[DCFujiwara]

Hello Michael.

Yes, the error message specifically mentions the Format function.

I have another query with the same type of problem;
SELECT
T.TeamName AS Team, UCase([C.FirstName]) & " " & UCase([C.LastName]) AS
CName, C.Address AS CAddress,
[C.City] & " " & [C.PostalCode] AS CCity, C.EmailAddress AS CEMail,
C.HomePhone AS CHPhone, C.LegionPhone
AS CLPhone, C.FaxPhone AS CFax, [P.LastName] & ", " & [P.FirstName] AS
PName, P.HomePhone AS PHPhone,
P.EmailAddress AS PEMail
FROM
Players AS P INNER JOIN (Players AS C INNER JOIN (Teams AS T INNER JOIN
TeamPlayers AS TP
ON T.TeamID=TP.TeamID) ON C.PlayerID=T.CaptainID) ON
P.PlayerID=TP.PlayerID
WHERE
(((TP.TeamID)=T.TeamID) And ((TP.PlayerID)=P.PlayerID) And
((T.CaptainID)=C.PlayerID) And
((Left([P.LastName],1))<>"*"))
ORDER BY
T.TeamName, [P.LastName] & ", " & [P.FirstName];

Both the UCase function and the Left function are specifically mentioned in
the error message.

Doug.

Hi,

Does it SPECIFICALLY mentions the Format function? If not, you may have
a problem of reference, and the error then occur in the first external
function call, here Format. Check your project references.



Hoping it may help,
Vanderghast, Access MVP

I run the following query in an Access 2003 database (MDB) running under
Windows 2000:

SELECT S.ScheduleID, Format(S.GameDate,"dd-mmm-yyyy") & ": " &
[V.TeamName]
& " vs " & [H.TeamName]
AS ScedEntry, S.WeekID, S.GameDate, V.TeamName
FROM
Teams AS V INNER JOIN (Teams AS H INNER JOIN Schedule AS S ON H.TeamID =
S.HomeID)
ON V.TeamID = S.VisitingID
WHERE
(((S.VisitingID)=[V].[TeamID]) AND ((S.HomeID)=[H].[TeamID]))
ORDER BY
S.WeekID, S.GameDate, V.TeamName;

If I copy the MDB file to a PC running Access 2003 under Windows XP SP2.
this query produces and error message indicating that the FORMAT function
cannot be used in the query, yet it runs OK running under Windows 2000.

Help would be appreciated.

 

[Michel Walsh]

Hi,


If you are within Access, then those seem all related to the same
problem: from the menu, Tools | References... and a list of reference is
displayed, but one that is checked, in its description, begin with the words
MISSING. Uncheck that reference (or find its location, if that reference is
really required). Save and try again.


Hoping it may help,
Vanderghast, Access MVP

Hello Michael.

Yes, the error message specifically mentions the Format function.

I have another query with the same type of problem;
SELECT
T.TeamName AS Team, UCase([C.FirstName]) & " " & UCase([C.LastName]) AS
CName, C.Address AS CAddress,
[C.City] & " " & [C.PostalCode] AS CCity, C.EmailAddress AS CEMail,
C.HomePhone AS CHPhone, C.LegionPhone
AS CLPhone, C.FaxPhone AS CFax, [P.LastName] & ", " & [P.FirstName] AS
PName, P.HomePhone AS PHPhone,
P.EmailAddress AS PEMail
FROM
Players AS P INNER JOIN (Players AS C INNER JOIN (Teams AS T INNER JOIN
TeamPlayers AS TP
ON T.TeamID=TP.TeamID) ON C.PlayerID=T.CaptainID) ON
P.PlayerID=TP.PlayerID
WHERE
(((TP.TeamID)=T.TeamID) And ((TP.PlayerID)=P.PlayerID) And
((T.CaptainID)=C.PlayerID) And
((Left([P.LastName],1))<>"*"))
ORDER BY
T.TeamName, [P.LastName] & ", " & [P.FirstName];

Both the UCase function and the Left function are specifically mentioned
in
the error message.

Doug.

Hi,

Does it SPECIFICALLY mentions the Format function? If not, you may have
a problem of reference, and the error then occur in the first external
function call, here Format. Check your project references.



Hoping it may help,
Vanderghast, Access MVP

I run the following query in an Access 2003 database (MDB) running under
Windows 2000:

SELECT S.ScheduleID, Format(S.GameDate,"dd-mmm-yyyy") & ": " &
[V.TeamName]
& " vs " & [H.TeamName]
AS ScedEntry, S.WeekID, S.GameDate, V.TeamName
FROM
Teams AS V INNER JOIN (Teams AS H INNER JOIN Schedule AS S ON
H.TeamID =
S.HomeID)
ON V.TeamID = S.VisitingID
WHERE
(((S.VisitingID)=[V].[TeamID]) AND ((S.HomeID)=[H].[TeamID]))
ORDER BY
S.WeekID, S.GameDate, V.TeamName;

If I copy the MDB file to a PC running Access 2003 under Windows XP
SP2.
this query produces and error message indicating that the FORMAT function
cannot be used in the query, yet it runs OK running under Windows 2000.

Help would be appreciated.

 

[DCFujiwara]

Michael.

The References option does not appear in the Tools drop-down menu.

But I solved my problem by creating an empty database on the target PC
(running A2003 under Windows XP) and then importing the tables, queries,
forms, and reports from the original database on the source PC (running
A2003 under Windows 2000).

Thanks for your time and efforts.
Doug.

Hi,


If you are within Access, then those seem all related to the same
problem: from the menu, Tools | References... and a list of reference is
displayed, but one that is checked, in its description, begin with the words
MISSING. Uncheck that reference (or find its location, if that reference is
really required). Save and try again.


Hoping it may help,
Vanderghast, Access MVP

Hello Michael.

Yes, the error message specifically mentions the Format function.

I have another query with the same type of problem;
SELECT
T.TeamName AS Team, UCase([C.FirstName]) & " " & UCase([C.LastName]) AS
CName, C.Address AS CAddress,
[C.City] & " " & [C.PostalCode] AS CCity, C.EmailAddress AS CEMail,
C.HomePhone AS CHPhone, C.LegionPhone
AS CLPhone, C.FaxPhone AS CFax, [P.LastName] & ", " & [P.FirstName] AS
PName, P.HomePhone AS PHPhone,
P.EmailAddress AS PEMail
FROM
Players AS P INNER JOIN (Players AS C INNER JOIN (Teams AS T INNER JOIN
TeamPlayers AS TP
ON T.TeamID=TP.TeamID) ON C.PlayerID=T.CaptainID) ON
P.PlayerID=TP.PlayerID
WHERE
(((TP.TeamID)=T.TeamID) And ((TP.PlayerID)=P.PlayerID) And
((T.CaptainID)=C.PlayerID) And
((Left([P.LastName],1))<>"*"))
ORDER BY
T.TeamName, [P.LastName] & ", " & [P.FirstName];

Both the UCase function and the Left function are specifically mentioned
in
the error message.

Doug.

Hi,

Does it SPECIFICALLY mentions the Format function? If not, you may have
a problem of reference, and the error then occur in the first external
function call, here Format. Check your project references.



Hoping it may help,
Vanderghast, Access MVP


I run the following query in an Access 2003 database (MDB) running under
Windows 2000:

SELECT S.ScheduleID, Format(S.GameDate,"dd-mmm-yyyy") & ": " &
[V.TeamName]
& " vs " & [H.TeamName]
AS ScedEntry, S.WeekID, S.GameDate, V.TeamName
FROM
Teams AS V INNER JOIN (Teams AS H INNER JOIN Schedule AS S ON
H.TeamID =
S.HomeID)
ON V.TeamID = S.VisitingID
WHERE
(((S.VisitingID)=[V].[TeamID]) AND ((S.HomeID)=[H].[TeamID]))
ORDER BY
S.WeekID, S.GameDate, V.TeamName;

If I copy the MDB file to a PC running Access 2003 under Windows XP
SP2.
this query produces and error message indicating that the FORMAT function
cannot be used in the query, yet it runs OK running under Windows 2000.

Help would be appreciated.