[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Access 2000 - Cdate with IIF

Posted on 2011-05-12
15
Medium Priority
?
659 Views
Last Modified: 2012-06-21
Dear Experts,

I have a field which has text format in Access, it can have three kind of values

- text "CommonCode"
- text "NotInRSD"
- texts like "2012-02-22"

My target would to convert the third scenario to date format with CDate, in the other two cases it should stay as it is.

I have tried this formula but this does not seem converting the date kind of text to date

DateFormatted: IIf([Date_]="CommonCode" Or [Date_]="NotInRSD";[Date_];CDate([Date_]))

thanks,
0
Comment
Question by:csehz
  • 6
  • 4
  • 4
  • +1
15 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35749022
try

DateFormatted: IIf(isdate([Date_]); CDate([Date_]);[Date_])

or this

DateFormatted: IIf(isdate([Date_]), CDate([Date_]),[Date_])
0
 
LVL 40

Expert Comment

by:als315
ID: 35749042
You can't store in one field text and date values. You can add field with date, where will be converted date
0
 
LVL 1

Author Comment

by:csehz
ID: 35749062
Thanks I have tried both, but unfortunately it is still text.

Basically this formula works for me - DateFormatted: CDate([Date_]) - converting the date text to date, but bringing #Error for the other two scenarios.

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 1

Author Comment

by:csehz
ID: 35749088
Als315 thanks, do you have maybe idea then how to keep the dates texts as date and maybe applying blank for the other two ones?

So being blank where now it brings the CDate value #Error
0
 
LVL 1

Author Comment

by:csehz
ID: 35749111
Or also would be proper to me having some fictitious date instead of the #Error, for example 2015-01-01
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35749115
Because you cannot make the field DateFormatted either a datetime datatype or a string datatype -  it is either one or the other - as stated by als315.
0
 
LVL 1

Author Comment

by:csehz
ID: 35749162
GRayL yes but could be maybe applied a kind of iserror for such case?

So IIf the Cdate get error then just give some fictitious date like 2015-01-01, or can be even blank, and if the Cdate is successful without error, in that case should be that result.

0
 
LVL 40

Expert Comment

by:als315
ID: 35749203
May be better to use 1900-01-01, but you think in right direction
0
 
LVL 40

Expert Comment

by:als315
ID: 35749229
Or you can put NULL:
IIf(IsDate([Date_]);CDate([Date_]);Null)
0
 
LVL 40

Accepted Solution

by:
als315 earned 1000 total points
ID: 35749248
Sorry, different regional settings. Correct:
IIf(IsDate([Date_]),CDate([Date_]),Null)
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35749315
csehz,

are you storing the dateformatted to a field in a table?

i thought you are just using it in a query...
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 1000 total points
ID: 35749338
if that is the case then use

try

DateFormatted: IIf(isdate([Date_]); CDate([Date_]);null)

or this

DateFormatted: IIf(isdate([Date_]), CDate([Date_]),null)


or

DateFormatted: IIf(isdate([Date_]); CDate([Date_]);#1/1/1900#)

or this

DateFormatted: IIf(isdate([Date_]), CDate([Date_]),#1/1/1900#)

0
 
LVL 1

Author Comment

by:csehz
ID: 35749485
Als315, I have used your formula on my machine like this

Expr2: IIf(IsDate([Date_]);CDate([Date_]);Null) and works perfect.

Capricorn, also your formulas working, I applied this version

Expr5: IIf(IsDate([Date_]);CDate([Date_]);#1900-01-01#)

Thanks very much the help, you are great, so closing the topic
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35749533
the poster that modified the original code, got the accepted answer and the original got an assist. what an irony...
0
 
LVL 1

Author Comment

by:csehz
ID: 35749628
Capricorn sorry but on my machine has to be ; instead of the , and sometimes manually has to change. However you prepared both, just I am an amateur and can confuse a lot things :-)

Basically I still does not understand what is the difference of this two

1) Your first ; version post was like this

Expr6: IIf(IsDate([Date_]);CDate([Date_]);[Date_])

and this is still text for dates, and CommonCode and NotInRSD text without #Error.

2) In your last posting in my understanding this is around the same

Expr7: IIf(IsDate([Date_]);CDate([Date_]);Null)

and this works perfectly for my targets. Date for dates and blank for the texts.

But still considering why, but you know I could think on this even for two weeks :-))) For me just great that it works now this 2) formula.

Anyway I am using this in a query, so this text field is in a query, and based on that applying your formula
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

872 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