Solved

Update a date field to NULL

Posted on 2008-06-19
9
1,789 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 143

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 143

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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

808 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