By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,762 Members | 1,838 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,762 IT Pros & Developers. It's quick & easy.

Query field returns error from right table in Left Join

Seth Schrock
Expert 2.5K+
P: 2,941
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.
Mar 9 '17 #1
Share this Question
Share on Google+
7 Replies


PhilOfWalton
Expert 100+
P: 1,430
Just a guess.

Are you in effect trying to calculate DateValue(Null)

If So you might need something like
Expand|Select|Wrap|Line Numbers
  1. IIf(Not IsNull(TransactionDateTime), DaleValue(TransactionDateTime))
  2.  
Phil
Mar 9 '17 #2

Seth Schrock
Expert 2.5K+
P: 2,941
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.
Mar 9 '17 #3

PhilOfWalton
Expert 100+
P: 1,430
In fact Nz will, without a default value will give a zero 0 or empty space ""

DateValue(0) will give an error

Phil
Mar 9 '17 #4

Seth Schrock
Expert 2.5K+
P: 2,941
However, in this case, Nz() returned an error.
Mar 9 '17 #5

jforbes
Expert 100+
P: 1,107
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:
Expand|Select|Wrap|Line Numbers
  1. IIf(IsNull([TransactionDateTime]),Null,DateValue([TransactionDateTime]))
Mar 9 '17 #6

Seth Schrock
Expert 2.5K+
P: 2,941
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.
Mar 10 '17 #7

Seth Schrock
Expert 2.5K+
P: 2,941
I found out that this is a known bug. See Allen Browne: Bug Outer Join Expression.
Apr 17 '17 #8

Post your reply

Sign in to post your reply or Sign up for a free account.