Converting 3 number fields into 1 date field?

[CK_1984]

I have 3 number fields [Month], [Day], [Year]. I would like to combine these 3 fields into 1 date field with the following format M/DD/YYYY.

I tried DateSerial([Month], [Day], [Year]) without success.

For example: When Month is 11, Day is 9, and Year is 1998 the DateSerial answer is 2/18/2017 or when Month is 3, Day is 9, and Year is 1999 the DateSerial answer is 1/19/2009.

Please help.

Thanks

 

[pbaldy]

From help:

Syntax
DateSerial(year, month, day)

 

[CK_1984]

Thank you it work beautifully

 

[pbaldy]

Happy to help, and welcome to the site.

 

[CK_1984]

I thought it worked until I tried to set criteria.

Criteria
between [enter begin date] and [enter end date]

Input
7/1/2009 and 8/15/2010

Answer
all dates with 7 and 8 as the month regardless of day and year and no date in between those months.

Can I not set criteria like a regular date field or am I missing some step or syntax error?

Thanks for the help.

 

[pbaldy]

It sounds like the field is text, or being treated as such. Can you post the db? Personally I don't use the [Enter..] method of getting user input. You have a lot more control when you have the user enter on a form.

 

[lmcc007]

From help:

Syntax
DateSerial(year, month, day)

If you're missing the day or month or year, how do you tell it to display what you have or say "incomplete date"?

 

[pbaldy]

You could use the Nz() function on each argument to replace the Null with whatever you want (1 for day for instance).

 

[lmcc007]

You could use the Nz() function on each argument to replace the Null with whatever you want (1 for day for instance).

LIke:

DateSerial(Nz(year, "___"), Nz(month, "__"), Nz(day, "__"))

 

[pbaldy]

No, because the DateSerial() function can't handle the strings. You'd have to provide a numeric value.

 

[lmcc007]

Got it. Used:

Expr1: IIf(IsNull([EventMonth_n]),"mm",Format([EventMonth_n],"00")) & "/" & IIf(IsNull([EventDay_n]),"dd",Format([EventDay_n],"00")) & "/" & IIf(IsNull([EventYear_n]),"yyyy",([EventYear_n]))

 

[lmcc007]

What's the quickest way to get Dates already in db in put them in to 3 separate fields?

 

[pbaldy]

An update query using the Day, Month and Year functions.

 

[lmcc007]

Got it, I used: Month: DatePart("m",[EventDate])