Access Date Issues?

[bmcguirk]

Is there anyone out there who can address the issue in the following thread on the ANSI SQL board:

http://www.tek-tips.com/gviewthread.cfm/lev2/4/lev3/32/pid/220/qid/67598

Is there an issue with Access 97 dates that I am not addressing with my SQL statement? Any other ideas?

Thanks!

--Brian

 

[ghubbell]

Hi Brian,
That's quite a hassle you're having. If you have Access then you probably have the Northwinds sample database. If you could find it and paste this in to a new query in SQL maybe it would give you some clues:

SELECT [Order Details].ProductID, Orders.OrderID, Orders.OrderDate
FROM Orders RIGHT JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
WHERE (((Orders.OrderDate)>#1/1/1998#));

Although it's not patients and visits, its products and orders and seems to do what I asked it to. Check it out if you have a chance. Gord
ghubbell@total.net

 

[RickSpr]

I notice in your query you have coded "b.VisitDate > 12/31/1998". Wouldn't "12/31/1998" in that context be interpreted as two division operations, yielding a very small number (about .0001937)?

I don't know much about current ANSI standard SQL. My old ANSI SQL reference doesn't even include a date data type. Could it be that there isn't one? How does the visit date come to be stored in the database? Most DBMSs (Access for instance) encode a date as a floating point number giving the number of days since a particular base date, but the base date depends on the DBMS. Whatever DBMS is storing this date, you have to understand what its base date is, as well as the base date for the system you're writing SQL for. And you might have to convert between them in order to avoid comparing apples and oranges. Rick Sprague

 

[bmcguirk]

Rick--

I was thinking along your lines too. The database I described on the ANSI thread is actually just a simple model of a database that I'm working on. The problem is I can't get SQL work on this model with 4 records in it! I don't know if Access let's you do the >< operations on a date/time field and that's why I to moved the conversation to this thread. As far as syntax goes, the SELECT statements they wrote on the ANSI board look good to me and I have done similar operations on other DBMS', but for whatever reason I can't get any of them to work on this model. I haven't tried ghubel's solution yet which looks more like what query generator gives you so I'll adapt that and give it a shot, but otherwise I'm stumped.

 

[bmcguirk]

It works!

Everyone had a hand in the solution. The queries over on the ANSI thread just needed #'s around the date. Thanks to everyone who helped.

--B