471,896 Members | 1,169 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,896 software developers and data experts.

Query field returns error from right table in Left Join

Seth Schrock
2,965 Expert 2GB
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
7 1322
1,430 Expert 1GB
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))
Mar 9 '17 #2
Seth Schrock
2,965 Expert 2GB
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
1,430 Expert 1GB
In fact Nz will, without a default value will give a zero 0 or empty space ""

DateValue(0) will give an error

Mar 9 '17 #4
Seth Schrock
2,965 Expert 2GB
However, in this case, Nz() returned an error.
Mar 9 '17 #5
1,107 Expert 1GB
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
2,965 Expert 2GB
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
2,965 Expert 2GB
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.

Similar topics

reply views Thread by Jon Trelfa | last post: by
1 post views Thread by Paul Bramscher | last post: by
2 posts views Thread by Darryl Kerkeslager | last post: by
7 posts views Thread by John Øllgård Jensen | last post: by
2 posts views Thread by eurolinux | last post: by
1 post views Thread by colleen1980 | last post: by
9 posts views Thread by chadlupkes | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.