By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
460,022 Members | 1,401 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 460,022 IT Pros & Developers. It's quick & easy.

How to combine two Microsoft Access date fields into one

P: n/a
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.
Nov 4 '10 #1
Share this Question
Share on Google+
9 Replies


NeoPa
Expert Mod 15k+
P: 31,769
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 :
Expand|Select|Wrap|Line Numbers
  1. =[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.
Nov 4 '10 #2

gnawoncents
100+
P: 214
Try this:

Expand|Select|Wrap|Line Numbers
  1. =IIf(IsNull([Previous Start Date]),([Previous Start Date] & " to " & [PrevAssnEnd]),Null) 
Nov 4 '10 #3

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


This could do that.
Expand|Select|Wrap|Line Numbers
  1. =IIF(Isnull([Previous Start Date]or isnull([PrevAssnEnd],Null,[Previous Start Date] & " to " & [PrevAssnEnd])
Nov 4 '10 #4

100+
P: 255
Rozeanna, you forgot some close brackets for the IsNulls =)

Expand|Select|Wrap|Line Numbers
  1. =IIF(IsNull([Previous Start Date])OR IsNull([PrevAssnEnd]),Null,[Previous Start Date] & " to " & [PrevAssnEnd])
Nov 5 '10 #5

NeoPa
Expert Mod 15k+
P: 31,769
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 :
Expand|Select|Wrap|Line Numbers
  1. =[Previous Start Date] + ' to ' + [PrevAssnEnd])
There really is no need to over-complicate matters by using a bunch of function calls.
Nov 5 '10 #6

gnawoncents
100+
P: 214
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:

Expand|Select|Wrap|Line Numbers
  1. =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
Nov 5 '10 #7

NeoPa
Expert Mod 15k+
P: 31,769
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.
Nov 5 '10 #8

gnawoncents
100+
P: 214
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.
Nov 6 '10 #9

NeoPa
Expert Mod 15k+
P: 31,769
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 :
Expand|Select|Wrap|Line Numbers
  1. =IIf([Previous Start Date] & [PrevAssnEnd]='','',[Previous Start Date] & IIf(IsNull([PrevAssnEnd]),'', ' to ' & [PrevAssnEnd]))
BTW Good catch GnawOnCents.
Nov 6 '10 #10

Post your reply

Sign in to post your reply or Sign up for a free account.