Combine dates from two fields

SELECT [BPB] &"-" & [BPE] AS CombinedDates
FROM YourTable

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..

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?

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]);

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


Please see attached.

where is the attached ?

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

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

<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

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

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

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.

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.

Barbara69,

just making it clear, youask in your original post

<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.
so, please post another question regarding your other problem.

please follow the guideline of one question per thread..