The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!
Query not working with number
I'm trying to make a query that will select records
where category = restaurant, restaurant and bar, etc (see below)
AND
24hour = True
I had a previous recordset but instead of 24hour = True best = true: which works fine (SQL below)
SELECT *
FROM entertainment
WHERE category in ('Restaurant' , 'Restaurant and Bar', 'Restaurant and Club', 'Restaurant, Bar and Club') AND best = True
ORDER BY ID DESC
When I change "best = true" to "24hour = true" I get error "Syntax error (missing opperator)" I assume this is becasue "24hour" starts with a number or some other small problem with my syntax. Below is what I have. Does anyone know what I'm din wrong?
SELECT *
FROM entertainment
WHERE category in ('Restaurant' , 'Restaurant and Bar', 'Restaurant and Club', 'Restaurant, Bar and Club') AND 24hour = True
ORDER BY ID DESC
where category = restaurant, restaurant and bar, etc (see below)
AND
24hour = True
I had a previous recordset but instead of 24hour = True best = true: which works fine (SQL below)
SELECT *
FROM entertainment
WHERE category in ('Restaurant' , 'Restaurant and Bar', 'Restaurant and Club', 'Restaurant, Bar and Club') AND best = True
ORDER BY ID DESC
When I change "best = true" to "24hour = true" I get error "Syntax error (missing opperator)" I assume this is becasue "24hour" starts with a number or some other small problem with my syntax. Below is what I have. Does anyone know what I'm din wrong?
SELECT *
FROM entertainment
WHERE category in ('Restaurant' , 'Restaurant and Bar', 'Restaurant and Club', 'Restaurant, Bar and Club') AND 24hour = True
ORDER BY ID DESC
Asked:
2
2
2 Solutions
Rouchie,
Thanks, I'm using an Access database. I fixed the problem by changing the name of the column from "24hour" to "twentyfourhour Any idea why the query won't work when it starts with a number?
Thanks, I'm using an Access database. I fixed the problem by changing the name of the column from "24hour" to "twentyfourhour Any idea why the query won't work when it starts with a number?
Probably because Access automatically reads the start of the word (the numbers) and presumes it's a number, then throws an error. 24hour may even be the name of a built-in time function in access. This is the most common cause of problems.
In SQL you can wrap braces around the column name to force it to read correctly:
SELECT *
FROM entertainment
WHERE
(category in ('Restaurant' , 'Restaurant and Bar', 'Restaurant and Club', 'Restaurant, Bar and Club')) AND
([24hour] = 1)
ORDER BY ID DESC
In SQL you can wrap braces around the column name to force it to read correctly:
SELECT *
FROM entertainment
WHERE
(category in ('Restaurant' , 'Restaurant and Bar', 'Restaurant and Club', 'Restaurant, Bar and Club')) AND
([24hour] = 1)
ORDER BY ID DESC
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
Featured Post
Tackle projects and never again get stuck behind a technical roadblock.
Join Now
It obviously depends on how you designed the tables, but you should always use a bit field for true/false values, in which case your query becomes this:
SELECT *
FROM entertainment
WHERE
(category in ('Restaurant' , 'Restaurant and Bar', 'Restaurant and Club', 'Restaurant, Bar and Club')) AND
(24hour = 1)
ORDER BY ID DESC