"Invalid Use Of Null" using CDate

Hi All,

I have a date/time field in a table which is converted within a query from a string value via the following:

New Date Field: Int(CDate([Original Date Field]))

The problem is, I cannot view the query in design view if any records do not have data within the date field -- I get a "Invalid Use Of Null" error message.  This field will not always contain data, therefore, I need some way to work around this.

Suggestions?

Thanks,
Ed
MICHAEDAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
shanesuebsahakarnConnect With a Mentor Commented:
Try:

New Date Field: IIf(IsNull([Original Date Field]),Null,Int(CDate([Original Date Field])))
0
 
Ryan ChongConnect With a Mentor Commented:
try:

New Date Field: IIF(IsDate([Original Date Field]) ,  Int(CDate([Original Date Field])) , 0)
0
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
or

New Date Field: IIF(([Original Date Field])Is Null ,"",  Int(CDate([Original Date Field])))
0
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'.

 
MICHAEDAuthor Commented:
Hi,

I'm getting "Invalid Operator" so I'm trying to find the expression's error....
0
 
Jokra_the_BarbarianCommented:
Alternatively, you can avoid the IIF function by using:
     Format(Nz([Original Date Field], ""), "mm/dd/yy")
0
 
MICHAEDAuthor Commented:
Jokra, will your function convert my date/time field ...or leave it as a string?
0
 
MICHAEDAuthor Commented:
Folks, I've tried all suggestions -- and I do not get the error anymore.  However, the sort order is incorrect...it appears that the field is sorting like a string field rather than a date field.
0
 
MICHAEDAuthor Commented:
Shane's version worked -- thanks all!

Ed
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.

All Courses

From novice to tech pro — start learning today.