By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,738 Members | 1,461 Online
Bytes IT Community
+ Ask a Question
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
  1. SELECT [NAV's - ETF Income Adjustments (data)].[Fund Number], Month(Date()) & "/1/" & Year(Date()) AS Begin_Date, Date() AS End_Date
  2. FROM [NAV's - ETF Income Adjustments (data)]
  3. GROUP BY [NAV's - ETF Income Adjustments (data)].[Fund Number], Month(Date()) & "/1/" & Year(Date()), Date();
  4.  
Query 2:

Expand|Select|Wrap|Line Numbers
  1. 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
  2. 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
Share this Question
Share on Google+
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
  1. 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
  1. 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

Expert 100+
P: 296
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:

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:

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];
Try changing the ! to a . in IIf([Date] Between [Begin_Date] And [End_Date],[NAV's - ETF Income Adjustments (data)]![Date]) AS Date_Range
Sep 25 '07 #4

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

Scott Price
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
  1. SELECT [NAV's - ETF Income Adjustments (data)].[Fund Number], [NAV's - ETF Income Adjustments (data)].[Date], [NAV's - ETF Income Adjustments (data)].UNITS
  2. FROM [NAV's - ETF Income Adjustments (data)]
  3. 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

P: 55
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
  1. SELECT [NAV's - ETF Income Adjustments (data)].[Fund Number], [NAV's - ETF Income Adjustments (data)].[Date], [NAV's - ETF Income Adjustments (data)].UNITS
  2. FROM [NAV's - ETF Income Adjustments (data)]
  3. WHERE ([Date] Between (DateSerial(year(Now()), month(Now()), 1)) And Now());

Hey guys....

Well I changed it to this:

Expand|Select|Wrap|Line Numbers
  1. SELECT [NAV's - ETF Income Adjustments (data)].[Fund Number], [NAV's - ETF Income Adjustments (data)].Date, [NAV's - ETF Income Adjustments (data)].UNITS
  2. FROM QryETFDate INNER JOIN [NAV's - ETF Income Adjustments (data)] ON QryETFDate.[Fund Number] = [NAV's - ETF Income Adjustments (data)].[Fund Number]
  3. WHERE ((([NAV's - ETF Income Adjustments (data)].Date) Between [Begin_Date:] And [End_Date]));


HOWEVER, after looking further down I noticed MLcampeua's and his works too. I believe I'd like to go with his that way I don't have to enter in a begin date.

Thank you guys for all your help....



If I may ask one more question:

When appending to a table which already exists in a different layout. Can you transpose the information that you are appending?

Thanks guys!
Sep 25 '07 #9

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

Scott Price
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

Scott Price
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
  1. SELECT [NAV's - ETF Income Adjustments (data)].[Fund Number], [NAV's - ETF Income Adjustments (DATA)].[Date], [NAV's - ETF Income Adjustments (data)].UNITS
  2. FROM [NAV's - ETF Income Adjustments (DATA)]
  3. 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

Post your reply

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