asked on

How to combine 2 statements in Microsoft Access statement?

I have a column that converts AS400 date formats into mm/dd/yyyy except it it converting it as a text. I have a formula that will convert it as a date/time format, but now I have two extra columns in my table. One for the converted 400 date and another to convert that converted test to a date format. Does anyone know of one statement to be used in access that would work? Or can someone help me put these two statements together:

BDATE: IIf(Len([MBBIDA])=7,Format(Mid([MBBIDA],4,2) & "-" & Mid([MBBIDA],6,2) & "-" & Mid([MBBIDA],2,2),"mm/dd/yyyy"),Format(Mid([MBBIDA],3,2) & "-" & Mid([MBBIDA],5,2) & "-" & Left([MBBIDA],2),"mm/dd/yyyy"))

BDAY: CVDate(Format([BDATE],"mm/dd/yyyy"))

Helen Feddema

Just wrap the BDATE expression in CDate()

Helen Feddema

Or CVDate() -- whichever works for you

Anthony

ASKER

That makes sense. I'm a little of a novice with this so would the expression look like this:

Cdate(BDATE: IIf(Len([MBBIDA])=7,Format(Mid([MBBIDA],4,2) & "-" & Mid([MBBIDA],6,2) & "-" & Mid([MBBIDA],2,2),"mm/dd/yyyy"),Format(Mid([MBBIDA],3,2) & "-" & Mid([MBBIDA],5,2) & "-" & Left([MBBIDA],2),"mm/dd/yyyy")))

Anthony

ASKER

I got it... it worked this way:

BDAY: CDate(IIf(Len([MBBIDA])=7,Format(Mid([MBBIDA],4,2) & "-" & Mid([MBBIDA],6,2) & "-" & Mid([MBBIDA],2,2),"mm/dd/yyyy"),Format(Mid([MBBIDA],3,2) & "-" & Mid([MBBIDA],5,2) & "-" & Left([MBBIDA],2),"mm/dd/yyyy")))

Thanks for your help!!!

Ramanhp

BDAY: CVDate( IIf(Len([MBBIDA])=7,Format(Mid([MBBIDA],4,2) & "-" & Mid([MBBIDA],6,2) & "-" & Mid([MBBIDA],2,2),"mm/dd/yyyy"),Format(Mid([MBBIDA],3,2) & "-" & Mid([MBBIDA],5,2) & "-" & Left([MBBIDA],2),"mm/dd/yyyy")) ,"mm/dd/yyyy"))

Ramanhp

just to add on to it, in case, any time you like to have multiple functions, there in, you need to put the complete syntax; the label defined in the current statement would not get recognized as it is also initializing at the time of execution

Anthony

ASKER

I appreciate everyone's responses. The problem that I have now is that if my MBBIDA columns has this value:

201226

the column next to is converts this to 12/26/2020

The value is coming from an as400 so the value is "cyymmdd"

The above example should be 12/26/1920

Any ideas on how to fix this?

Gustav Brock

You can use the expression:

strDate = "201226"
datDate = CDate(Format("19" & strDate, "0000/00/00"))

/gustav

Anthony

ASKER

where would I put that in this expression:

BDAY: CDate(IIf(Len([MBBIDA])=7,Format(Mid([MBBIDA],4,2) & "-" & Mid([MBBIDA],6,2) & "-" & Mid([MBBIDA],2,2),"mm\/dd\/yyyy"),Format(Mid([MBBIDA],3,2) & "-" & Mid([MBBIDA],5,2) & "-" & Left([MBBIDA],2),"mm\/dd\/yyyy")))

I'm a little confused.

Gustav Brock

I see no "c" from "cyymmdd" in 201226.

/gustav

Anthony

ASKER

That's correct. The c would be 0. Since you can't have leading zeroes in a number field in access, it won't have anything there. It only happens for people that are born before 1930...

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anthony

ASKER

Gustav, your formula worked!

I really appreciate your input.

Thanks!!!!

Gustav Brock

Really?

But what is put for c for those born after 1930?

/gustav

Anthony

ASKER

From what I've been reading on the internet, is that Microsoft Access assumes that everything that is in a 2 digit year format before 1930 is in the 2000's. So if the date format was 230324 Access reads the date conversion as 03/24/2023 not 1923. Your formula rectifies that problem for me.

Gustav Brock

Now I see. Thanks!

/gustav