Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1815
  • Last Modified:

Update a date field to NULL

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
grunners80
Asked:
grunners80
  • 4
  • 2
  • 2
  • +1
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
change:

IIf(IsNull('" & LaundryLetters1Date & "'),'', '" & LaundryLetters1Date & "')
into:
IIf(IsNull('" & LaundryLetters1Date & "'), NULL, '" & LaundryLetters1Date & "')
0
 
grunners80Author Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
ok, let's go 1 step further:
 " & IIf(IsNull(LaundryLetters1Date), "NULL", "#" & LaundryLetters1Date & "#") & " 

Open in new window

0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Mr_BenCommented:
Try:

IIf(IsNull('" & LaundryLetters1Date & "'),'', '" & LaundryLetters1Date & "')
into:
IIf(IsNull('" & LaundryLetters1Date & "')," & # NULL# & ", '" & LaundryLetters1Date & "')
0
 
grunners80Author Commented:
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
 
grunners80Author Commented:
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
 
Gustav BrockCIOCommented:
You are making it too complicated:

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

/gustav
0
 
grunners80Author Commented:
Datatype mismatch in criteria expression again.  This is really bugging me now, it's a day ruiner!
0
 
Gustav BrockCIOCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now