473,554 Members | 2,839 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Query field returns error from right table in Left Join

Seth Schrock
2,965 Recognized Expert Specialist
I have a query the among other things, converts a date/time field (TransactionDat eTime) to just a date value using the DateValue() function (TransactionDat e). 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 TransactionDate Time, 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 1447
PhilOfWalton
1,430 Recognized Expert Top Contributor
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
2,965 Recognized Expert Specialist
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
1,430 Recognized Expert Top Contributor
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
2,965 Recognized Expert Specialist
However, in this case, Nz() returned an error.
Mar 9 '17 #5
jforbes
1,107 Recognized Expert Top Contributor
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 Recognized Expert Specialist
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 Recognized Expert Specialist
I found out that this is a known bug. See Allen Browne: Bug Outer Join Expression.
Apr 17 '17 #8

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

Similar topics

0
1165
by: Jon Trelfa | last post by:
I'm having difficulties getting all rows to return from the left table on a left join. For some reason, i only get the rows where there is a match between the right and the left table. Here's what I have... the tables: student ------- studentId (int) lastname (varchar)
1
3433
by: Paul Bramscher | last post by:
Here's one for pathological SQL programmers. I've got a table of things called elements. They're components, sort of like amino acids, which come together to form complex web pages -- as nodes in trees which form parent-child relationships, sort of like newsgroups. For example, the parent_id field points to another element. Indent_level...
2
3953
by: Darryl Kerkeslager | last post by:
The following SQL does as I intend, but ... SELECT offender_id, off_name, inv_ppo_id, add_note, add_zip_id, it_name AS Purpose FROM (offender INNER JOIN (investigation INNER JOIN lu_investigation_type ON investigation.inv_action_id = lu_investigation_type.investigation_type_id) ON offender.offender_id = investigation.inv_off_id) LEFT...
7
6870
by: John Øllgård Jensen | last post by:
Hi Using MS Asccess 2000: In a query I'm trying to create a new field with following expression: FilmDate: Left(,4) The field "FilmNo" is another text field in the query. This is expression should return the 4 leftmost characters of the FilmNo
1
1911
by: aaron.reese | last post by:
Guys, this is my problem:- I have records which are linked by two fields on a left inner join (there may be 0,1 or more records in the right hand table) The relationship is not unique (it's a map reference so it can't be...) so I am getting a cartesian join on the right hand table - look, it's not my database OK, I don't have any...
2
1317
by: eurolinux | last post by:
I'm trying to left join a table where there are two identical fields I want to select all records in a field from the left table and only those records from the identical field of the right table which are not bigger or smaller then 1 both field in the tables are integer data type Can someone help
1
1541
by: colleen1980 | last post by:
Hi: Can any one please tell me how do i change my query so that it also shows the records from table Main which are not in table . I try to use the LEFT Join but it gives syntax error. Thanks. SELECT q1.Name, q1.Tot-q2.Tot AS StarBalance FROM , Sum(Main.) as Tot Group By ]. AS q1 INNER JOIN , Sum(Val(Replace(nz(Prize,0),"$",""))) as Tot
2
4047
by: David F | last post by:
Hello, I have what seems like a simple left join query, but Access returns results like it's an inner join. I've tried numerous combinations without success. I have a table (ProjectList) with one row per project. Each project has a status condition of Green, Yellow or Red. The other table (DisplayOrder) has three rows, one for each...
9
1732
by: chadlupkes | last post by:
I'm getting NULLs where there shouldn't be. Any help is appreciated. Here are the tables: precinct Field Type Null Key Default Extra id smallint(6) PRI NULL auto_increment precinct_number int(11) 0 precinct_name varchar(20) MUL
5
1753
by: jimatqsi | last post by:
I have some code that works fine in an all Access environment. I am moving the back-end to SQL Server 2012. After moving the back-end I am getting this error when I run a particular query. ODBC--Call Failed The multi-part identifier "ro.RoleID" could not be bound. (#4104) The multi-part identifier "ro.RoleID" cound not be bound.(#4104) The...
0
7499
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7780
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8013
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
7862
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
5136
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3539
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3530
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1112
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
814
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

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.