Solved

Query not working with number

Posted on 2006-11-30
5
151 Views
Last Modified: 2010-05-18
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
0
Comment
Question by:elliottbenzle
  • 2
  • 2
5 Comments
 
LVL 25

Expert Comment

by:Rouchie
ID: 18044011
Not sure which database you're using, but SQL Server doesn't use True/False.  It uses a bit field instead which is 1/0
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
0
 
LVL 4

Author Comment

by:elliottbenzle
ID: 18044813
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?
0
 
LVL 25

Accepted Solution

by:
Rouchie earned 300 total points
ID: 18044846
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
0
 
LVL 11

Assisted Solution

by:dodge20
dodge20 earned 200 total points
ID: 18045771
Actually I believe access uses -1,0 for yes/no fields.

-1 means yes
0 means no
0
 
LVL 11

Expert Comment

by:dodge20
ID: 18045870
My previous comment only applies if you have your acces field set to type yes/no. If it is a text field, you can ignore it.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Adobe Dreamweaver CS5 is a WYSIWYG web page editor that has advanced HTML, CSS, and Javascript rendering functionality and is probably the most well-known HTML editor available. Much of Dreamweaver's appeal centers around the Design View interfac…
This article is very specific and is only intended to help if you are installing Dreamweaver 8 in a Windows 7 environment with Office 2007 installed.   I'm not sure why Microsoft tends to release OS' that should not be released but they do.  Windows…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now