Undefined Function '[Format$]' in expression

G

Guest

Hi,

We had been sent an access program that had been used by them with access
2000. We use Access 2003 and a couple of the queries are generating the
undefined function error. I checked the reference Libraries and there aren't
any there with missing in the name. These are the libraries that are
currently selected:
Visual Basic for Applications, Microsoft Access 11.0 Object Library, IAS
Helper COM Component 1.0 Type Library, and OLE Automation.

Here is the code:

SELECT [T_AES ID & CUSTOMER DETAILS].[A/C No], [T_AES ID & CUSTOMER
DETAILS].[Customer Name], [Traffic File].[Tail Number], [Traffic
File]![Source File] AS [Traffic Month], [Traffic File].[Aircraft ICAO ID],
[Traffic File].[Time On 4], [Traffic File].[Call Start Date], ToDate([Call
Start Date]) AS [Start Date Bill], [Traffic File].[Chargeable Duration],
[Chargeable Duration]/86400 AS [Duration Serial], CVDate(ToSeconds([Traffic
File]![Time On 4])/86400) AS [Start time], Minute([Traffic File]![Chargeable
Duration]/86400) & ":" & [Format$](Second([Traffic File]![Chargeable
Duration]/86400),"00") AS [Duration Bill], [Format$](([Traffic
File]![Chargeable Duration]/86400),"h:nn:ss") AS test, [Traffic
File].Service, [Traffic File].Direction, [T_AES ID & CUSTOMER
DETAILS].[Billing Currency], [T_AES ID & CUSTOMER DETAILS].[Customer Class],
[T_AES ID & CUSTOMER DETAILS].Country, [T_AES ID & CUSTOMER
DETAILS].[Customer's Address 1], [T_AES ID & CUSTOMER DETAILS].[Customer's
Address 2], [T_AES ID & CUSTOMER DETAILS].[Customer's Address 3], [T_AES ID &
CUSTOMER DETAILS].[Town/City], [T_AES ID & CUSTOMER DETAILS].[Post code],
[Traffic File].[Called Number Backup], [Traffic File].[Destination Country],
[T_AES ID & CUSTOMER DETAILS].[Aircraft ICAO ID]
FROM [Traffic File] LEFT JOIN [T_AES ID & CUSTOMER DETAILS] ON [Traffic
File].[Aircraft ICAO ID] = [T_AES ID & CUSTOMER DETAILS].[Aircraft ICAO ID]
WHERE ((([T_AES ID & CUSTOMER DETAILS].[Customer Name]) Is Null) AND
(([Traffic File]![Source File])=[YYYYMM]) AND (([T_AES ID & CUSTOMER
DETAILS].[Not Active]) Is Null));

Please help, your suggestions are very much appreciated.
Thank you,
Rhonda
 
J

John Spencer

Try replacing [Format$] with Format (no brackets, no dollar sign). I'm
surprised that this ran in 2000,

SELECT [T_AES ID & CUSTOMER DETAILS].[A/C No]
, [T_AES ID & CUSTOMER DETAILS].[Customer Name]
, [Traffic File].[Tail Number]
, [Traffic File]![Source File] AS [Traffic Month], [Traffic File].[Aircraft
ICAO ID]
, [Traffic File].[Time On 4], [Traffic File].[Call Start Date]
, ToDate([Call Start Date]) AS [Start Date Bill]
, [Traffic File].[Chargeable Duration]
, [Chargeable Duration]/86400 AS [Duration Serial]
, CVDate(ToSeconds([Traffic File]![Time On 4])/86400) AS [Start time]
, Minute([Traffic File]![Chargeable Duration]/86400) & ":" &
Format(Second([Traffic File]![Chargeable
Duration]/86400),"00") AS [Duration Bill]
, Format(([Traffic File]![Chargeable Duration]/86400),"h:nn:ss") AS test
, [Traffic File].Service, [Traffic File].Direction
, [T_AES ID & CUSTOMER DETAILS].[Billing Currency]
, [T_AES ID & CUSTOMER DETAILS].[Customer Class]
, [T_AES ID & CUSTOMER DETAILS].Country
, [T_AES ID & CUSTOMER DETAILS].[Customer's Address 1]
, [T_AES ID & CUSTOMER DETAILS].[Customer's Address 2]
, [T_AES ID & CUSTOMER DETAILS].[Customer's Address 3]
, [T_AES ID & CUSTOMER DETAILS].[Town/City]
, [T_AES ID & CUSTOMER DETAILS].[Post code]
, [Traffic File].[Called Number Backup], [Traffic File].[Destination
Country]
, [T_AES ID & CUSTOMER DETAILS].[Aircraft ICAO ID]
FROM [Traffic File] LEFT JOIN [T_AES ID & CUSTOMER DETAILS] ON [Traffic
File].[Aircraft ICAO ID] = [T_AES ID & CUSTOMER DETAILS].[Aircraft ICAO ID]
WHERE ((([T_AES ID & CUSTOMER DETAILS].[Customer Name]) Is Null) AND
(([Traffic File]![Source File])=[YYYYMM]) AND
(([T_AES ID & CUSTOMER DETAILS].[Not Active]) Is Null));

Rhonda said:
Hi,

We had been sent an access program that had been used by them with access
2000. We use Access 2003 and a couple of the queries are generating the
undefined function error. I checked the reference Libraries and there
aren't
any there with missing in the name. These are the libraries that are
currently selected:
Visual Basic for Applications, Microsoft Access 11.0 Object Library, IAS
Helper COM Component 1.0 Type Library, and OLE Automation.

Here is the code:

SELECT [T_AES ID & CUSTOMER DETAILS].[A/C No], [T_AES ID & CUSTOMER
DETAILS].[Customer Name], [Traffic File].[Tail Number], [Traffic
File]![Source File] AS [Traffic Month], [Traffic File].[Aircraft ICAO ID],
[Traffic File].[Time On 4], [Traffic File].[Call Start Date], ToDate([Call
Start Date]) AS [Start Date Bill], [Traffic File].[Chargeable Duration],
[Chargeable Duration]/86400 AS [Duration Serial],
CVDate(ToSeconds([Traffic
File]![Time On 4])/86400) AS [Start time], Minute([Traffic
File]![Chargeable
Duration]/86400) & ":" & [Format$](Second([Traffic File]![Chargeable
Duration]/86400),"00") AS [Duration Bill], [Format$](([Traffic
File]![Chargeable Duration]/86400),"h:nn:ss") AS test, [Traffic
File].Service, [Traffic File].Direction, [T_AES ID & CUSTOMER
DETAILS].[Billing Currency], [T_AES ID & CUSTOMER DETAILS].[Customer
Class],
[T_AES ID & CUSTOMER DETAILS].Country, [T_AES ID & CUSTOMER
DETAILS].[Customer's Address 1], [T_AES ID & CUSTOMER DETAILS].[Customer's
Address 2], [T_AES ID & CUSTOMER DETAILS].[Customer's Address 3], [T_AES
ID &
CUSTOMER DETAILS].[Town/City], [T_AES ID & CUSTOMER DETAILS].[Post code],
[Traffic File].[Called Number Backup], [Traffic File].[Destination
Country],
[T_AES ID & CUSTOMER DETAILS].[Aircraft ICAO ID]
FROM [Traffic File] LEFT JOIN [T_AES ID & CUSTOMER DETAILS] ON [Traffic
File].[Aircraft ICAO ID] = [T_AES ID & CUSTOMER DETAILS].[Aircraft ICAO
ID]
WHERE ((([T_AES ID & CUSTOMER DETAILS].[Customer Name]) Is Null) AND
(([Traffic File]![Source File])=[YYYYMM]) AND (([T_AES ID & CUSTOMER
DETAILS].[Not Active]) Is Null));

Please help, your suggestions are very much appreciated.
Thank you,
Rhonda
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Crosstab query (undefined function "format" in expression) 3
macro to find data from one sheet & copy in another sheet 6
Undefined function in expression 4
Undefined function 'Date' in Expression 2
calculations in reports 1
Page Load error when caliing Stored procedure using LINQTOSQL 2
Undefined function 'X' in expression 0
DB insert problems with apostrophe in a Surname and Datetime value 5

Top