Solved

Update a date field to NULL

Posted on 2008-06-19
9
1,804 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 
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 51

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 51

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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

617 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