Let me try to describe my situation. I need to perform an update on a record in an Access database and I need to pick which record to update by two criteria, one of which is a Date field (in Access, the actual datatype of the field is Date/Time). Here is what I currently have:
However, whenever I try to execute that code, I get the following exception:
So does anyone know how I can get around this? I need to use that date/time as the criteria for the record I'm after (because contractNum might not be unique but contractNum + requestReceived will be) but I can't seem to get that to work. Any ideas? Thanks, folks. Corey [ April 22, 2004: Message edited by: Corey McGlone ]
Yes, I do. You see Access and its Date/Time datatype is a pain in the arse. I had this identical problem with ODBC and VB 6.0 here is my solution. In the query you need to format the Date/Time field into a String and then you can have it in your WHERE clause, as in Format$(NetWorths.EntryDate,'MM/DD/YYYY') So instead of NetWorths.EntryDate, you will put in your actual table.fieldname. This will format the date/time into just the date as a string So in your WHERE clause put something like WHERE Format$(NetWorths.EntryDate,'MM/DD/YYYY') = '04/22/2004' Good Luck Mark
Eric may not know Java, but his instincts are on-target. With a PreparedStatement you set the data as a Date and don't perform a String translation. Why not let the driver do the work (and do it properly) for you? [ April 22, 2004: Message edited by: Bear Bibeault ]
Originally posted by Bear Bibeault: Eric may not know Java, but his instincts are on-target. With a PreparedStatement you set the data as a Date and don't perform a String translation. Why not let the driver so the work (and do it properly) for you?
Unfortunately the problem isn't with Java, it is with Access and how to do queries against its tables and a Date/Time datatype. Mark
Joined: Dec 20, 2001
Originally posted by Bear Bibeault: I'd also investiage the use of PreparedStatement which will eliminate translation and delimiting issues. I should have pointed this out originally, I suppose, but I had actually already tried that. I was hoping to do just what you were thinking - avoid the issue, entirely. Unfortunately, when I tried that, I got this:
Anyway, I'm going to try Mark's suggestion and see if I can't get past my error. Thanks for the help, everyone. Corey
Joined: Nov 08, 2001
The reason I said it is that I passing a string to my stored proceedure with .NET and a SQL Server 2000 database and it was erroring out saying that the data types were a mis-match so I forced it to be a date on the .NET side and it cleared my error. Man that is one long sentence there...
Joined: Dec 20, 2001
Well, Mark's suggestion got me a little further, but it's still not quite working. I'm not getting the error message now, so it appears that it is successfully comparing the Dates now. Unfortunately, I can't get it to match the record I want in the database to perform the update. The extra crummy part is that now I don't get an error message, but I also can't really see what's going wrong, either - it just says that there were no rows to update by returning 0 from the executeUpdate method. One thing I probably should have explicity pointed out earlier is that the database is storing a Date as well as a Time. For example, an entry in the database might look like this: 2/19/2004 11:36:29 AM I tried taking Mark's suggestion a little further (along with the MS Access Help files) and came up with this line:
Unfortunately, I still can't get it to match the record I want. The worst part about it is that I don't know what the "FORMAT$" function is really doing to my String to make it match the date in Access. Is there any way that I could see the result of that function? Maybe I can do a little VBA script in Access to pop that up in a message box or something... I'll try that out tomorrow when I'm back at work. Anyway, I just thought I'd let you know where I sit right now. If I can't get this figured out soon, I might just turn those Date/Time fields into String fields - I'm just not getting much benefit from using the specialized datatype and it's giving me a big headache. Thanks for the help so far, folks, Corey [ April 22, 2004: Message edited by: Corey McGlone ]
If you intend to use the Time portion in your query, you will find it very very difficult to get a match. I suggest using just the date portion, unless there will be two records for the same ID and date, then you will have to use the Time, but maybe not so much of a precision. The query has to be precise, or you will be off. Even for a second. Mark [ April 22, 2004: Message edited by: Mark Spritzler ]
I read somewhere that dates in access are stored as floating point numbers with the whole number being the date and the fraction being the time. A fraction of .5 would be noon. A property of fractions, based on binary bits, is that they cannot always represent every possible decimal number so rather than matching for equality you should match against an earlier and later date/time to be sure to get your match. I now use integer numbers to represent dates: eg 20040423 for today and that seems to work ok for me. I store time in a separate minute of the day field also as an integer. If your db is already set up you should think about range checking to get your date.
The nice thing about Standards is that there are so many to choose from!