How to combine two Microsoft Access date fields into one

[Leelee]

I have a Start Date field and a End date field in one of my tables. I have a report that I created and I want to combine the two fields into one so that it reads as follows:

01/01/2010 to 12/31/2010

This is the expression I'm using: =[Previous Start Date] & " to " & +[PrevAssnEnd]

The problem is, for those records with no data values the word "to" is showing up in the field and I want the field to remain blank if the field is empty.

[NeoPa]

LeeLee:
The problem is, for those records with no data values the word "to" is showing up in the field and I want the field to remain blank if the field is empty.

How can this make sense when there are two fields? Please make the question make sense.

Also, is the plus sign (+) in =[Previous Start Date] & " to " & +[PrevAssnEnd] simply a mistake? If not, the question makes even less sense than I thought.

Assuming you have the commonest form of this issue, you may want to try :

=[Previous Start Date] & ' to ' + [PrevAssnEnd])

See Using "&" and "+" in WHERE Clause for more on why that will have the effect I think you're after.

[gnawoncents]

Try this:

=IIf(IsNull([Previous Start Date]),([Previous Start Date] & " to " & [PrevAssnEnd]),Null) 

[Rozeanna Jerry]

SO that means if one of the dates are blank then nothing should be shown:


This could do that.

=IIF(Isnull([Previous Start Date]or isnull([PrevAssnEnd],Null,[Previous Start Date] & " to " & [PrevAssnEnd])

[colintis]

Rozeanna, you forgot some close brackets for the IsNulls =)

=IIF(IsNull([Previous Start Date])OR IsNull([PrevAssnEnd]),Null,[Previous Start Date] & " to " & [PrevAssnEnd])

[NeoPa]

I know the question wasn't well asked and was quite ambiguous, but it's rare for end dates to exist in situations where start dates don't (not impossible but extremely rare in real life situations).

With this in mind one can guess that what is required is to handle the absence of an end date (rather than the start date as post #3 almost handles). To do this it is absolutely not necessary to check for nulls using either IsNull() or Nz(), but the simple expedient of using the plus (+) character as a concatenator will work (as already illustrated in post #2 and fully explained in the linked article).

If the guess is wrong (who can't say for sure unless and until the OP clarifies the question), then implementing displaying the string only if both values are non-null is even more straightforward :

=[Previous Start Date] + ' to ' + [PrevAssnEnd])

There really is no need to over-complicate matters by using a bunch of function calls.

[gnawoncents]

NeoPa,

Thanks for the "+" tip. I hadn't seen that one before and will definitely use it in my future work. Also, sorry to post on top of you -- I failed to refresh the page and see if anyone had posted a solution before posting mine.

-------

Leelee,

NeoPa is absolutely correct (no surprise there). If you are working with text values, go with his solution. It is much cleaner. However, if you are using date fields, you can use an IIf statement; but don't use my first one since I got the Null backwards, and as NeoPa pointed out, should probably be looking at the end date for Null. Instead try:

=IIf(IsNull([PrevAssnEnd]),Null,([Previous Start Date] & " to " & [PrevAssnEnd])) 

Finally, as NeoPa said,

who can say for sure unless and until the OP clarifies the question

[NeoPa]

GnawOnCents:
However, if you are using date fields,

I hate to disabuse, as you certainly seem to be grappling with the issues, but as the result is textual, the original form of the fields shouldn't really come into it. The point is that the requirement is for a textual result. If you were looking to use IsNull() then you would want to make sure that if the end date is null then both the end date, and the string ' to ', were both dropped from the result (It's possible to use double-quotes (") in Access SQL but the standard is to use single quotes (')). This doesn't seem to be the result of the code posted. Sorry.

[gnawoncents]

NeoPa,

Please excuse my ignorance. I had tried the + signs and got errors whenever dates were used in both source fields, but am likely just missing something.

Regarding the single quotes, I hadn't heard it was preferred to use them in place of double quotes. After reading your comment, I read your insight on single vs double quotes and learned a few things. Once again, thank you for your expert advice.

[NeoPa]

I suspect this is down to Access automatically converting things where it detects a need. Typically, adding dates to a string (particularly concatenating using the &) is easily recognised as requiring the date value to be converted to text (using the default date format). I expect that the plus concatenation char (+) may cause some confusion as it is also the addition character. Dates can be considered to be numeric, so unfortunately when it sees the plus char it doesn't treat it as a concatenation instruction at all, but as an addition character instead. As converting the date to a formatted string rather blows out the whole concept, I have to say that my approach won't actually work in this case.

It's a shame, as the longhand version looks and reads quite clumsily :

=IIf([Previous Start Date] & [PrevAssnEnd]='','',[Previous Start Date] & IIf(IsNull([PrevAssnEnd]),'', ' to ' & [PrevAssnEnd]))

BTW Good catch GnawOnCents.