[RESOLVED] WHERE field = a date?

[drag0n_45]

Hey again,
I'm having trouble looking up date values in my database.
When I say

...WHERE Shipments.ShipmentDate=#07/16/2009#...

No results are returned even though there was a shipment created today.

If I say

...Shipments.ShipmentDate BETWEEN #07/10/2009# AND #07/16/2009#

I retrieve all results non-inclusive of 7/16/2009.

I believe this is because the Date/Time field in the database also stores the time. Since the time is stored as the fractional part and assumed to be zero when no fractional part is specified, saying 7/16/2009 is actually the equivalent of saying 7/16/2009 12:00AM.

That makes sense and explains the non-inclusive factor of the between statement as well as why I can't pull up the correct date using =.

I suppose what I need to do is find a way to ignore the timestamp for comparisons (or don't use a timestamp when I save to the DB - something I'd rather not do).

Anyone know of a way to do this?

[techgnome]

I'm going to guess that the use of # means you are using Access, aren't you?

Try this:

WHERE CDATE(Shipments.ShipmentDate)=#07/16/2009#

CDATE(Shipments.ShipmentDate) BETWEEN #07/10/2009# AND #07/16/2009#

I think that will work... CDate should strip off the time portion and return jsut the date, defaulting it to 12:00:00am....

IF... it doesn't work.. you can try this:

WHERE CDATE(Format("mm/dd/yyyy", Shipments.ShipmentDate))=#07/16/2009#

CDATE(Format("mm/dd/yyyy", Shipments.ShipmentDate)) BETWEEN #07/10/2009# AND #07/16/2009#

(I hope I got the parameters in the right order for Format() ... if not, swap them around).

-tg

[si_the_geek]

CDate wont help, as that includes the time too, but you can use Int - and that would be safer than the Format/CDate combination, which could have problems due to Regional Settings.

[drag0n_45]

I keep forgetting to notate what DB Engine I'm using. Arg!

Int could do it. I began to wonder how that'd actually solve the problem until I remembered how dates are stored again...

Let me try your suggestions and I'll report back. thanks a lot!

EDIT: Is there a right way to do this not using VBA functions? For instance, if this were an SQL database or something? I know that eventually all programs will be running from an SQL server.

[si_the_geek]

Unfortunately dates are one of the things that are very specific to each database system, there isn't really a generic way to deal with them.

The closest you can do for this kind of thing is use Between, with the second part using 1 second before midnight, so for a single date:

 BETWEEN #07/16/2009# AND #07/16/2009 23:59:59#

..and for a range:

 BETWEEN #07/10/2009# AND #07/16/2009 23:59:59#

That still leaves putting the date values into the SQL statement (which is specific to each DBMS), but if you use parameters then that issue is eliminated too.

[drag0n_45]

Well of course that sparks an interest of parameters.

If I have interpreted my mistyped SQL queries' result in Access's datasheet view correctly, then a parameter is simply a field that doesn't exist in the database, but retains a value that is set somehow?

I get parameters in general, just not use, implementation, etc with then in db's...

[si_the_geek]

There are a few things that go by the name of parameters, what I was referring to was the method of putting values into the SQL statement in your VB code - as far as the database is concerned you aren't doing anything differently.

There is an explanation of the benefits of using them (and links to code examples) in the FAQ article Why should I use Parameters instead of putting values into my SQL string?

[drag0n_45]

Double post.

Int generated an overflow in the DBEngine and CDate doesn't keep track of the time. I suppose there's just the midnight idea at this point. That's something I had initially considered but thought might be a little "code tacky" if that makes sense.

[si_the_geek]

It isn't ideal, as it takes two DateAdd's (or a const of #23:59:59# which you add to the date), but covers the bases - except for the potential possibility that there is data with fractional seconds (eg: #.. 23:59:59.44#).

Double post.

Oops, deleted!

[drag0n_45]

After reading the bit about parameterized queries, I feel that it's a great idea and something I should implement. However, I found nothing in there about how to do that with the .Filter property.

I chose to use .Filter for all string values in order to speed up the result retrieval since I'm using real-time filtering (which can be disabled via a checkbox). Since Access connections take forever (compared to say, SQL) to connect, I didn't want to open the connection and close it ever time the user changed a textual search parameter (typing 'new york' would require 8 connections to the DB in that case.

On the upside, databases can't be hacked using .Filter I don't think. Since direct SQL can't be injected into the DB with that property, there's not much in the way of vulnerabilities. I am curious what the special characters are though.

Looks like I'll have to go with explicitly stating the time, although I'm still unsure how to make that work with the equals side of things....

[si_the_geek]

I very rarely use .Filter, but I'm pretty sure that it is purely ADO, and the very limited version of SQL it uses is entirely proprietary (but similar to Jet [Access] SQL). I doubt that injection (if possible) could do much - at worst it would alter the data in the recordset, which could then potentially be reflected in the database.


Note that there is no need to keep re-opening and closing a connection, you can leave it open for quite a while, but need to bear in mind that the DB might close it after a few minutes, and Access is more likely to have problems (so perhaps use a Timer to close it after a certain time).

Alternatively you could just store a copy of the data (to an array etc), and manipulate that from code.

Like with many things tho, there is no "right" answer for all situations.

[drag0n_45]

.Filter only allows boolean comparisons with the exception of like. No SQL execution can be made, only strict filtering (which is why I needed to put the date fields into the actual SQL statement - BETWEEN isn't supported in filter).

Personally, I like using disconnected recordsets for a couple of reasons. First and foremost, I'm always guarenteed that a connection to the DB is there since I always re-open it (great for network environments).

I'm still faced with the problem of trying to set a date equal to a dtPicker date, which fails because of the time in the DB. When I tried

"CInt(ShipmentDate)=" & cint(dtPicker.value)

I got an overflow message. Trying

"CInt(ShipmentDate)=#" & format(dtPicker.value,"Short Date") & "#"

also overflowed.

[si_the_geek]

For .Filter can use Like against dates? If so you can specify the date followed by a wildcard. I have no idea what date format would be needed, or if it would be Regional Settings dependent.

The overflow is because you are using CInt (convert to the Integer data type, with bankers rounding) rather than Int (return the pre-decimal part of the number, rounding down).

[drag0n_45]

I don't know why i didn't think of this before... but here's what i did for make equals work...

vl_SQL_str = vl_SQL_str & " BETWEEN #" & Format(dtDateStart.Value, "yyyy/mm/dd") & "# AND #" & Format(dtDateStart.Value, "yyyy/mm/dd") & " 11:59:59 PM #"

it's a between, but for the whole day.

[si_the_geek]

That's part of what I suggested (but apparently didn't make clear) back in post #5

[drag0n_45]

LOL You wrote the code, I think it was pretty clear - I just think I was speed reading.

[dee-u]

I know this is resolved but I just want to share this workaround which I had suggested to my I.T. Manager many years ago when he was asking me why his "between" is not working, I had used DateValue for this, something like

WHERE DateValue(Shipments.ShipmentDate)=#" & DateValue("07/16/2009") & "#"

WHERE DateValue(Shipments.ShipmentDate BETWEEN #"  & DateValue("07/10/2009") & "# AND #"  & DateValue("07/16/2009") & "#"

[si_the_geek]

That's an interesting method, but is specific to Access (when being used from Classic VB), which drag0n_45 wants to avoid.

[dee-u]

That's an interesting method, but is specific to Access (when being used from Classic VB), which drag0n_45 wants to avoid.

Yes, unfortunately it is for Access only.

[drag0n_45]

Looks like that can be put in the filter method though, which makes sorting the records quicker. If I understand correctly, .Filter is quicker in most cases than re-querying the recordset.
The only reason I'd like to avoid Access-specific commands is because I don't want to get in the habit of using them once I make the transition to SQL server. At this point I'm just re-writing all of my company's software to use the same database format, but just be more user-friendly (not using data controls, for instance). I figure the less I have to re-code when it comes time to switch to SQL Server, and the more standardized I my code becomes now, the better.