Solved

Update a date field to NULL

Posted on 2008-06-19
9
1,783 Views
Last Modified: 2013-11-27
Hi
I'm not sure if I'm going about this the right way.  I'm trying to update a table with values from a form.  The problem I am having is that sometimes these values are empty, and sometimes they have data.  I've got round this by using IIF functions in my update statement, however this isn't quite working on a date field.  Basically the date may be blank, in which case it should update the datetime field in the table to null, or it may have a date value (running the update in this instance works).  I'm getting type conversion errors when I try to update to null.  Code attached, many thanks
"dbo_tbl_Weekly_Expenses_Temp.[LaundryLetters1] = IIf(IsNull('" & LaundryLetters1 & "'),'', '" & LaundryLetters1 & "'), " & _ 'This line works fine, updating a text field
         "dbo_tbl_Weekly_Expenses_Temp.[LaundryLetters1Date] = IIf(IsNull('" & LaundryLetters1Date & "'),'', '" & LaundryLetters1Date & "') " & _ 'This line errors, updating datetime field

Open in new window

0
Comment
Question by:grunners80
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21821876
change:

IIf(IsNull('" & LaundryLetters1Date & "'),'', '" & LaundryLetters1Date & "')
into:
IIf(IsNull('" & LaundryLetters1Date & "'), NULL, '" & LaundryLetters1Date & "')
0
 

Author Comment

by:grunners80
ID: 21822010
Thanks, I'd tried that earlier, Access errors

"...Access didn't update 3 field(s) due to a type conversion failure"

I know though that if I put a date in and leave the other 2 fields as null the update goes through fine, so it's definitely the date that is causing the problem.

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21822033
ok, let's go 1 step further:
 " & IIf(IsNull(LaundryLetters1Date), "NULL", "#" & LaundryLetters1Date & "#") & " 

Open in new window

0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 1

Expert Comment

by:Mr_Ben
ID: 21822247
Try:

IIf(IsNull('" & LaundryLetters1Date & "'),'', '" & LaundryLetters1Date & "')
into:
IIf(IsNull('" & LaundryLetters1Date & "')," & # NULL# & ", '" & LaundryLetters1Date & "')
0
 

Author Comment

by:grunners80
ID: 21822796
I've managed to get it to work with the following, however if the condition is false it now won't update the date with that in the text box!  
IIf(Nz([forms]![frm_Expenses]![txtLaundryDate1])=0, Null, '" & LaundryLetters1Date & "')

Open in new window

0
 

Author Comment

by:grunners80
ID: 21822947
My question is, how can i still be getting a data type mismatch error when trying to update using the EXACT same code in an IIF statement that I use as simple criteria in an update?
'For instance, this works:
 
"dbo_tbl_Weekly_Expenses_Temp.[LaundryLetters1Date] ='" & Format([Forms]![frm_Expenses]![txtLaundryDate1], "yyyy-mm-dd") & "'" & _
 
'But this doesn't:
 
"dbo_tbl_Weekly_Expenses_Temp.[LaundryLetters1Date] = IIf(Nz([forms]![frm_Expenses]![txtLaundryDate1])=0, Null, '" & Format([Forms]![frm_Expenses]![txtLaundryDate1], "yyyy-mm-dd") & "') " & _

Open in new window

0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 21823230
You are making it too complicated:

  "dbo_tbl_Weekly_Expenses_Temp.[LaundryLetters1Date] = " & IIf(IsNull(LaundryLetters1Date, Null, Format(LaundryLetters1Date, "\#yyyy\/mm\/dd\#")) & ""

/gustav
0
 

Author Comment

by:grunners80
ID: 21829504
Datatype mismatch in criteria expression again.  This is really bugging me now, it's a day ruiner!
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 200 total points
ID: 21829541
A right paranthesis was missing:

 "dbo_tbl_Weekly_Expenses_Temp.[LaundryLetters1Date] = " & IIf(IsNull(LaundryLetters1Date), Null, Format(LaundryLetters1Date, "\#yyyy\/mm\/dd\#")) & ""

And
dbo_tbl_Weekly_Expenses_Temp.[LaundryLetters1Date]
is of DateTime data type?

/gustav
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question