tdjfdjdj tdjfdjdj - 7 months ago 14
SQL Question

ms access - data vanishes when i remove criteria and query the table

I want to select two records with an id of 1 and 2. Both appear via SQL query if I hard code it in the criteria column. However when I remove criteria and select the same data in SQL using a WHERE clause, only 1 record appears. What happened to the other record? All filters are off.

WORKS:

id

Criteria: 1 or 2


sql:

select * from mytable where mytable.id=1 or mytable.id= 2
//2 records appear. Success.


DOESNT WORK <--but I need to code it this way.

id

No Criteria:


sql:

select * from mytable where mytable.id=1 or mytable.id= 2
//only record 1 appears. Failure.


Why is the 2nd record not appearing?

Answer

The problem here is when you build a query using the query builder as you point out a simple condition such as this works just fine:

1 or 2

So if you type above into query builder, you see this and it does work:

enter image description here

However, if you flip into SQL view, you will see that Access actually writes out that you MUST include the column name.

enter image description here

Eg you need this:

[id] = 1 or [id] = 2

So you have to include the column name. And if you flip the query builder into SQL view mode, you will see access wrote this for you:

So, for such conditions you write by hand, or in code, you need to include the column name. You also I suppose could write this:

[id] in (1,2)