SQL Between statement 1

[Polariz666]

Hi all.

I found a problem with my code and realised that the SQL statement...

rs.Open "SELECT Name, S, Time, Tid FROM TstArchive WHERE [Time] BETWEEN '" & passDateToQueryField.Text & "%' AND '" & passDateToQueryField2.Text & "%' ORDER BY Name ASC", db, adLockOptimistic

...does not return/include the data for the last date in the range. I read somewhere that some databases exhibit this behaviour and others choose to include the data. Is there any way around this without adding a day onto the final date?

Much appreciate guys.

Mike.

 

[Polariz666]

For reference, here is where I found the previous problem out:

http://www.w3schools.com/sql/sql_between.asp

Cheers guys,

M.

 

[gmmastros]

You don't say what type of database you have. I know that in SQL Server and Access, date AND time are stored in the same field. When you don't specify the time, then midnight is assumed. One thing you can do is to 'hard code' the times. Like this....

rs.Open "SELECT Name, S, Time, Tid FROM TstArchive WHERE [Time] BETWEEN '" & passDateToQueryField.Text & " 12:00:00 AM' AND '" & passDateToQueryField2.Text & " 11:59:59 PM' ORDER BY Name ASC", db, adLockOptimistic

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Polariz666]

Sorry about that, I am using VB6 with an access database. I'll give that a try cheers.

 

[gmmastros]

In Access, Date/Time fields are delimited with the pound symbol.

rs.Open "SELECT Name, S, Time, Tid FROM TstArchive WHERE [Time] BETWEEN #" & passDateToQueryField.Text & " 12:00:00 AM# AND #" & passDateToQueryField2.Text & " 11:59:59 PM# ORDER BY Name ASC", db, adLockOptimistic

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Polariz666]

Right i've discovered a problem hehe. This isn't my code so after further investigation the variable that passDateToQueryField.Text is attached to is actually not a date - it's a String. The reason for this is the database has dates stored in the format 20041201170123. This means that it's 1st December, 2004 17:51:23pm. Hence why in the query, the first section of 20041201 uses a % to indicate any text after it (Ignoring time) can be used.

The ways I could possibly see doing it is to either convert the both text's into dates by rearranging the numbers to the correct format and then doing it your way George; or by just by converting the second into a date, adding on a day, then reconverting it back to the db format as a new string and do it the original way I was doing it.

Any other suggestions at all? Does what i'm thinking make sense?

Cheers.

 

[gmmastros]

First and formost... Whenever you are allowed to make such decisions, ALWAYS store the data using the datatype that is most appropriate. If storing a date & time, then use the DateTime data type.

That being said, I am aware that frequently (as developers), we inherit someone else code and have to support it. That's where hack's and kludge's come from.

I do have a suggestion for you, though. You can still hardcode the time, you just need to use the format that your data is in. If I understand correctly, the first 8 characters are the date, and the last 6 characters are the time. Just make your between statement look like... Between 20041201000000 And 20041201235959

rs.Open "SELECT Name, S, Time, Tid FROM TstArchive WHERE [Time] BETWEEN '" & Left(passDateToQueryField.Text, 8) & "000000'  AND '" & Left(passDateToQueryField2.Text, 8) & "235959' ORDER BY Name ASC", db, adLockOptimistic

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Polariz666]

Excellent mate worked a treat, thanks alot.

I hear you about inheriting other people's code; I wanted a quick fix to the few problems this program had and it's made more difficult by not knowing the code at all when it's not your own. The database itself is actually generated and updated by yet another completely different program written by someone else - just to add more confusion. The date fields themselves in the database upon further investigation are also text rather than date so the problem stems from there.

Thanks again.

Mike.