Query Help Please

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

Hi,

I am using MS Access 2010 I have this query.

SELECT [Soccer Regional].[Vendor Name], [Soccer Regional].[Invoice
Number], [Soccer Regional].[Invoice Date], [Soccer Regional].[Player
Name], [Soccer Regional].[Invoice Total], [invoice total]*0.045 AS
State, [Invoice Total]*0.0325 AS City, [invoice total]*0 AS County,
[state]+[city]+[county] AS Total, IIf([Ball Type]="fine",([Total]*0),
([Total]*0.0225)) AS Discount, [total]-[discount] AS Refund, [Soccer
Regional].[Claim Number], [Soccer Regional].[Permit Number]
FROM [Soccer Regional]
WHERE ((([Soccer Regional].[Player Name]) Not Like "*withdrawn*" And
([Soccer Regional].[Player Name]) Not Like "*denied*") AND (([Soccer
Regional].[Claim Number])="12-45E"))
ORDER BY [Soccer Regional].[Invoice Date];

Now I want to change the Total*0.0225 to calculate using the rate
2.25% only when the [Soccer Regional].[Invoice Date] is between
1/1/2000 thru 6/30/2010 and to calculate using the rate of 1.00% when
the [Soccer Regional].[Invoice Date] is after 7/1/2010.
 
Change this calculated field:

IIf([Ball Type]="fine",([Total]*0),
([Total]*0.0225)) AS Discount

to this:

IIf([Ball Type]="fine",[Total]*0,
IIf([Soccer Regional].[Invoice Date] <= #6/30/2010#,
[Total]*0.0225, [Total]*0.01)) AS Discount
 
Change this calculated field:

IIf([Ball Type]="fine",([Total]*0),
([Total]*0.0225)) AS Discount

to this:

IIf([Ball Type]="fine",[Total]*0,
IIf([Soccer  Regional].[Invoice Date] <= #6/30/2010#,
[Total]*0.0225, [Total]*0.01)) AS Discount

--

        Ken Snellhttp://www.accessmvp.com/KDSnell/




Hi,
Click to expand...
I am using MS Access 2010 I have this query.
Click to expand...
SELECT [Soccer  Regional].[Vendor Name], [Soccer  Regional].[Invoice
Number], [Soccer  Regional].[Invoice Date], [Soccer  Regional].[Player
Name], [Soccer  Regional].[Invoice Total], [invoice total]*0.045 AS
State, [Invoice Total]*0.0325 AS City, [invoice total]*0 AS County,
[state]+[city]+[county] AS Total, IIf([Ball Type]="fine",([Total]*0),
([Total]*0.0225)) AS Discount, [total]-[discount] AS Refund, [Soccer
Regional].[Claim Number], [Soccer  Regional].[Permit Number]
FROM [Soccer  Regional]
WHERE ((([Soccer  Regional].[Player Name]) Not Like "*withdrawn*" And
([Soccer  Regional].[Player Name]) Not Like "*denied*") AND (([Soccer
Regional].[Claim Number])="12-45E"))
ORDER BY [Soccer  Regional].[Invoice Date];
Click to expand...
Now I want to change the Total*0.0225 to calculate using the rate
2.25%  only when the [Soccer  Regional].[Invoice Date] is between
1/1/2000 thru 6/30/2010 and  to calculate using the rate of 1.00% when
the [Soccer  Regional].[Invoice Date] is after  7/1/2010.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Thanks for the help how about the 2nd half:
when
the [Soccer Regional].[Invoice Date] is after 7/1/2010
 
The expression I provided does both "halves". You asked for dates between
1/1/2000 and 6/30/2010, and for dates after or equal to 7/1/2010. So the
expression I provided checks the date to see if it's less than or equal to
6/30/2010 and to use 2.25% if yes, else to use 1.00%.

--

Ken Snell
http://www.accessmvp.com/KDSnell/


Change this calculated field:

IIf([Ball Type]="fine",([Total]*0),
([Total]*0.0225)) AS Discount

to this:

IIf([Ball Type]="fine",[Total]*0,
IIf([Soccer Regional].[Invoice Date] <= #6/30/2010#,
[Total]*0.0225, [Total]*0.01)) AS Discount

--

Ken Snellhttp://www.accessmvp.com/KDSnell/




Hi,
Click to expand...
I am using MS Access 2010 I have this query.
Click to expand...
SELECT [Soccer Regional].[Vendor Name], [Soccer Regional].[Invoice
Number], [Soccer Regional].[Invoice Date], [Soccer Regional].[Player
Name], [Soccer Regional].[Invoice Total], [invoice total]*0.045 AS
State, [Invoice Total]*0.0325 AS City, [invoice total]*0 AS County,
[state]+[city]+[county] AS Total, IIf([Ball Type]="fine",([Total]*0),
([Total]*0.0225)) AS Discount, [total]-[discount] AS Refund, [Soccer
Regional].[Claim Number], [Soccer Regional].[Permit Number]
FROM [Soccer Regional]
WHERE ((([Soccer Regional].[Player Name]) Not Like "*withdrawn*" And
([Soccer Regional].[Player Name]) Not Like "*denied*") AND (([Soccer
Regional].[Claim Number])="12-45E"))
ORDER BY [Soccer Regional].[Invoice Date];
Click to expand...
Now I want to change the Total*0.0225 to calculate using the rate
2.25% only when the [Soccer Regional].[Invoice Date] is between
1/1/2000 thru 6/30/2010 and to calculate using the rate of 1.00% when
the [Soccer Regional].[Invoice Date] is after 7/1/2010.- Hide quoted
text -
Click to expand...

- Show quoted text -
Click to expand...

Thanks for the help how about the 2nd half:
when
the [Soccer Regional].[Invoice Date] is after 7/1/2010
 
Back
Top