Denaes Posted May 2, 2004 Posted May 2, 2004 (edited) 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 Quote
Denaes Posted May 3, 2004 Author Posted May 3, 2004 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 :) Quote
techmanbd Posted May 3, 2004 Posted May 3, 2004 In writing select staements, don't use double quote"", you should use single quotes - 'instartdate', then you wouldn't need all the parenthesis Quote Live as if you were to die tomorrow. Learn as if you were to live forever. Gandhi
Denaes Posted May 3, 2004 Author Posted May 3, 2004 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:: Quote
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.