Can you DIM an expression in a query? 1

[ruthcali]

i am using Access 97 in a Novell environment.

i have a form based on a query. That query contains a table with a Memo field called Remarks. The first entry in that Remarks memo field is always in the form of a date (ex: Jun 30 2000, Work was completed on the project ...)

So it's a memo field that kind of contains a 'date'. (but Access doesn't view Jun 30,2000 as a date. that is my problem).

i need to extract that date. So in my query, i have a column: expr1: Left([Remarks],11)

i also have another column to compare it to the current date: expr2: now()-expr1

but since Access doesn't view the value in expr1 as a date, i get #error as the value for expr2.

what i need is all the records where now()-expr1 > 14.

it seems like somewhere in my query, i have to type:
Dim expr1 as date

Does anyone have any ideas?
thanks!

 

[THoey]

how about trying the following:

now()- CDATE(expr1) > 14

Hope that helps...


Terry M. Hoey
th3856@txmail.sbc.com

Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?

 

[ruthcali]

OH MY GOODNESS!! IT WORKS.

i have been trying to do this since yesterday. and your one comment has just solved all my problems!!!

CDATE.

i don't think i ever would have found that out.

thank you so much for your help!

 

[ruthcali]

Oh, i spoke too soon. It works, but when i type in >14 in the query for the criteria for Expr2, and then hit run, i get a pop up window 'enter parameter value, Expr1'.

When i remove the >14, the query works.

this is what i have in my query:
Expr1: Left([Remarks],11)

Expr2: Now()-CDate([Expr1]), then in the criteria, i have >14.

Help. i feel like i am sooo close.

 

[THoey]

Try this:

Expr1: Left([Remarks],11)

Expr2: Now()-CDate(Left([Remarks],11)),

then in the criteria, have > 14.

Let me know what happens... (Guessing)


Terry M. Hoey
th3856@txmail.sbc.com

Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?

 

[Shep]

Combination of things
1) adding / subtracting of dates should be done using DateDiff / DateAdd
2) don't force Access to evaluate the first expression to calculate the required values - do it in one go...

DaysElapsed: DateDiff("d",CDate(Left([Remarks],11)),Now())

HTH

Shep

 

[THoey]

Thanks Shep. I was kinda rushing through that and didn't think it all the way through. In Oracle, we really don't have to worry about those DateDiff type things.

RuthCali, Listen to what Shep said...


Terry M. Hoey
th3856@txmail.sbc.com

Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?

 

[ruthcali]

Help! It's not working. i keep getting 'data type mismatch in criteria expression'.

i have
DaysElapsed: DateDiff("d",CDate(Left([Remarks],11)),Now())
Then in the criteria section, i have >14.

when i remove the >14, the query runs.
when i type the >14, i get the data type mismatch error.

help! i am going to cry. :-(

 

[Durkin]

I've managed to recreate your problem. When I run my query without any criteria in the DaysElapsed field it runs fine and when I put something in it doesn't. The reason mine is doing that, at least, is that I purposely put a shorter date format in one of my records so that the first 11 characters do not convert to a date. When you run the query without any criteria if you scroll down through the results you'll see that some of the fields have #Error in them. It's only when Jet is trying to evaluate whether this value is greater than 14 or not that the Data type error occurs. You should check if all the records in your table have the first 11 characters in such a way that they can be evaluated as dates. Hope this finally solves it for you. ;-) Durkin
alandurkin@bigpond.com

 

[Battista]

Try CDate(Left([YourField],11))

 

[ruthcali]

Durkin!!! You were absolutely right!!! You helped me solve my problem and you won't believe how easily it was solved.

all i had to do was move that crazy DaysElapsed column to the end of the QE grid!!!! can you believe it. that's all it took and i have spend about 5 hours trying to figure out what was wrong.

Your comment made sense. just one wrong value could mess up the critieria. so i started looking. i found some values that were #error because those Memo Remarks fields didn't contain dates. but i had filtered those away, that's why it was confusing. but the ORDER IS IMPORTANT.

see, in my query, i had other criteria. for example, i had city="wdc" and status="open". Using those two criteria, i filtered away all the #error values. But i had the DaysElapsed column BEFORE those two criteria above.

so, FIRST Access was trying to find values >14 and it ran into errors, because my two other filters (city and status) were after that.

but, if i took away the >14 criteria, i got a table with no #error values because Access used the two filters (city and status).

when i moved my daysElapsed column after the city and status columns, everything worked.

thinking back, it makes sense. it all boils down to order of operations.

So, thank you Durkin!!
And, Terry, thank you for your CDATE.
And Shep, thanks for the DateDiff and the combined expression hint.
Without you guys, i would still be crying and lost! All of you are the best and i thank you for your help!!

 

[ruthcali]

Here's an even better solution where the placement of the columns don't matter: using the ISDATE function.

DaysElapsed: IIf(IsDate(Left([remarks],11)),CDbl(DateDiff("d",CDate(Left([Remarks],11)),Now())),0)

So this function checks for a date field, if it doesn't find one, instead of #error, i get a 0.

So when Access evalutes the >14 criteria, it doesn't run into Type Mismatch because all the values are valid.