occasional #error from datediff function

[ducker]

I've seen this a few times on random PCs running AC97.
A calculation that works properly using multiple datediff functions giving a result of #ERROR even though it does in fact work properly on all other computers.

I'm trying to nail down why this happens on only SOME records, on one or two PCs out of 50+.

My situation: I have a age field calculated from the birthdate field.. When flipping through employees most show the age properly, but some display an #ERROR, even though the birthdate is present. I've attempted to re-enter the birthdate, but this still doesn't fix the problem.

The majority of computers in the office this does not happen, and the age is properly displayed for ALL employees.

Thanks for any help!

-Mike

 

[ducker]

A little more info:

in a query on the same PC.

With the expression:
Left([birthdate],5)

I expect to get a return value of MM/DD.

Yet my values are all over the place on this PC: sometimes I get a 1/1/2, sometimes it spits out a correct result 10/11. It appears as if the PC is dropping the leading zeros.

The problem might not be with the datediff function, but with the way the database is handleing date fields.

The PC is running XP. It previously had Office XP on it, which we uninstalled, and reinstalled Access 97. In doing this we had to rerun, office setup with a /y to properly get AC97 installed.

Is there another DLL, OCX or something that I need to tweek to get this to run properly?

 

[WayneRyan]

ducker,

Try: Left(Format([birthdate], "mm/dd/yyyy"), 5)

Wayne

 

[ducker]

I can try that... but the crux of my problem isn't that particular funciton. It's the fact that my code/function/expressions work for 98.5% of all the computers here. yet there are a couple where I see erratic results!

I'd rather look to fix the PCs then re-write expressions for only 2 PCs.

 

[ducker]

Ok. that worked.

I figured out what the problem is.

For some reason Access is dropping ANY zero's from the dates.
when using:
Format([birthdate], "mm/dd/yyyy")

it forces it to put back in the leading zeros.

For me to go through EVERY reference of any date fields in all my queries,reports,forms. Would be very time consuming. Has anyone seen this problem, and know of an Operating System fix? As I think that's what might be causing this problem.

Agian, it's an XP machine running AC97.

-mike

 

[The_Doc_Man]

With the expression:
Left([birthdate],5)
I expect to get a return value of MM/DD.

Implies a text-mode value of the underlying field. If [birthdate] is actually a DATE field, God alone knows WHAT this think looks like... but it ain't a date any more.

But if it is a TEXT field, the Datexxxx functions don't work so well, either. They expect DATE field inputs. I sense mode confusion.

 

[ducker]

Maybe this is just with over versions of access but I noticed if you simply type mm/dd it will, by default, throw in the current year. Since I'm just looking at a Month/day comparison, I do not care about the year. But on some select PC's this doesn't work...

Again, I think it's simply the fact that for some bizzar reason some machines drop the leading "0" in a date field.

If I look AT the [birthdate] field on the form it will display as
"1/1/2005" on the "bad" PC.

On all the machines that are running this form properly, it displays as
"01/01/2005"



-mike

 

[RV]

Check the Regional Settings for your "erratic" PC's

RV

 

[ducker]

Check the Regional Settings for your "erratic" PC's

RV

Awesome!!! exactly what the problem was.

Under Regional and Language Options
- Customize - Date tab

Format properties of short date.

sure enough it was M/D/yyyy
modified it to mm/dd/yyyy and everything works now! woohoo

thanks!

-Mike