SQL BETWEEN 'DATES' - Programmers Heaven

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

SQL BETWEEN 'DATES'

JamesFosJamesFos Posts: 240Member
I'm using Jet 4.0 and SQL to control an MS Access DB.

I have an sql statement.

SELECT Field1,
Field2,
Field3,
Expr1 AS ExprName1,
Expr2 AS ExprName2
FROM Tabel1,
Tabel2
WHERE Tabel1.PrimeKey = Tabel2.ForeignKey AND
Field2 BETWEEN '14/08/2001' AND '14/09/2001'

When i run it the server brings up an error "Invalid data type in condition statement". When i take out the part..

"Field2 BETWEEN '14/08/2001' AND '14/09/2001'"

.. it works fine and i have to restrict the date in the code (i'm using VB with SQL to access the data base).

does anyone know whats wrong? the code would be a lot simpler if the SQL statement could work.

TIA

Comments

  • WEBMASTERWEBMASTER Posts: 549Member
    Are you using the correct date format?

    Also

    try use

    WHERE Field2=>'14/08/2001' and Field2 <= '14/09/2001'

    Just and idea....



    [blue][italic][b]/WEBMASTER[/b][/italic][/blue]


  • JamesFosJamesFos Posts: 240Member
    I have all my dates set to DD/MM/YYYY and in the queries i use DD/MM/YYYY so what's wrong?

    James

  • WEBMASTERWEBMASTER Posts: 549Member
    did you try to use

    WHERE Field2=>'14/08/2001' and Field2 <= '14/09/2001'



    [blue][italic][b]/WEBMASTER[/b][/italic][/blue]


  • JamesFosJamesFos Posts: 240Member
    : did you try to use
    :
    : WHERE Field2=>'14/08/2001' and Field2 <= '14/09/2001'
    :
    :
    :
    : /WEBMASTER
    :
    :

    Yes.

    I really have no idea.

    Is there a version of SQL which might not accept this? I'm using Jet 4.0.



  • WEBMASTERWEBMASTER Posts: 549Member
    Check the database helpfiles?

    [blue][italic][b]/WEBMASTER[/b][/italic][/blue]


  • WEBMASTERWEBMASTER Posts: 549Member
    Perhaps try reorder the date?

    2001/08/14
    2001-08-14



    [blue][italic][b]/WEBMASTER[/b][/italic][/blue]


  • WEBMASTERWEBMASTER Posts: 549Member
    Perhaps try reorder the date?

    2001/08/14
    2001-08-14



    [blue][italic][b]/WEBMASTER[/b][/italic][/blue]


  • CytCyt Posts: 557Member
    You must use DataValue('YOURDATE') to convert the date to the correct DATATIME format...

    (I've just used 50 minutes searching all of my resources + the internet for that one - and I found it in the help four Visual C++ under "SQL, query criteria")

    The code that I'm working on right now works now :-):

    [code]
    SELECT * FROM Schedules WHERE Start >= DateValue('04/10/2001') AND End <= DateValue('11/10/2001')
    [/code]


Sign In or Register to comment.