concatenate text into a date

[LGarcia]

Hi all,
I have 3 text fields: myMonth, myDay, myYear. In a query I would like to
concatenate these fields to create a valid date. The problem is that not all
fields have data. If I have a month and a year I can use a 1 for day. If I
only have a year then it should return a null.
I strung a few IIF statements to do this but all I get is text that looks
like a date but doesn't sort or filter like a date. Any suggestions?

Thanks,
LGarcia

 

[Dirk Goldgar]

Hi all,
I have 3 text fields: myMonth, myDay, myYear. In a query I would like to
concatenate these fields to create a valid date. The problem is that not
all fields have data. If I have a month and a year I can use a 1 for day.
If I only have a year then it should return a null.
I strung a few IIF statements to do this but all I get is text that looks
like a date but doesn't sort or filter like a date. Any suggestions?

Possibly:

IIf(myYear Is Null, Null, DateSerial(myYear, Nz(myMonth, 1), Nz(MyDay,
1))

I *think* DateSerial will successfully convert numeric text values to the
integers it requires. If not, you could use this:

IIf(myYear Is Null, Null, CDate(Nz(myMonth, "1") & "/" & Nz(MyDay, "1")
& "/" & myYear))

 

[Douglas J. Steele]

in message news:[email protected]...


Possibly:

IIf(myYear Is Null, Null, DateSerial(myYear, Nz(myMonth, 1), Nz(MyDay,
1))

I *think* DateSerial will successfully convert numeric text values to the
integers it requires. If not, you could use this:

Yes, in my opinion DateSerial is the best route to take.

 

[LGarcia]

DateSerial worked! Many thanks to you both!

Yes, in my opinion DateSerial is the best route to take.