Formula: Return Null Value while Maintaining Format

[JoeM]

I am doing a DLOOKUP calculation in a query, where if my record is of a certain type (based on a field setting), I want to lookup a date from a different table. If it is not of that type, I want to return a Null value (I do not want to return any date).


I am using DLOOKUP, because if I setup a LEFT JOIN query, my query becomes un-updateable, and it needs to be updateable, since it is the Source of an entry Form.

The issue is, if I try structuring my formula like this:
=IIF([Field1]="Annual",DLOOKUP([DateField1],...),Null)

The value it return is Text (even the Date Values it returns). I need it to return a Date Value, since it is being used in Due Date calculations.
If I replace "Null" with some dummy date, like DateValue("1/1/1900"), then all the fields it returns are date, but I do not want to display this date on my Form, nor should it be used in my calculations.

I suppose a simple workaround would be to use Conditional Formatting and format all 1/1/1900 dates to be the same color as the background so it is essentially "invisible", but I would prefer not to have to do this, if possible.

Is there any easy way to do what I want (without making a complex workaround)?

[ssanfu]

I would try

=IIF([Field1]="Annual",DLOOKUP([DateField1],...),"")

or

=IIF([Field1]="Annual",DLOOKUP([DateField1],...),Empty)

I did not test this, but if the value returned is text, either should work.
(There is not a space between the quotes)

[JoeM]

No, neither of those worked. I think you misunderstood the question.

I do NOT want to return anything in Text format, that is exactly what I am trying to avoid.
I want to return a valid Date value or nothing. Using Null, "", or "nothing at all seems to coerce all returned values to be Text, not Dates.

If you try it, you'll see what I mean. All the non-empty values being returned are left justified, indicating they are returning Text and not a valid Date value.

[ItsMe]

I was thinking "" too.

Maybe you can catch the query in a VBA module and break out the IIF statement there. Analyze it in a string and the pass it to a date variable.

Then you can assign the date variable to the field.

[JoeM]

Maybe you can catch the query in a VBA module and break out the IIF statement there. Analyze it in a string and the pass it to a date variable.

I would consider it that to be a "complex workaround", which I am trying to avoid (since I already have a simple workaround, if need be). I am not even sure how that would work anyhow, since the value needs to be passed back to a query, and once you did that, I think you may run into the exact same issue I am having now.

I was just hoping that there might be a simple solution that eludes me.

[ssanfu]

Oops, I saw the "= IIF...." and thought it was a formula in an unbound control.

OK, so I threw together a test dB.
I used this in the query:

CalcDate: IIf([Field1]="Annual",CDate(DLookUp("[DateField1]","DateLookup","[TestField]=6")),"")

then in the form, for the text box control bound to "CalcDate", set the "Format" property to "Short Date".

What say you???

[JoeM]

Oh wow, that returns all sort of errors on certain records.
I think I see what is going on now. I think there is a complicating factor that might be muddling things up.
The issue is this. In the Date field that the DLOOKUP is return, some of those dates are empty or Null.

So some of the empty/Null values are coming from the DLOOKUP part of the formula, and others are coming from the IIF part of the formula.

I am beginning to think that the two most straightforward workarounds are:
1. Use a dummy date and Conditional Format it to hide it, like I mentioned in my first post
- or -
2. Allow it to return as Text, and just have to use the CDATE function on values that aren't blank at the point where I want/need to use the value in a calculation.

[ItsMe]

It sounds like you got this handled.

I often times use a function that when called checks for 0 or Null or "". It simply returns true or false.

Regardless of how you proceed it seems you will be making a couple of trips to the data. Sorry I could not of been better help.

[JoeM]

Yes, unfortunately I find that most times that when I think I may be overlooking something obvious, I usually am not. Unfortunately, Access does have its limitations that we need to work around sometimes.

Thanks for the suggestions anyway.

[ssanfu]

You might be able to use the IsDate() function in the formula, but it is going to get messy.

A) You might check out ELookup() by Allen Browne http://allenbrowne.com/ser-42.html

B) You could write your own function to do the lookup. (my preference)

2. Allow it to return as Text, and just have to use the CDATE function on values that aren't blank at the point where I want/need to use the value in a calculation.

Nothing wrong with this.... and easier in some ways.

[JoeM]

Steve:

A) Thanks for this link. I did notice the queries performance takes a severe hit when I use DLOOKUP, so maybe this will help.

B) I thought about creating my own function, but an unsure how to create a function that would be any more efficient than using DLOOKUP/ELOOKUP (no sense in recreating the wheel if it is not any better). Do you have any tips on that?

Thanke

[ssanfu]

- Your own function (IMO) would be more efficient than DLOOKUP().

- ELOOKUP() uses recordsets and is how I would approach the problem.
Maybe try this first to see how ELOOKUP() functions. (no pun intended)

- Writing your own function allows you to customize your return value.


I have some queries with (my custom) functions, that call (more custom) functions, and sometimes call another (custom) function. Opening a report that has ~4000 main records that has to do calculations from a table that has several hundred thousand records only takes about 25 seconds. A long time ago, with a much smaller record set, using DLOOKUP() would take upwards of 8 minutes to get the report.

Your mileage may vary