I'm being an idiot I think...what's wrong here?

[Crazy Fool]

Hi folks - I'm trying to insert a row into an Access table
using the following:

INSERT INTO Files (FileID, FileName, FilePath, FileType,
FileSize, DiskID) VALUES
(SELECT Max(Files.FileID)+1 AS Expr1
FROM Files, 'HOLIDAY.XLT', 'N:\CALC', 'Microsoft Excel
5.0 Worksheet', 499712, 0

This tells me there's a syntax error in the SELECT MAX
part. I've simplified it to this:

INSERT INTO Files (FileID) VALUES
(SELECT Max(Files.FileID)+1 AS Expr1 FROM Files

which gives the same error. But the select statement on
its own works fine:

SELECT Max(Files.FileID)+1 AS Expr1
FROM Files;

I'm sure I am being a complete idiot and missing something
glaringly obvious. Could someone please put me out of my
misery?!

Thanks.

 

[Duane Hookom]

Try removing the ";" from the inner SELECT statement.

 

[John Vinson]

INSERT INTO Files (FileID, FileName, FilePath, FileType,
FileSize, DiskID) VALUES
(SELECT Max(Files.FileID)+1 AS Expr1
FROM Files, 'HOLIDAY.XLT', 'N:\CALC', 'Microsoft Excel
5.0 Worksheet', 499712, 0

If you're using the VALUES operand, you need to enclose the values in
parentheses:

INSERT INTO Files (FileID, FileName, FilePath, FileType,
FileSize, DiskID)
VALUES ( (SELECT Max(Files.FileID)+1 AS Expr1
FROM Files, 'HOLIDAY.XLT', 'N:\CALC', 'Microsoft Excel
5.0 Worksheet', 499712, 0);

I don't know for sure if you can use a Subquery in a VALUES list; an
alternative is to put the hardcoded values right in the SELECT
statement:

INSERT INTO Files (FileID, FileName, FilePath, FileType,
FileSize, DiskID)
(SELECT Max(Files.FileID)+1 AS Expr1, 'HOLIDAY.XLT', 'N:\CALC',
'Microsoft Excel 5.0 Worksheet', 499712, 0
FROM Files

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps