IIF statement

djai695

Registered User.
Local time
Today, 10:42
Joined
Dec 13, 2013
Messages
21
Does anyone know how to get this expression to work? It works by changing the numbers to correct date format, however, if the field is null, I want a blank to appear instead of "type"

=IIf([DATE_APPLIED_X] Is Null,"""",CDate(Mid([DATE_APPLIED_X],5,2) & "/" & Right([DATE_APPLIED_X],2) & "/" & Left([DATE_APPLIED_X],4)))

Report.JPG
 
Try
Code:
=IIf(Len([DATE_APPLIED_X] & '') = 0, [DATE_APPLIED_X], CDate(Mid([DATE_APPLIED_X], 5, 2) & "/" & Right([DATE_APPLIED_X], 2) & "/" & Left([DATE_APPLIED_X], 4)))
 
Thank you, I tried this, but I'm still getting the same results. It does not seem to be working. Any other ideas?
 
Show some sample data. Is there a possibility the data is not formatted as you desire it to be? Maybe..
Code:
yourData    ActualDate    ShouldHaveBeen
20131217    17 Dec 2013    20131217
2013314     14 Mar 2013    20130314
201311      1 Jan 2013     20130101
 
I highlighted the original field as it appears in the first column, then the second column is what I get when I use your statement. It works partially, but does not show me a blank when the [DATE_APPLIED_X] field is blank. Instead it displays "Type!"

Report.JPG
 
I am guessing this is a Report? If so, have you set a Format for the Control?
 
Yes this is a report. After I typed in the expression you provided, I then changed the format to short date.
 
Unfortunately it did not, I'm trying to figure this out. Any other ideas?
 
Remove the format. If that does not work.. Upload a Stripped DB.

To create a Sample DB (to be uploaded for other users to examine); please follow the steps..

1. Create a backup of the file, before you proceed..
2. Delete all Forms/Queries/Reports that are not in Question (except the ones that are inter-related)
3. Delete auxiliary tables (that are hanging loose with no relationships).
4. If your table has 100,000 records, delete 99,990 records.
5. Replace the sensitive information like Telephone numbers/email with simple UPDATE queries.
6. Perform a 'Compact & Repair' it would have brought the Size down to measly KBs..
7. (If your Post count is less than 10 ZIP the file and) Upload it..

Finally, please include instructions of which Form/Query/Code we need to look at. The preferred Access version would be A2003-A2007 (.mdb files)
 
I was actually able to manipulate the query, and somehow got it to work. Thank you.
 

Users who are viewing this thread

Back
Top Bottom