Date/Time Procedural Query Problem with Access

[Denaes]

Someone here on the boards convinced me that it was better to store time (HH:MM) and Dates (MM/DD/YYYY) as Date/Time format in the database; I had been storing them as Strings.

 

Then I came to a point were I need to evaluate ranges of dates and Text wasn't cutting it.

 

So I switched over to Date/Time formats. So far I've gotten the Insert and Update down pat. I can insert Dates and time just fine.

 

Now I have a very simple Query were I want to pull up all records that have a particular date (eventually I'll need to pull up ranges of record dates).

 

I kept getting Procedure mismatch errors using VB.Net, so I put that down and opened up access.

 

This is the SQL Query :

 

PARAMETERS inQueryDate DateTime;
SELECT Schedule.[Date], *
FROM Schedule
WHERE (((Schedule.[Date])="inQueryDate"));

 

and here is the error it brings up:

 

This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.

 

I tried it with 'like' instead of '=', which threw no error, but produced no results.

 

The date I'm trying to pull up is everything on "5/2/2004", the date is set to "Short Date" so it only is MM/DD/YYYY rather than all that extra stuff.

 

I'm sure it's not a hard issue, just something I'm missing/dont' know. I'll keep looking, but I'm dead tired right now (2:50am here!).

 

Edit: Sorry, wrong forum. I ment to post to the Database Categoy, sorry. Could someone please move it over here? :p

Edited May 2, 2004 by Denaes

[Denaes]

I still don't know why that wouldn't work, but I found a kind of ghetto workaround.

 

using the SQL Statement:

 

PARAMETERS inStartDate DateTime, inEndDate DateTime;
SELECT *
FROM Schedule
WHERE (((Schedule.BookingDate) Between "inStartDate" And "inEndDate"));

 

I don't know why Access likes to put so many parenthesis around the WHERE criteria, but it does.

 

The '=' just kept throwing errors.

 

Using the Between criteria and using either two dates (or the same date twice) seems to work.

 

It gets done what I need to currently get done, but I'd like to know what the proper way is to do this (or were I went wrong) if anyone knows :)

[techmanbd]

In writing select staements, don't use double quote"", you should use single quotes - 'instartdate', then you wouldn't need all the parenthesis

[Denaes]

In writing select staements' date=' don't use double quote"", you should use single quotes - 'instartdate', then you wouldn't need all the parenthesis[/quote']

 

Because I'm writing them in Access as stored procedures, Access "Fixes" them after I type them in. Thats how Access wants it written.

 

I wrote all of them by hand in VB.Net (in SQL) and inserted the procedures into the access database, but what I quoted is how Access displayed the SQL back to me when I went looking for the problem. ::shrugs::