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

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
Asked:
Anthony6890
  • 7
  • 5
  • 2
  • +1
1 Solution
 
Helen FeddemaCommented:
Just wrap the BDATE expression in CDate()
0
 
Helen FeddemaCommented:
Or CVDate() -- whichever works for you
0
 
Anthony6890Author 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

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


Thanks for your help!!!
0
 
RamanhpCommented:

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
 
RamanhpCommented:
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
 
Anthony6890Author 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
 
Gustav BrockCIOCommented:
You can use the expression:

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

/gustav
0
 
Anthony6890Author 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
 
Gustav BrockCIOCommented:
I see no "c" from "cyymmdd" in 201226.

/gustav
0
 
Anthony6890Author 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
 
Gustav BrockCIOCommented:
Like this:

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

/gustav
0
 
Anthony6890Author Commented:
Gustav, your formula worked!  

I really appreciate your input.  

Thanks!!!!
0
 
Gustav BrockCIOCommented:
Really?

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

/gustav
0
 
Anthony6890Author 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
 
Gustav BrockCIOCommented:
Now I see. Thanks!

/gustav
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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