• Status: Solved
• Priority: Medium
• Security: Public
• Views: 325

# 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"))

0
Anthony6890
• 7
• 5
• 2
• +1
1 Solution

Commented:
Just wrap the BDATE expression in CDate()
0

Commented:
Or CVDate() -- whichever works for you
0

Author Commented:
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")))
0

Author Commented:
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")))

0

Commented:

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"))
0

Commented:
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
0

Author Commented:
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?
0

CIOCommented:
You can use the expression:

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

/gustav
0

Author Commented:
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.
0

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

/gustav
0

Author Commented:
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...
0

CIOCommented:
Like this:

BDAY: CDate(Format("19" & [MBBIDA], "0000/00/00"))

/gustav
0

Author Commented:

Thanks!!!!
0

CIOCommented:
Really?

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

/gustav
0

Author Commented:
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.
0

CIOCommented:
Now I see. Thanks!

/gustav
0

## Featured Post

• 7
• 5
• 2
• +1
Tackle projects and never again get stuck behind a technical roadblock.