Concatenating data

[jealindgar]

Hello
I have 3 fields in my main form: day, month and year. Why? Some records contain day, month and year, others only month and year and others only year (for example) ´cause I don´t know all these data (this is the reason)

But when I concatenate the three fields in one only, this union can appear, for example: "01 of March of 2007", or "of March of 2007", or "of of 2007"

I do not have problems if three data are, but when only I have two or one of them that is what occurs. Any help?

 

[RuralGuy]

Try:
=([DayField]+" of ") + ([MonthField]+" of ") + [YearField]
...using *your* field names of course.

 

[missinglinq]

I tried that, RG, and that dog just don't hunt, unless you have all three elements present! If you don't have all three you get nothing.

This will do it, where ComboDateField is the concatenated field:

If Not IsNull(DayField) And Not IsNull(MonthField) And Not IsNull(YearField) Then
 ComboDateField = DayField & " of " & MonthField & " of " & YearField
End If

If IsNull(DayField) And Not IsNull(MonthField) And Not IsNull(YearField) Then
 ComboDateField = MonthField & " of " & YearField
End If

If IsNull(DayField) And IsNull(MonthField) And Not IsNull(YearField) Then
 ComboDateField = YearField
End If

This will handle the 3 conditions the OP outlined. It'll leave the combined field blank if other combinations are present, like having just a date, or just a month, but that's probably as it should be. If the OP has a fixed way to handle that kind of situation, it can be added to this routine in the same fashion!

Linq

Hey, RG! Hope it's cooler up there in the Rockies than it is down here in the flatlands! Richmond went over 102 & 104 F this week!

 

[RuralGuy]

How about:
=([DayField]+" of ") & ([MonthField]+" of ") & [YearField]
Hi Linq,
It is *much* cooler up here. We're usually 70F-80F around here but cooling rain about 2:00 PM and the temp drops 5-10 degrees. I love it.

 

[missinglinq]

Wish I were there, RG! Yes, your latest code will do the job, as long as there are only the three possible combinations the OP said could occur. The results get flaky with other combinations. A day and a year, for instance, could be misinterpreted, if the day were "1" thru "12." s dayfield = "2" and yearfield = "2007" would yield "2 of 2007" would could be tsken for "February 2007." A dayfield = "1" and monthfield = "March" without a year will give you "1 of March of." These kind of things are eliminated with my code because it simply doesn't assign a value to the target field if these kinds of combinations are entered by some doofus data entry type!

 

[jealindgar]

How about:
=([DayField]+" of ") & ([MonthField]+" of ") & [YearField]
Hi Linq,
It is *much* cooler up here. We're usually 70F-80F around here but cooling rain about 2:00 PM and the temp drops 5-10 degrees. I love it.

this is OK!! thanx!!!

but i don´t understand your help missing, ´cause help of ruralguy is perfect

 

[jealindgar]

ops, you are right missingling

is ok if i have day, month and year, month and year or only year, but isn´t ok if i have only day and/or month, because appear "of"

do you understand me?

 

[RuralGuy]

OK, then how about:
=[DayField] & (" of " + [MonthField]) & (" of " + [YearField])
...I never give up.

 

[RuralGuy]

Actually MissingLinq's solution will be the best. I was just trying to be clever.