Solved

# Combine dates from two fields

Posted on 2012-04-11
576 Views
I have a query in access 2007, one field in one row has an ID number (i.e. 11476) and another field in a second row has an ID number (i.e. 11476b). Each row has two date fields, a beginning and ending date. The first row beginning date is 6/1/11 and the first row ending date is 1/31/12. The second row beginning date is 2/1/12 and the second row ending date is 5/31/12. The beginning date field is named BPB and the ending date field is named BPE. How do I combine the dates, so the result will be 6/1/11-5/31/12 using sql?
0
Question by:Barbara69
• 7
• 6

LVL 74

Expert Comment

ID: 37834655
SELECT [BPB] &"-" & [BPE] AS CombinedDates
FROM YourTable
0

LVL 119

Expert Comment

ID: 37834712
Barbara69,

I have seen so many question like this with sample data that turns out to be the incosistent..

so better if you can upload a copy of the db with the table..
0

Author Comment

ID: 37834762
I understand what you suggested, but that's not what I'm looking to do. I want to take the beginning date of one row and combine it with the ending date of another row. So, how do I specify which begin and end date to combine from each row?
0

LVL 119

Expert Comment

ID: 37834773
you can try this query, assuming the name of the table is TableA

SELECT Val([ID Number]) AS ID, (select min(t.bpb) from tablea as t where  val([t].[id number])= val([tableA].[id number])) & " - " & (select max(t.bpe) from tablea as t where  val([t].[id number])= val([tableA].[id number])) AS FromToDate
FROM TableA
GROUP BY Val([ID Number]);
0

Author Comment

ID: 37834874
I want to take the Budget Period Begin Date of 10280 and combine it with the Budget Period End Date of 10280b to make the date range of 6/1/10-5/31/11.

Grant Number      Budget Period Begin Date      Budget Period End Date
10280              6/1/2010                            12/31/2010
10280b              1/1/2011                             5/31/2011

0

LVL 119

Expert Comment

ID: 37834884
where is the attached ?

the query i posted will do it for you, just change the field names accordingly
0

LVL 119

Accepted Solution

Rey Obrero earned 500 total points
ID: 37834907
SELECT Val([Grant Number]) AS GrantID, (select min(t.[Budget Period Begin Date]) from tblGrant as t where  val([t].[Grant Number])= val([tblGrant].[Grant Number])) & " - " & (select max(t.[Budget Period End Date]) from tblGrant as t where  val([t].[Grant Number])= val([tblGrant].[Grant Number])) AS FromToDate
FROM tblGrant
GROUP BY Val([Grant Number]);
0

Author Comment

ID: 37835971
Capricorn1,

I've attached a copy of a qry I ran showing an expended amout column that I exported to Excel. Would the same sql you provided work for adding together the amounts from the expended column from two different rows?

Why are you using Val[Grant Number]), since Grant Number is already a number? How would I get only those grant numbers > 10125? I added HAVING (((Val[txtGrantNumber]))> "10125"));, but it doesn't work. I tried HAVING (((Val([txtGrantNumber])) between "10125" and "12577")), it sort of worked except for all the numbers before 10125. So, how do I get rid of all the numbers before 10125? See below.

Grant Number      FromToDate
11      8/1/2011 - 6/30/2012
116      6/1/2000 - 5/31/2001
117      6/1/2000 - 5/31/2001
118      1/12/2001 - 8/31/2001
119      10/2/2000 - 5/31/2001
120      10/1/2000 - 5/1/2001
121      8/1/2000 - 6/30/2001
122      10/1/2000 - 5/31/2001
124      12/1/2000 - 4/30/2002
1113      9/1/2001 - 8/31/2002
1114      7/1/2001 - 5/31/2002
1115      7/1/2001 - 5/31/2002
1116      7/1/2001 - 5/31/2002
1117      7/1/2001 - 5/31/2002
1118      7/1/2001 - 5/31/2002
1119      7/1/2001 - 7/31/2002
1120      7/1/2001 - 5/31/2002
1121      7/1/2001 - 6/30/2002
1122      7/1/2001 - 5/31/2002
1123      7/2/2001 - 5/31/2002
1124      7/15/2001 - 5/31/2002
1125      8/15/2001 - 5/31/2002
1126      8/15/2001 - 7/31/2002
1127      9/15/2001 - 5/31/2002
1128      9/15/2001 - 8/31/2002
1129      9/1/2001 - 8/31/2002
1130      10/1/2001 - 9/30/2002
1205      9/1/2001 - 8/31/2002
1206      9/1/2001 - 3/31/2003
1207      6/1/2001 - 5/31/2002
1208      6/1/2001 - 5/31/2002
1209      7/1/2001 - 6/30/2002
1210      6/1/2001 - 5/31/2002
1211      6/1/2001 - 8/31/2002
10125      9/1/2010 - 8/31/2011
10126      4/1/2011 - 4/30/2012
10130      1/1/2011 - 12/31/2011
10131      1/1/2011 - 12/31/2011
10132      2/1/2011 - 3/31/2012
10140      2/1/2011 - 2/29/2012
10168      4/1/2011 - 4/30/2012
10275      6/1/2010 - 5/31/2011
10280      6/1/2010 - 5/31/2011
10375      10/1/2010 - 9/30/2011
10376      6/1/2010 - 5/31/2011
10377      6/1/2010 - 5/31/2011
10379      6/1/2010 - 8/31/2011
10381      6/1/2010 - 8/31/2011
10382      6/1/2010 - 8/31/2011
10383      6/1/2010 - 8/31/2011
10384      6/1/2010 - 10/31/2011
10385      6/1/2010 - 8/31/2011
10386      7/1/2010 - 6/30/2011
10387      6/1/2010 - 8/31/2011
10388      10/1/2010 - 10/31/2011
10464      9/1/2010 - 9/15/2011
10469      6/1/2010 - 5/31/2011
10573      7/1/2010 - 6/30/2011
10574      6/1/2010 - 5/31/2011
10576      7/1/2010 - 6/30/2011
10577      9/1/2010 - 8/31/2011
10654      11/1/2010 - 10/31/2011
11132      8/1/2011 - 7/31/2012
11134      10/1/2011 - 9/30/2012
11135      10/1/2011 - 9/30/2012
11136      6/1/2012 - 5/31/2013
11137      10/1/2011 - 9/30/2012
11138      10/1/2011 - 9/30/2012
11139      12/1/2011 - 11/30/2012
11140      10/1/2011 - 9/30/2012
11141      3/1/2012 - 2/28/2013
11142      6/1/2012 - 5/31/2013
11143      2/1/2012 - 1/31/2013
11144      1/1/2012 - 12/31/2012
11145      3/1/2012 - 2/28/2013
11146      1/1/2012 - 12/31/2012
11147      2/1/2012 - 1/31/2013
11148      2/1/2012 - 1/31/2013
11149      1/1/2012 - 12/31/2012
11150      3/1/2012 - 2/28/2013
11151      2/1/2012 - 1/31/2013
11152      4/1/2012 - 3/31/2013
11153      4/1/2012 - 3/31/2013
11154      3/1/2012 - 2/28/2013
11155      4/1/2012 - 3/31/2013
11156      1/1/2012 - 12/31/2012
11157      1/1/2012 - 12/31/2012
11158      1/2/2012 - 8/31/2012
11226      5/1/2012 - 4/30/2013
11240      3/1/2012 - 2/28/2013
11380      6/1/2011 - 5/31/2012
11468      6/1/2011 - 8/31/2012
11475      10/1/2011 - 9/30/2012
11476      6/1/2011 - 5/31/2012
11477      6/1/2011 - 5/31/2012
11486      7/1/2011 - 6/30/2012
11564      9/16/2011 - 8/31/2012
11569      6/1/2011 - 5/31/2012
11754      11/1/2011 - 10/31/2012
12375      3/1/2012 - 2/28/2013
12576      6/1/2012 - 5/31/2013
Vendor.accdb
qryAwardedAmt.xlsx
0

LVL 119

Expert Comment

ID: 37837038
<Why are you using Val[Grant Number]), since Grant Number is already a number?>

have you seen my post at http:#a37834712 ?

your initial posting indicates the ID numbers as (i.e. 11476) and another field in a second row has an ID number (i.e. 11476b)

11476 is not the same as  11476b
0

LVL 119

Expert Comment

ID: 37837075
change this

HAVING (((Val([tblVendor.txtGrantNumber])) Between "10125" And "12577")

with

HAVING (((Val([tblVendor.txtGrantNumber])) Between 10125 And 12577)    '< remove the quotes

the Val() function converts the text number data type to  Number
0

Author Comment

ID: 37839218
I'm having trouble converting your sql statement for my actual database. The field names are txtGrantNumber, dteBudgetBeginDate and dteBudgetEndDate from tblAward Data.
0

Author Comment

ID: 37847275
I got the qry working, but I don't know how to pick up the two expended amounts associated with each of the GrantID numbers. So, I need to add the amount that goes with 11476 to the amount that goes with 11476b to get a total amount expended.
0

Author Comment

ID: 37847569
Just so you understand what I'm talking about:

Grant ID     Pymt1  Pymt2  Pymt3    Expended
11476           100       50       60           210
11476b           80       90     100           270

I need total expended for 11476 expended amount + 11476b expended amount = 480.
0

LVL 119

Expert Comment

ID: 37847657
Barbara69,

<How do I combine the dates, so the result will be 6/1/11-5/31/12 using sql?>

i believe this is already been resolved..

we can't go on resolving all your problems one at a time in a single thread.

0