Adding 'fixed' values records

[SuperDre]

Is there a way to add a record using SQL (Access, DAO) with 'fixed' values only ? I've searched but couldn't find anything (too much results with not even close to what I want)..

The reason is this, I'm using standard SQL queries to fill a combobox, but the specified table doesn't have a record with the needed selection (and it shouldn't).

So I'm looking into UNION and would figure something like this (which would propably work in MySQL):

SELECT "<No item>", 0
UNION
SELECT MyDescription,MyID FROM MyTable
ORDER BY MyDescription,MyID

Which would result in a recordset like so:
"<No item>",0
"MyItem1", 1
"MyItem2", 2
"MyItem3", 3

But it doesn't seem to work in Access..

Any idea how to do this in an SQL query which works with MSAccess?

[si_the_geek]

A Union is what I would recommend.

If you want more help than that, give us more detail than "it doesn't seem to work".

[brucevde]

Access requires a From clause for all Select statements. In this case specifying any table name will do.

SELECT "<No item>", 0 From MyTable
UNION

[SuperDre]

A Union is what I would recommend.

If you want more help than that, give us more detail than "it doesn't seem to work".

Well, the specified query (with only the MyXXXX replaced with the original field/tablename(s)) gives the following error:

An Error Occured.
Error 3141 : The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

Also the same error occures if I only use SELECT "blah",0;

Hmmm.. seems this does work with JetEngine 3.6, but ofcourse we are using 3.5 and there it doesn't seem to work.. sigh.....

Access requires a From clause for all Select statements. In this case specifying any table name will do.

SELECT "<No item>", 0 From MyTable
UNION

That does work, thanx..... but only if there are records in the Unioned query, otherwise it doesn't.. and ofcourse there are times when the table is empty but it still needs to come up with the "<No item>" record.. double sigh.. it really is a monday

[si_the_geek]

Am I right in thinking that this time "doesn't work" means that it doesn't give an error, but just returns no records?

If so, try using a different table (one that will always have records).

[SuperDre]

Am I right in thinking that this time "doesn't work" means that it doesn't give an error, but just returns no records?

If so, try using a different table (one that will always have records).

Thanx, that's exactly what I thought when I was driving home from work, so I'll try it tomorrow..