February 18th, 2013, 03:29 AM
SQL syntax to display values within certain date range
I am trying to learn SQL at the moment and would appreciate some help with specifying the date using SQL syntax. Currently I have my table in Microsoft Access but I am using the SQL query design field to enter SQL code to run queries.
One of the columns in my table is the OrderDate column, which specifies the date an order was made in the format MM/DD/YYYY. I want to write a query to only display data where the OrderDate is between 1st Jan 2007 and 31st Dec 2007 however I am having issues. I've tried a few ways but none have worked. I am beginning to think whether its related to some formatting of the field in the table which I need to specify. My code is listed below. Thanks for the help in advance.
WHERE OrderDate BETWEEN 01/01/2007 AND 12/31/2007;
Result shown is incorrect and all values other than 2007 is shown. If I put ' ' around the dates ('01/01/2007' AND '12/31/2007'), I get an error 'Data type mismatch in criteria expression.'
WHERE OrderDate >= 01/01/2007 AND OrderDate < 12/31/2007;
Result shown is incorrect again and only values other than 2007 are shown. If I put the ' ' around the dates, same error is encountered as Code 1 above.
February 18th, 2013, 04:41 AM
WHERE OrderDate BETWEEN #2007-01-01# AND #2007-12-31#
February 18th, 2013, 05:00 AM
Thanks. I have tried your advice and the output is now showing everything including the 2007 fields plus the fields outside of year 2007. Unfortunately it did not work since I just want to see the entries for 2007.
February 18th, 2013, 05:52 AM
could you please post a description of your table, along with a couple of rows of data
i'm curious whether you're actually using a DATE column type
February 18th, 2013, 06:14 AM
Thanks for the quick reply. The table contains information about customers who have placed orders. It contains Order number, order date, customer number, employee number, order name, order street, order city, order state, and order zip. The order date is specified as MM/DD/YYYY. One thing i have noticed is that, there is a small plus side before the first column and when i click it for each row the product number and quantity columns are shown. I have pasted some sample values in the next post below.
I am not sure how to specify the date type since I am new to this, but I have downloaded the database example from the below site if you would like to try.
File is located in the zip file and is called the OrderEntry:
I am working on the OrderTbl and trying to extract results based on the OrdDate column.
February 18th, 2013, 06:16 AM
OrdNo OrdDate CustNo EmpNo OrdName OrdStreet OrdCity OrdState OrdZip
O1116324 01/23/2007 C0954327 E8544399 Sheri Gordon 336 Hill St. Littleton CO 80129-5543
O1231231 01/23/2007 C9432910 E9954302 Larry Styles 9825 S. Crest Lane Bellevue WA 98104-2211
O1241518 02/10/2007 C9549302 Todd Hayes 1400 NW 88th Lynnwood WA 98036-2244
O1455122 01/09/2007 C8574932 E9345771 Wally Jones 411 Webber Ave. Seattle WA 98105-1093
O1579999 01/05/2007 C9543029 E8544399 Tom Johnson 1632 Ocean Dr. Des Moines WA 98222-1123
O1615141 01/23/2007 C8654390 E8544399 Candy Kendall 456 Pine St. Seattle WA 98105-3345
February 18th, 2013, 06:43 AM
sorry, man, i can't read zip files
could you take a screenshot of the design view of the table please
February 18th, 2013, 06:52 AM
Actually your method is correct and works. I was confused because i am seeing this last entry from the table which is for year 2013 appearing in the results when i run the query. All the fields for this entry are empty except the order date column but it has the asterisk next to the first column so it is a new row but the date has to be entered i assume as a per-requisite.
Thanks a lot for the help.