Solved

Update a date field to NULL

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

Expert Comment

by:Mr_Ben
ID: 21822247
Try:

IIf(IsNull('" & LaundryLetters1Date & "'),'', '" & LaundryLetters1Date & "')
into:
IIf(IsNull('" & LaundryLetters1Date & "')," & # NULL# & ", '" & LaundryLetters1Date & "')
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
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…

757 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now