What am I missing my my code???????

[Jonathan]

I am trying to format a currency field in a DBW report by editing the SQL in
the wizard to the following:

SELECT *, Format(NetVal,'£#,##0.00) as NetVal1 FROM Vw_NOM_ANAL WHERE
(NominalCode = '1111' AND "Trading Year" = '1111')

But when I run the "verify query" I keep getting the following error
message: -

Server error: Unable to retrieve schema information from the query:

SELECT *, Format(NetVal,'£#,##0.00) as NetVal1 FROM Vw_NOM_ANAL WHERE
(NominalCode = '1111' AND "Trading Year" = '1111')

against a database using the connection string

DRIVER={SQL Server};SERVER=myservername;DATABASE=IT;UID=********

The following error message comes from the database driver software; it may
appear in a different language depending on how the driver is configured.
-------------------------------------------------------
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near
'1111'.

Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147467259 (0x80004005)

Does anyone know what I have done wrong?

Tia


Jonathan

 

[Mark Fitzpatrick]

Jonathan,
What is "Trading Year"? Is it a column? If so then you need to put
brackets around it like [Trading Year].

Hope this helps,
Mark Fitzpatrick
Microsoft MVP - FrontPage

 

[Jonathan]

Hi Mark,

Yes it was the [] that was creating that error message buy now I am getting
a message saying 'Format' is not a recognised function, please see below: -

Server error: Unable to retrieve schema information from the query:

SELECT * FROM Vw_NOM_ANAL WHERE (NominalCode = '1111 "Trading Year" =
'2004') SELECT *, Format(NetVal,'£#,##0.00') as NetVal1 FROM Vw_NOM_ANAL
WHERE (NominalCode = '0797' AND [Trading Year] = '1111'

against a database using the connection string

DRIVER={SQL Server};SERVER=myserver=IT;UID=********

The following error message comes from the database driver software; it may
appear in a different language depending on how the driver is configured.
-------------------------------------------------------
[Microsoft][ODBC SQL Server Driver][SQL Server]'Format' is not a recognized
function name.

Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147467259 (0x80004005)


An ideas?


Jonathan

Jonathan,
What is "Trading Year"? Is it a column? If so then you need to put
brackets around it like [Trading Year].

Hope this helps,
Mark Fitzpatrick
Microsoft MVP - FrontPage

I am trying to format a currency field in a DBW report by editing the SQL
in
the wizard to the following:

SELECT *, Format(NetVal,'£#,##0.00) as NetVal1 FROM Vw_NOM_ANAL WHERE
(NominalCode = '1111' AND "Trading Year" = '1111')

But when I run the "verify query" I keep getting the following error
message: -

Server error: Unable to retrieve schema information from the query:

SELECT *, Format(NetVal,'£#,##0.00) as NetVal1 FROM Vw_NOM_ANAL WHERE
(NominalCode = '1111' AND "Trading Year" = '1111')

against a database using the connection string

DRIVER={SQL Server};SERVER=myservername;DATABASE=IT;UID=********

The following error message comes from the database driver software; it
may
appear in a different language depending on how the driver is configured.
-------------------------------------------------------
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax
near
'1111'.

Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147467259 (0x80004005)

Does anyone know what I have done wrong?

Tia


Jonathan

 

[Jens Peter Karlsen[FP MVP]]

Maybe this can help?

Invoking Built-in User-Defined Functions
Microsoft® SQL Server™ 2000 implements several built-in functions as
user-defined functions that return a table. The invocation of these
built-in user-defined functions follows special rules:

For built-in user-defined functions that return a table, the function
name must be specified with a leading double colon to distinguish
it from user-defined functions that are not built-in. It also must be
specified as a one-part name with no database or owner qualifications.
For example:
SELECT *
FROM ::fn_helpcollations()

For built-in user-defined functions that return a scalar value, the
function name must be specified as a one-part name (do not specify
database or owner). Do not specify a leading double colon .


Regards Jens Peter Karlsen. Microsoft MVP - Frontpage.

-----Original Message-----
From: Jonathan [mailto:[email protected]]
Posted At: 23. marts 2005 10:09
Posted To: microsoft.public.frontpage.client
Conversation: What am I missing my my code???????
Subject: Re: What am I missing my my code???????


Hi Mark,

Yes it was the [] that was creating that error message buy
now I am getting a message saying 'Format' is not a
recognised function, please see below: -

Server error: Unable to retrieve schema information from the query:

SELECT * FROM Vw_NOM_ANAL WHERE (NominalCode = '1111 "Trading Year" =
'2004') SELECT *, Format(NetVal,'£#,##0.00') as NetVal1 FROM
Vw_NOM_ANAL WHERE (NominalCode = '0797' AND [Trading Year] = '1111'

against a database using the connection string

DRIVER={SQL Server};SERVER=myserver=IT;UID=********

The following error message comes from the database driver
software; it may appear in a different language depending on
how the driver is configured.
-------------------------------------------------------
[Microsoft][ODBC SQL Server Driver][SQL Server]'Format' is
not a recognized function name.

Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147467259 (0x80004005)


An ideas?


Jonathan

Jonathan,
What is "Trading Year"? Is it a column? If so then you need to
put brackets around it like [Trading Year].

Hope this helps,
Mark Fitzpatrick
Microsoft MVP - FrontPage

I am trying to format a currency field in a DBW report by editing the
SQL in the wizard to the following:

SELECT *, Format(NetVal,'£#,##0.00) as NetVal1 FROM Vw_NOM_ANAL
WHERE (NominalCode = '1111' AND "Trading Year" = '1111')

But when I run the "verify query" I keep getting the following error
message: -

Server error: Unable to retrieve schema information from the query:

SELECT *, Format(NetVal,'£#,##0.00) as NetVal1 FROM Vw_NOM_ANAL
WHERE (NominalCode = '1111' AND "Trading Year" = '1111')

against a database using the connection string

DRIVER={SQL Server};SERVER=myservername;DATABASE=IT;UID=********

The following error message comes from the database driver software;
it may appear in a different language depending on how the driver is
configured.
-------------------------------------------------------
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect
syntax near '1111'.

Source: Microsoft OLE DB Provider for ODBC Drivers
Number: -2147467259 (0x80004005)

Does anyone know what I have done wrong?

Tia


Jonathan