Odd Critreia Problem with Update Query

[lfarnsworth]

Hello all,

I've run into an odd problem and I wondered if anyone here might have some input.

I "inherited" a database from a fellow who retired and I'm trying to make some modifications to it. I made a copy of the database, so I can mess around with it without worring about changing the raw data. Now, I'm trying to write an Update Query, so we can easily update some data. I'm using a date/time field as a criteria for the changes.

To test the query, I've litterally copied and pasted a date from the table into the query criteria field. However, when I run the query it tells me it is modifying 0 rows. It's like it can't find anything in the table that matches the criteria, but how can that be possible when I've copied the criteria data directly from the table?

Any thoughts?

 

[Brianwarnock]

I think that we might need to see your SQL as a minimum.

Brian

 

[lfarnsworth]

Here's the mess that is the SQL

[SIZE=3][FONT=Times New Roman]UPDATE Article INNER JOIN (BatchName INNER JOIN (MeasType INNER JOIN (((((Plant INNER JOIN Mach ON Plant.keyPlant = Mach.keyPlant) INNER JOIN Batch ON Mach.keyMach = Batch.keyMach) INNER JOIN Meas ON Batch.keyBatch = Meas.keyBatch) INNER JOIN AllMeas ON Meas.keyMeas = AllMeas.keyMeas) INNER JOIN MeasValue ON AllMeas.keyAllMeas = MeasValue.keyAllMeas) ON MeasType.keyMeasType = MeasValue.keyMeasType) ON BatchName.keyBatchName = Batch.keyBatchName) ON Article.keyArticle = Batch.keyArticle [/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]SET Mach.strMachName = [Machine Name], Plant.strPlantName = [Plant Name], Article.strArticleName = [Item Name][/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]WHERE (((BatchName.strBatchName)<>"Batch Name") AND ((Batch.datCreated)=[Date]));[/SIZE][/FONT]

 

[Brianwarnock]

Wow, you have enough joins going on, are you sure it finds any matches?
I wouldnot use DATE as a field name it is a reserved word, not that I think that is the problem here. sorry the SQL didn't help me.

Brian

 

[lfarnsworth]

Wow, you have enough joins going on, are you sure it finds any matches?

Like I said, I inherited that thing. I can only assume that the guy who designed it knew what he intended. I know that it works for what he designed it to do at least, but just deciphering it has been a nightmare.

Anyway, ugly as it works, I know the joins work. At least, they work when I use the structure for a basic Select query.

I wouldnot use DATE as a field name it is a reserved word, not that I think that is the problem here.

Yeah., I wouldn't have designed it this way. Unfortunately, it's such a tangle I'm afraid that if I change anything, the whole mess will fall appart around me.

Thanks for looking it over anyway.

 

[lfarnsworth]

OK, forgetting about the demended spiderweb of Joins for the moment.

Let's take this query:

SELECT Batch.datCreated
FROM Batch
WHERE (((Batch.datCreated)=#7/3/2008 7:51:0#));

How is possisble that I can I write the above query, copying the date/time criteria value directly from a row in Batch.datCreated and pasting it into the query and yet have the query fail to return the row that has that date/time as a result?

 

[gemma-the-husky]

is this a uk date - typing it directly, it will be treated as july 3rd, not mar 7th

 

[lfarnsworth]

Typing it directly, it would be July 3, 2008, 7:51 AM

 

[Brianwarnock]

This is wierd, there must be something odd going on here, I don't suppose ther is any chance of posting a bit of the DB to have a play with, as long as its not 2007 for me.

Brian

 

[lfarnsworth]

Unfortunately, no. Sensitive data and all that.

 

[Brianwarnock]

OK, It will probably be something simple but its got me beat.

Brian

 

[lfarnsworth]

Is there any way the data I'm copying and pasting from the cell is somehow different from the data that is actually in the cell. To use an example "When is 1 not 1? When 1 is really 0.998."

 

[Brianwarnock]

Good thinking, but that wont be an issue in this case I dont think.
To test if its an issue use datediff to check for a diff of zero between the 2 values, I believe that comparing dates without using date functions can cause problems, I think that there was a thread on this some time back, but using date functions removes that problem.
I think that your best hope will be one of the MVPs picking up the thread.

Brian

 

[lfarnsworth]

I still don't know why this query doesn't work. However, I have discovered that while setting the criteria to = doesn't work, setting at at <, >, <> or Between all work just fine. Furthermore, if I set the criteria as ">#7/3/2008 7:51:0#", the query returns results including 7/3/2008 7:51AM.

No idea why, but it gives me a workaround. Just thought I'd share that.

 

[Rabbie]

Is there any way the data I'm copying and pasting from the cell is somehow different from the data that is actually in the cell. To use an example "When is 1 not 1? When 1 is really 0.998."

This can be your problem. The time part of an Access Date/Time field is stored as a decimal fraction so a value that is displayed as 7/3/2008 7:51:0 may actually be a value between 7/3/2008 7:50:59.5 and 7/3/2008 7:51:0.0499. this would explain why the = comparison did not work and the > one did.

Hope this helps.

 

[lfarnsworth]

That helps. It confirms my theory and explains what's going on. Thanks

 

[Brianwarnock]

This can be your problem. The time part of an Access Date/Time field is stored as a decimal fraction so a value that is displayed as 7/3/2008 7:51:0 may actually be a value between 7/3/2008 7:50:59.5 and 7/3/2008 7:51:0.0499. this would explain why the = comparison did not work and the > one did.

Hope this helps.

I can't help feeling that if this were true we would be running into this situation more often as we all do comparisons on calculated dates. I don't think it is at all possible on entered dates. I am assuming that the date was entered only to minutes here.

But as I have no suggestions I guess I'd better shut up.

Have you got things working now?

Brian

 

[Rabbie]

Brian, Always glad to get your input. I think this is only a potential issue on Times which are set by the Now() function. Dates will always be OK because they are the integer part of the field. It is the fraction part that can cause the problem is there is not an exact match between the contents and what is displayed. I have just done some quick tests and 0.5 displays as 12:00:00 which is correct But 0.5000001 also displays as 12:00:00.

This why I think this may be why the exact match was not working.

 

[Brianwarnock]

Just realised that I must have been a bit dozy on this as it is similar to people doing date tests with time present, my excuse is that I have always worked to seconds, but maybe the old grey matter wants a kick up the backside.

Stll not sure how it failed when he copied and pasted the field into the criteria.

Brian

 

[lfarnsworth]

Have you got things working now?

By doing the criteria as a Between rather than an = I'm working around the problem. It has, of course, opened up a whole other set of problems but fortunately those are just due to the structure of the database and, while annoying, are neither strange nor entirely unexpected.

I'm still curious why copying and pasting didn't work. My best theory, based on the evidence at hand, was that it was some copying the displayed value rather than the actual value.

There may also be peculiarities in the data, because it is being fed into the database from an another piece of software (which may have compatability issues) rather than just being entered directly. Can't go into too much detail on that firstly because there are proprietary issues and secondly because I don't actually know all the details myself.