Select data between two dates
 

I am trying to select records from an Access database that has a date field formatted dd/mm/yyyy format and are between two dates defined in Dim parameters.
The code I have tried is this:
When I execute this script I get the following error:
"ADODB.Recordset error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

/performdates.asp, line 12"
Line 12 = objRs4.Open sql4, Conn

Your help is really appreciated.

First of all, the error is dead obvious:
No place prior to that objRs4.Open line have you ever defined the variable Conn.

***********

But now we come to your next problem: Your query won't work.

You say your date is in the format dd/mm/yyyy.

Okay, consider these two dates, 60 days apart:
And now let's assume that the value in your database is
There is NO POSSIBLE WAY that '03' can be between '20' and '19'.

When you store dates AS STRINGS, the ONLY way you can then compare them is if you store them as yyyy/mm/dd.

Because STRINGS are compared ONE CHARACTER AT A TIME. And the first mismatch trigger the < or > result. In this case, the '0' of '03' is less than both the '2' or '20' and the '1' of '19' and so is NOT between them.

There MAY be hope for you:

What is the DATA TYPE of the field Cncrt_dateasp in your table?

Use Access (the program, that is) to look at your table and examine that table deifinition and tell us the data type.

Revised code
 

Hi Old Pendant,
You helped me solve part of the problem.
The date format was/is the issue as you correctly observed.

Revised code:
If I remove the ' from in front of the Today and Future with the dates in yyyy-mm-dd format it works fine. That is the format of the Cncrt_dateseq data in the database.
Now I need to know how to get the (Date()) and (DateAdd("d",30,Today)) to be in the correct format.

Any ideas?

Thank you so much for your help.

That makes no sense.

If you do that, they you will be doing something likeand the SQL engine will see that as *SUBTRACTION*!! That is, it will see it as
2012
minus 09 (==>>1993)
minus 19 (==>>1974)

At least with the '...' around those values I could see it working.

Are you *SURE* the values in the database look like '2012-10-03' and not 20121003 (that is, numbers)??

Take a screen shot of an Access display of the table data and show it.

Thanks again for your help Old Pedant.
The database values for Cncrt_dateseq are as shown. They are a text field. There is also a field Cncrt_dateasp that is in a dd/mm/yyyy format. I tried using that but get extra events in the result, see it here, http://www.wayneoquin.com/performdates.asp

Here is screen shot of database table.
http://wayneoquin.com/img/concert_datesc1.jpg

Okay... It's too bad those are text fields, but luckily Access (JET) has an answer for that.

We can use *EITHER* the CNCRT_DATE or the CNCRT_DATESEQ field. Doesn't matter. Either will work. (But not the cncrt_dateasp field.)
Try that! In Access, before you try using it in your ASP code.

Notice that you NO LONGER need to pass in Today and Future from the ASP code. Let Access do the date arithmetic for you!

If that works (and I'm 90% sure it will), then you can just convert it to ASP thus:

If it's not obvious, CDATE() in Access works the same as it does in VBScript: IT takes the TEXT form of a date (or date + time) and converts it into the internal DATETIME format used by Access (and by VBScript). So in this case, it converts the text in the DB field and converts it to a DATETIME value.

Hi again Old Pedant,
Your are a genius!
It works with Cncrt_Date. It does not work with Cncrt_Dateseq.
One last request.
I want to "highlight" the performance on the day of the performance would the code be like this:
I have tried both Cncrt_Date and Cncrt_Dateseq fields but neither work. I have also tried it with and without the brackets around the DATE.
Any suggestions?

No. Not at all. WHY would you use LEN( )??? That just gets the length of the string. It would *NEVER* equal a DATE() value!

p.s.: Although you *can* use code such as objRS4.Fields.Item("Cncrt_Date").Value as the incredibly idiotic DreamWeaver code does, there is no reason to do so!

Simply doing objRS4("Cncrt_Date") produces the IDENTICAL result *AND* executes roughly 3 times faster!

Oh Dear! Asleep at the keyboard again :(. That's what happens when you blindly copy working code from section to another without reviewing the code. ARRGH!!!
I tried your code above and it throws this error, Microsoft VBScript runtime error '800a000d'

Type mismatch: 'CDATE'

/performdates.asp, line 43
Line 43 reads: Any ideas?

Well, it *SHOULD* work if all your values in that column match what you showed in that screen shot.

Example code:
But if any of your values there are not legal dates in the form YYYY-MM-DD then of course you could get that error.

So maybe do this:
Now you will get the INVALID DATE message and it will show you which of the values from your DB has got a problem.

AHHH!!! I *MISSED* this comment in one of your prior posts:
That clearly indicates that one of the values in the CNCRT_DATESEQ indeed has a problem! If Access couldn't use that column, then neither could VBScript.

So just change to using that column in the IF test:

Ha! that fixed the problem. It works fine now. Thank you for all your help. Again, you bailed me out.

Hi! Query to get records between two particular date like 07/08/2012 and 10/08/2012 then use below query:



SELECT * FROM #temp WHERE CONVERT(VARCHAR(10),CrDate,103) BETWEEN CONVERT(VARCHAR(10),'07/08/2012',103) AND CONVERT(VARCHAR(10),'10/08/2012',103)

OR


Syntax Query to get data between any two dates then you use below query :


SELECT Column1,Column2,Column3 FROM TABLENAME WHERE Column3 BETWEEN GetDate()-1 AND GetDate()

WRONG ANSWER

Those queries are for SQL SERVER ONLY.

This thread was about JET OLEDB queries. That is, queries into an ACCESS database.

Neither the Access nor JET drivers have CONVERT() or GETDATE() functions.

Those queries aren't even very good if you are using SQL Server.

JUST FOR EXAMPLE:GetDate() returns the current date *AND TIME*.

So let's say you make that query at 17:00:00 (5 PM) today.

Essentilly, you are asking for
So, for example, you won't find records from 11 AM yesterday.

WORSE: If column3 is a DATE-ONLY column, then you wont' find ANY records from yesterday.

I think you might want to go revisit your SQL Server documentation to find better ways than what you are doing there.