Can't evaluate date in text string for Iif without #Error (1 Viewer)

[J3nny]

Hi,

I'm very stuck. I'm working with a table of financial transactions. I need to know the date a record relating to cash received actually arrived in our bank, but the software doesn't allow the user to enter this, so I've asked them to enter it into a text reference field, e.g. 'Other Text XX 16/04/14', and then I've got a formula - CDate(Right(Trim([RefField]),8)) - which takes the date from this reference.

This works fine, but it's really important I don't miss any of these records due to the date not being entered, so what I'm trying to make is a formula which will give me the date from the ref field as in the formula above if there is one, but if that formula gives an error (so the date has been missed out or incorrectly entered) then I want to take the date from another field, called [DateAdded] as a failsafe.

I can't work out a formula that won't give me an error, i've tried loads. First I tried iif(iserror(CDate(Right(Trim([RefField]),8))),[DateAdded],CDate(Right(Trim([RefField]),8))) but that gives an error, and I think from looking at forums that Access evaluates both parts of an Iif function so it'll do that.

It seems like other people are saying that you need to use Nz, IsDate or IsNumeric or something along those lines instead of IsError to evaluate the field, but I can't get that to work as it's not a field, it's a portion of a text field.

This is my latest attempt:
Join Date: IIf(IsDate(Right(Trim([RefField]),8)),[DateAdded],[Calc Date]), where [Calc Date] is an expression field with CDate(Right(Trim([RefField]),8)) in it, but there's the #Error again.

Help?? How on earth can I use iif to pick either a date from a text string, or where that results in an error then another date field?

 

[jdraw]

Can you give some examples of the text/date that is resulting in error ? Why does it have to be an immediate iif? In reality there should be data validation against each field being entered.

You realize, based on your post, that you are attempting a workaround to overcome a design omission. There should be an "arrival date" in the user dialog.

 

[J3nny]

Hi jdraw

Thanks, I know it's a rubbish workaround but it's because I'm using Access to query a 3rd party's database over an ODBC connection, it's the database of some very expensive financial software which should have a Received_Date field in the first place, which would be saving me a whole lot of time if it did.

I've asked users to enter the date in text at the end of the reference field, but they don't do this for other references so it's not foolproof and no doubt someone will forget to do it at some stage, and I can't have values being missed off my report.

The data looks something like this:

| DateAdded | RefField |
| 10/1/2014 | OtherTextXX 08/01/14 | <-Correctly entered
| 12/1/2014 | OtherTextXX | <-Incorrect, missing text date in RefField

In my query, the CalcDate expression based on CDate(Right(Trim([RefField]),8)) give me 08/01/2014 for the first record, but #Error for the second, and I need some way to return DateAdded (12/1/2014) for the second record in the event of an error in the CDate expression.

Does that help?

 

[pr2-eugin]

So why are you using CDate? Could you not use IsDate?

Join Date: IIF(IsDate(Mid(RefField, InStrRev(RefField, " "))), Mid(RefField, InStrRev(RefField, " "))), "")

 

[J3nny]

Hi Paul,

I tried IsDate, and it fixes the #Error issue, but it doesn't take the date from the text field, i think it looks at it but because it's just a string IsDate returns False and it continues on and takes DateAdded for every record instead.

I've got a horrible workaround now that involves making the first query with CalcDate in it a Make Table query, knowing that the #Errors will be set to null (and I'll have SetWarnings turned off), and then using Iif(IsNull([CalcDate], ... in a query over the created table instead, which is working, but is a bit untidy.

 

[pr2-eugin]

What?? IsDate will take in the text you are passing, check if it is a Date, if so, it will give you the Date in the True part of the immediate If. Else a NullString is returned.

 

[J3nny]

Umm.. Yes you're right, I tried something which didn't work but was obviously doing something stupid, I think I maybe had CDate within the IsDate function as I thought it needed an actual date value. I've put in this:

IIf(IsDate(Right(Trim([RefField),8)),CDate(Right(Trim([RefField]),8)),[DateAdded])

And it's working perfectly. Thank you very much for your help!

 

[pr2-eugin]

I would suggest you use Mid, your code will fail if dates are entered a bit differently. Example 8/1/14 Or 08/01/2014 Or 08/Feb/2014 with a space as the separator and Mid function will fish out the date part. Then again it is upto you.

Glad to help. Good luck !

 

[jdraw]

I think this is the expression Paul gave (syntax adjusted)

Join Date: IIf(IsDate(Mid(reffield, InStrRev(reffield, " "))), Mid(reffield, InStrRev(reffield, " ")), "")

OOps: I see you have had further discussion.
I tried Paul's expression and got a syntax error -- it was just 1 too many ).
I think his expression is correct, and I would replace the "" with the field name [DateAdded]

 

[J3nny]

Thanks jdraw.. And Paul, I think Right() might be the easiest as I haven't shown it in my sample data but there might be (and probably would be) other spaces in the reference, I've asked users to enter dates in DD/MM/YY format so I'm hoping they stick to it, I did think about another formula which would find the first / and would use Mid() based on its position, but this is working for the moment so I'll go back to it when I get a chance and tweek it, thanks very much for your help!