Using an Expr to build another Expr?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using a (this is the 1st Expr) Left expression [Left([SSN RANGE
(CKPT)],4) to pull out the date that I need and then I am using a (this is
the 2nd Expr) DateDiff expression DateDiff("yyyy", [DOB],[Expr1]) to get the
difference. The problem that I am having is that the data is wrong. The
difference is much higher than it should be and I can't figure out why. Is
it that this operation can't be done or I am doing something wrong?

Thanks-in-advance
 
Dagny said:
I am using a (this is the 1st Expr) Left expression [Left([SSN RANGE
(CKPT)],4) to pull out the date that I need and then I am using a
(this is the 2nd Expr) DateDiff expression DateDiff("yyyy",
[DOB],[Expr1]) to get the difference. The problem that I am having
is that the data is wrong. The difference is much higher than it
should be and I can't figure out why. Is it that this operation
can't be done or I am doing something wrong?

Thanks-in-advance
Click to expand...

First explain how a text string that is only four characters long can
represent a date. The last argument in DateDiff expects a date value, not a
string. A string might sometimes work if Access can implicitly make a date
value out of it, but I doubt that will happen with only four characters
supplied.
 
I understand the logic but I have done this many many times and can't figure
out what is going on. I haven't done this exact same configuration but I'm
still having problems doing things that I've done before...doesn't make any
sense....

This is what I'm having problems with now and this i've done before and it
works (suppose to work): Expr1: DateDiff("yyyy",[DOBCON],[SSN DATE (CKPT)])

If anyone can tell me why this isn't working this time, I'll really
appreciate it.

Thanks-in-advance

Rick Brandt said:
Dagny said:
I am using a (this is the 1st Expr) Left expression [Left([SSN RANGE
(CKPT)],4) to pull out the date that I need and then I am using a
(this is the 2nd Expr) DateDiff expression DateDiff("yyyy",
[DOB],[Expr1]) to get the difference. The problem that I am having
is that the data is wrong. The difference is much higher than it
should be and I can't figure out why. Is it that this operation
can't be done or I am doing something wrong?

Thanks-in-advance
Click to expand...

First explain how a text string that is only four characters long can
represent a date. The last argument in DateDiff expects a date value, not a
string. A string might sometimes work if Access can implicitly make a date
value out of it, but I doubt that will happen with only four characters
supplied.
Click to expand...
 
Dagny said:
I understand the logic but I have done this many many times and can't
figure out what is going on. I haven't done this exact same
configuration but I'm still having problems doing things that I've
done before...doesn't make any sense....

This is what I'm having problems with now and this i've done before
and it works (suppose to work): Expr1: DateDiff("yyyy",[DOBCON],[SSN
DATE (CKPT)])

If anyone can tell me why this isn't working this time, I'll really
appreciate it.
Click to expand...

Okay, but it would appear that both [DOBCON] and [SSN DATE (CKPT)] are DATES
which is what the second and third arguments for DateDiff have to be. Your
previously posted expression was using Left([SSN RANGE (CKPT)], 4) for the
third argument and that is clearly NOT a date.
 
Hey just wanted to say thanks for you help. After reviewing and reviewing
and reviewing...my brain finally accepted that I needed the fields to match
(they both need to be strings - like you had previously pointed out).

So thanks Rick.


Rick Brandt said:
Dagny said:
I understand the logic but I have done this many many times and can't
figure out what is going on. I haven't done this exact same
configuration but I'm still having problems doing things that I've
done before...doesn't make any sense....

This is what I'm having problems with now and this i've done before
and it works (suppose to work): Expr1: DateDiff("yyyy",[DOBCON],[SSN
DATE (CKPT)])

If anyone can tell me why this isn't working this time, I'll really
appreciate it.
Click to expand...

Okay, but it would appear that both [DOBCON] and [SSN DATE (CKPT)] are DATES
which is what the second and third arguments for DateDiff have to be. Your
previously posted expression was using Left([SSN RANGE (CKPT)], 4) for the
third argument and that is clearly NOT a date.
Click to expand...
 
Back
Top