459,738 Members | 1,461 Online
Need help? Post your question and get tips & solutions from a community of 459,738 IT Pros & Developers. It's quick & easy.

# Need Help with: Date Parameter Queries

 P: 55 I have two queries. My goal is to pull a range of numbers that fall on or in a certain date range. For example, \$100 on 9/1/2007 and \$200 on 9/24/2007. I want the date range to pick up both the 100 and 200 dollars. From there I will sum them. The key is I only want current month. The following SQL is already written for both Queries. My issue is in the second query I receive #error. Query 1: Expand|Select|Wrap|Line Numbers SELECT [NAV's - ETF Income Adjustments (data)].[Fund Number], Month(Date()) & "/1/" & Year(Date()) AS Begin_Date, Date() AS End_Date FROM [NAV's - ETF Income Adjustments (data)] GROUP BY [NAV's - ETF Income Adjustments (data)].[Fund Number], Month(Date()) & "/1/" & Year(Date()), Date();   Query 2: Expand|Select|Wrap|Line Numbers SELECT [NAV's - ETF Income Adjustments (data)].[Fund Number], IIf([Date] Between [Begin_Date] And [End_Date],[NAV's - ETF Income Adjustments (data)]![Date]) AS Date_Range, [NAV's - ETF Income Adjustments (data)].UNITS FROM QryETFDate INNER JOIN [NAV's - ETF Income Adjustments (data)] ON QryETFDate.[Fund Number] = [NAV's - ETF Income Adjustments (data)].[Fund Number]; Sep 25 '07 #1
19 Replies

 Expert 100+ P: 218 First thought - it looks as if you are mixing SQL and VBA syntax with the "IIF" function Expand|Select|Wrap|Line Numbers SELECT [NAV's - ETF Income Adjustments (data)].[Fund Number], IIf([Date] Between [Begin_Date] And [End_Date],[NAV's - ETF Income Adjustments (data)]![Date]) AS Date_Range Is this what you intended? Steve Sep 25 '07 #2

 P: 55 First thought - it looks as if you are mixing SQL and VBA syntax with the "IIF" function Expand|Select|Wrap|Line Numbers SELECT [NAV's - ETF Income Adjustments (data)].[Fund Number], IIf([Date] Between [Begin_Date] And [End_Date],[NAV's - ETF Income Adjustments (data)]![Date]) AS Date_Range Is this what you intended? Steve thanks Steve for your reply. It is my understanding that IIF is SQL and IF is VBA. Is this not correct? The only thing I can come up with is that my second query doesn't reference Begin_ Date as an actual date even though when you run the query it shows up as a date. Sep 25 '07 #3

 P: 55 Try changing the ! to a . in IIf([Date] Between [Begin_Date] And [End_Date],[NAV's - ETF Income Adjustments (data)]![Date]) AS Date_Range Thanks...I tried that but no such luck getting it to give me dates instead of #errors. Sep 25 '07 #5

 Expert 100+ P: 1,384 First of all, IIF is equally applicable in VBA as in SQL, for the differences in the function between VBA and SQL see this link: http://www.thescripts.com/forum/thread611331.html. Second, instead of putting your date criteria in an iif statement, why don't you instead use it as a parameterized query, using the date criteria in the WHERE criteria of your sql? I.e.: SELECT ... FROM ... HAVING [table].[datefield] BETWEEN [Begin_Date] And [End_Date] Regards, Scott Sep 25 '07 #6

 Expert 100+ P: 296 What if you were to try combining this into one query? And just because it looks like a date in your first query, doesn't mean Begin_Date is one. Try using the DateSerial() function instead. Using the IIF in the Select statement won't filter out all results that you aren't looking for. In order to filter the results, you need to put the criteria in the WHERE clause. Try something like this: (I haven't tested this code) Expand|Select|Wrap|Line Numbers SELECT [NAV's - ETF Income Adjustments (data)].[Fund Number], [NAV's - ETF Income Adjustments (data)].[Date], [NAV's - ETF Income Adjustments (data)].UNITS FROM [NAV's - ETF Income Adjustments (data)] WHERE ([Date] Between (DateSerial(year(Now()), month(Now()), 1)) And Now()); Sep 25 '07 #7

 Expert 100+ P: 296 Looks like I was a little slow on posting...Scott your reply wasn't there when I started replying but looks like we're somewhat on the same page Sep 25 '07 #8

 Expert 100+ P: 296 You da man!!! That works like a charm. Thank you so very much. I'm glad it worked for you! Michelle Sep 25 '07 #10

 P: 55 I'm glad it worked for you! Michelle sorry didn't know you were a lady!! : ) Sep 25 '07 #11

 Expert 100+ P: 296 sorry didn't know you were a lady!! : ) Not a problem! Hard to tell with some people's user names. As for appending to tables, I'm still rather new to Access and don't have experience in that area, so maybe Scott or someone else will have an idea for you. Sep 25 '07 #12

 Expert 100+ P: 1,384 Looks like I was a little slow on posting...Scott your reply wasn't there when I started replying but looks like we're somewhat on the same page Michelle, you are quite welcome to post at any time! Glad we're on the same page, too :-) For the append operation, a little more information is needed. What are the table structures, and what are you trying to append? Regards, Scott Sep 25 '07 #13

 P: 55 Michelle, you are quite welcome to post at any time! Glad we're on the same page, too :-) For the append operation, a little more information is needed. What are the table structures, and what are you trying to append? Regards, Scott Scott, The table Structure looks like this: Entry Date Channel 99 Channel 132 (Headings) 5/1/2007 \$200,000 \$300 5/1/2007 \$200,000 \$300 The Qry Data which i will be appending: Channel Entry Date Total Units (Headings) 99 9/26/2007 \$200,000 132 9/26/2007 \$300 Sep 25 '07 #14

 Expert 100+ P: 296 Scott, The table Structure looks like this: Entry Date Channel 99 Channel 132 (Headings) 5/1/2007 \$200,000 \$300 5/1/2007 \$200,000 \$300 The Qry Data which i will be appending: Channel Entry Date Total Units (Headings) 99 9/26/2007 \$200,000 132 9/26/2007 \$300 Are there more headings in the first table structure? The example you gave is redundent data. It might make more sense to have your table structure as: Entry Date Channel Units 5/1/2007 99 \$200,000 5/1/2007 132 \$300 It seems as if it would be easier to append your query results this way, as well. Sep 25 '07 #15

 P: 55 Are there more headings in the first table structure? The example you gave is redundent data. It might make more sense to have your table structure as: Entry Date Channel Units 5/1/2007 99 \$200,000 5/1/2007 132 \$300 It seems as if it would be easier to append your query results this way, as well. I will most likely just change the table so that they are alike. I just was curious if this is attainable. Thanks for all your help. Adam Sep 25 '07 #16

 Expert 100+ P: 1,384 I will most likely just change the table so that they are alike. I just was curious if this is attainable. Thanks for all your help. Adam Hi Adam, The second structure that you posted is the more sound logically speaking. When making changes keep in mind the database normalisation rules. (See: http://www.thescripts.com/forum/thread585228.html ) This would be possible, but would require some fairly extensive code to open the first recordset, then re-order the records to conform to the second. Regards, Scott Sep 25 '07 #17

 P: 55 Hi Adam, The second structure that you posted is the more sound logically speaking. When making changes keep in mind the database normalisation rules. (See: http://www.thescripts.com/forum/thread585228.html ) This would be possible, but would require some fairly extensive code to open the first recordset, then re-order the records to conform to the second. Regards, Scott thanks scott I will read it tonight. Again appreciate all the help. adam Sep 25 '07 #18

 P: 55 Another question pertaining to Date's, but this time adding a calculation: First, I am trying to have a running Year To Date Calculation. Earlier I wanted to pull a range in the year. Would the logic change if I wanted to sum an entire year?? Secondly, I have a query which is appending data amounts to a table. Right now, today's numbers will be used for tomorrow. Where my issue lies is when the new month begins I need to take prior day's amount add it to current days amount and append the total. This is what I haev so far. SELECT QryETFDateRange.[Fund Number], Date()+1 AS [Compass Entry Date], Sum(QryETFDateRange.UNITS) AS [Total Units] FROM QryETFDateRange GROUP BY QryETFDateRange.[Fund Number], Date()+1; thanks Adam. Sep 25 '07 #19

 Expert 100+ P: 296 Another question pertaining to Date's, but this time adding a calculation: First, I am trying to have a running Year To Date Calculation. Earlier I wanted to pull a range in the year. Would the logic change if I wanted to sum an entire year?? The logic would stay the same. Just change the DateSerial() function: Expand|Select|Wrap|Line Numbers SELECT [NAV's - ETF Income Adjustments (data)].[Fund Number], [NAV's - ETF Income Adjustments (DATA)].[Date], [NAV's - ETF Income Adjustments (data)].UNITS FROM [NAV's - ETF Income Adjustments (DATA)] WHERE ([Date] BETWEEN (DateSerial(year(Now()), 1, 1)) AND Now()); I will have to look at your second question a little later. I'm about to go into a meeting. Sep 25 '07 #20