Simple SQL query

  • teyjohn
  • Beginner
  • Beginner
  • User avatar
  • Posts: 51
  • Loc: Singapore

Post 3+ Months Ago

Hi guys....

I'm having trouble doing a simple SQL query using Microsoft Access. I am actually trying to make a query to get the data from a table called "abc" where the "abcDate" is from 1/1/2004 until 31/12/2004. The attribute "abcDate" is a Date/Time data type. So, I tried the query below:

Code: [ Select ]
SELECT *
FROM abc
WHERE abcDate
BETWEEN '1/1/2004'
AND '31/12/2004';
  1. SELECT *
  2. FROM abc
  3. WHERE abcDate
  4. BETWEEN '1/1/2004'
  5. AND '31/12/2004';


But... it gives me error saying that "Data Type Mismatch in Criteria Expression". I am sure that this is the correct form of SQL query, but... why the error?
I even tried this query:

Code: [ Select ]
SELECT *
FROM abc
WHERE abcDate >= 1/1/2004
AND abcDate <= 31/12/2004;
  1. SELECT *
  2. FROM abc
  3. WHERE abcDate >= 1/1/2004
  4. AND abcDate <= 31/12/2004;


But.... it provides an empty table.
So.... anyone have any idea? Please help me.... :(

^_^[/list]
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • Managedlinks
  • Proficient
  • Proficient
  • Managedlinks
  • Posts: 294

Post 3+ Months Ago

Hmm not very good at MS SQL I generally trial and error it

change it to...
Code: [ Select ]
SELECT *
FROM abc
WHERE abcDate
BETWEEN #1/1/2004#
AND #31/12/2004#;
  1. SELECT *
  2. FROM abc
  3. WHERE abcDate
  4. BETWEEN #1/1/2004#
  5. AND #31/12/2004#;


I use the query designer as a quick tool to try new things under the views menu you can swap from designer to sql
this generates the right code
  • ATNO/TW
  • Super Moderator
  • Super Moderator
  • User avatar
  • Posts: 23456
  • Loc: Woodbridge VA

Post 3+ Months Ago

Managedlinks wrote:

I use the query designer as a quick tool to try new things under the views menu you can swap from designer to sql
this generates the right code


Interesting. I just learned that same trick today myself. Now I need to go play.
  • Managedlinks
  • Proficient
  • Proficient
  • Managedlinks
  • Posts: 294

Post 3+ Months Ago

Yeah its the lazy way to learn sql, Being a programmer I am of course lazy, otherwise I'd get a real job ;)

Post Information

  • Total Posts in this topic: 4 posts
  • Users browsing this forum: No registered users and 146 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.