Query field returns error from right table in Left Join

[Seth Schrock]

I have a query the among other things, converts a date/time field (TransactionDateTime) to just a date value using the DateValue() function (TransactionDate). I have another query that is based on the customers table with a left join to the above query (the query is the right "table"). So basically, I get every customer with the transactions that link up. However I'm getting a weird result. Customer ID 4 has no transactions, so all my fields from the query are blank (as expected) except for TransactionDate which returns #Error. If I replace TransactionDate with TransactionDateTime, then I get just a blank value (as I would expect). The DateValue() function is in the first query, so there is no formula in this second query. Why does it return an error instead of a blank value like all the other fields? I am really at a loss on this. I have three other fields coming from the query and they all just return blank values on that record.

[PhilOfWalton]

Just a guess.

Are you in effect trying to calculate DateValue(Null)

If So you might need something like

IIf(Not IsNull(TransactionDateTime), DaleValue(TransactionDateTime))
 

Phil

[Seth Schrock]

I haven't tried the IsNull() function, but I have tried the Nz() which is essentially the same thing and I get the same result.

[PhilOfWalton]

In fact Nz will, without a default value will give a zero 0 or empty space ""

DateValue(0) will give an error

Phil

[Seth Schrock]

However, in this case, Nz() returned an error.

[jforbes]

I think you are really attempting to pass the Null on through the function instead of replacing the Null with a Default value. To do this I think you would need to test for a Null and if found leave it it alone:

IIf(IsNull([TransactionDateTime]),Null,DateValue([TransactionDateTime]))

[Seth Schrock]

I ended up figuring out a method to get rid of those null records and that fixed it.

Also, JForbes, your code did work. I'm still not sure why the error though since the function was being used before the null values would have been in place.

[Seth Schrock]

I found out that this is a known bug. See Allen Browne: Bug Outer Join Expression.