Access 2010 Syntax error (missing operator) in query expression

Question

My query works perfectly on the SQL server, which is where the data is stored. but produces an error when I create the same query in Access 2010

Code

select tbl_NCRLog.NcrNumber,'H:\Quality Control\Nonconformance Reports'  

    + cast (tbl_NCRLog.NcrNumber as Char (15)) as FileLocation

from tbl_NCRLog

The table "tbl_NCRLog" is a linked table in my Access database.

Can anybody help?  I'm stumped.

Thanks

Answer 1

Since the table is in SQL Server, you can use a pass-through query and maintain your existing SQL syntax.

Answer 2

Hi John;

What I'm trying to produce is a path

EG

H:\Quality Control\Nonconformance Reports\4

OR

H:\Quality Control\Nonconformance Reports\5

The number at the end of the path is a variable and is the unique record number of the Nonconformance Report you are viewing in the main form of the Access database.

I'd like to turn that into a hyperlink so that when users click on it the hyperlink will open up the network location that contains the various files related to that Nonconformance report.

The Char (15) is just a starting point.  If I want the hyperlink to work properly I'll probably have to trim off the trailing spaces.

I can produce the desired path by creating an SQL View and then linking that into my Access database as a linked table.  Still having problems with the hyperlink though.

Suggestions?

Thanks

Answer 3

Try CStr() instead of Cast() - as Mimmo says it's not an Access function. What is the datatype of NcrNumber and what output do you want? Is the (15) intended to get trailing blanks up to 15 characters, or a maximum size, or what?

Answer 4

I don't know Sql server. What's cast ?  It's not an Access function. Perhaps you want format the field ?

Answer 5

Hi,

try with & instead +

select tbl_NCRLog.NcrNumber,'H:\Quality Control\Nonconformance Reports'  

   & cast (tbl_NCRLog.NcrNumber as Char (15)) as FileLocation

from tbl_NCRLog

Mimmo