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.
7  1431 
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
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.
In fact Nz will, without a default value will give a zero 0 or empty space ""
DateValue(0) will give an error
Phil
However, in this case, Nz() returned an error.
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]))
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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.
...
|
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)...
|
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...
|
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.
...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
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...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
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...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |
