What is wrong with the following SQL statement? It is being used for a query in Access 2003.?
December 27, 2009 - 8:44 am
SELECT [Anabolic Steroids List].[BNF Name]
FROM [Anabolic Steroids List].[Max Dosage]
WHERE Max Dosage = 2;
Anabolic Steroids List = Table Name
BNF Name = Column Heading
Max Dosage = Column Heading
Max Dosage is set as a ‘number’ field and BNF Name is set as a ‘text’ field.
SELECT [Anabolic Steroids List].[BNF Name]
FROM [Anabolic Steroids List]
WHERE [Anabolic Steroids List].[Max Dosage] = 2
Where you went wrong -
You can use spaces in your Table /Column names, but if you do you -have- to surround the name with the square brackets.
The FROM clause references the Table not the Column.
In access, you can’t (shouldn’t) reference Column names without prefixing the Table name.
If you use the Wizard, you won’t get the syntax wrong!
December 27th, 2009 at 1:49 pm
Try rephrasing it as:
SELECT [Anabolic Steroids List].[BNF Name], [Anabolic Steroids List].[Max Dosage]
FROM [Anabolic Steroids List]
WHERE [Anabolic Steroids List].[Max Dosage] = 2;
It is always best to avoid spaces in table and field names. Names like Anabolic_Steroids_List and Max_Dosage are more commonplace. Some DBMSs does not allow spaces, though Access sure does.
References :
Experience
December 27th, 2009 at 2:10 pm
First of all, never use spaces in column headings, as you can see in your query they are encapsulated []
This gives the results you are looking for
SELECT [Anabolic Steroids List].[BNF Name], [Anabolic Steroids List].[Max Dosage]
FROM [Anabolic Steroids List]
WHERE ((([Anabolic Steroids List].[Max Dosage])=2));
References :
December 27th, 2009 at 2:59 pm
try this query:
is the Max Dosage a Integer? or string?
SELECT [BNF Name]
FROM [Anabolic Steroids List]
WHERE [Max Dosage] = 2
remove the " ; " semi colon coz it will affect the query analyzer
References :
December 27th, 2009 at 3:08 pm
SELECT [Anabolic Steroids List].[BNF Name]
FROM [Anabolic Steroids List]
WHERE [Anabolic Steroids List].[Max Dosage] = 2
Where you went wrong -
You can use spaces in your Table /Column names, but if you do you -have- to surround the name with the square brackets.
The FROM clause references the Table not the Column.
In access, you can’t (shouldn’t) reference Column names without prefixing the Table name.
If you use the Wizard, you won’t get the syntax wrong!
References :
Database designer/developer for over 25 years; including MS Access