Query Headache 1

[mattfox]

I am in the process of creating an inventory database.&nbsp;&nbsp;The kicker is that certain things get a certain amount of free time before storage charges begin.&nbsp;&nbsp;I put this expression in the query to show the number of days we have had the item: # of Days: (Date()-[Date]).&nbsp;&nbsp;[Date] is the date we recieved it.&nbsp;&nbsp;This works fine.&nbsp;&nbsp;Now we have a customer that wants the storage to show on the daily inventory report.&nbsp;&nbsp;Here is the criteria:<br>Item1 - 5 free days $5 a day after that<br>Item2 - 30 free days $0.70 a day after that<br>Item3 - 30 free days $0.50 a day after that.<br><br>Here is the IIf statement that I wrote :<br>Item1 Ch: IIf(([Empty/Loaded]=&quot;L&quot And ([# of Days]&gt;5),5,0)<br>Item2 Ch: IIf(([Empty/Loaded]=&quot;E&quot And ([# of Days]&gt;30),0.70,0)<br>Item3 Ch: IIf(([Empty/Loaded]=&quot;&quot And ([# of Days]&gt;30),0.50,0)<br>All of that works fine, but I need to have totals on the report.<br><br>Here is the IIf statement that I wrote :<br>Total Item1 Ch: Sum(IIf(([Empty/Loaded]=&quot;L&quot And ([# of Days]&gt;5),5,0))<br><br>I get this error when I try to run the query :<br>You tried to execute a query that doesn't include the specified expression 'Date' as part of an aggregate function.<br><br>I have no clue what I am doing wrong.&nbsp;&nbsp;If somebody knows something please enlighten me.<br><br><br>Thank You!!<br><br>

 

[famousb]

first of all, if you're doing $5/day for every day over 5, wouldn't your IIf statement need to incorporate that?<br>ie.&nbsp;&nbsp;<b>IIf(([Empty/Loaded]=&quot;L&quot And ([# of Days]&gt;5),5*([# of Days]-5),0))</b><br><br>then to Sum all the Item1 Ch's on a report, the shouldn't&nbsp;&nbsp;formula just be:<br><b><br>Total Item1 Ch: Sum([Item1 Ch])<br></b><br>you don't need the IIf statement because the Item1 Ch's that have no charge will still equal 0. <p>Brian Famous<br><a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a><br><a href= > </a><br>

 

[mattfox]

Thanks for the response.&nbsp;&nbsp;I do not know why I did not see that logic.<br>I still get this error :<br>You tried to execute a query that doesn't include the specified expression 'Date' as part of an aggregate function.<br>

 

[famousb]

maybe try this:<br>First, change your field [Date] to [RecdDate] (or something like that) - sometimes i think Access gets confused on things like that.<br>Next, Change the formula for [# of days] to <br>=DateDiff(&quot;d&quot;, Date(), RecdDate)<br><br>also, out of curiousity, are any of your charges business day sensitive (ie. they don't get charged on Saturdays or Sundays)?&nbsp;&nbsp;this can change alot of $$$ amounts. <p>Brian Famous<br><a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a><br><a href= > </a><br>

 

[elizabeth]

Not an answer to your error but just a maintenence idea. You might want to create a small table for these values such as :<br><br>tblRate: ItemID, FreeDays, DailyRate<br><br>Then you could avoid hardcoding those values in your query, making your app easier to maintain.

 

[mattfox]

Well changed all of that stuff and now I get this Error: <br><br>You tried to execute a query that doesn't include the specified expression 'Container #' as part of an aggregate function.<br><br>'Container #' is another field name.&nbsp;&nbsp;What is going on?<br><br>

 

[famousb]

If 'Container #' is to be included, it must be included in brackets ( ie.&nbsp;&nbsp;[Container #]) since it has a space and a symbol in the name (these are other things to avoid in Access for ease of programming.<br><br>try that and see what happens. or try changing the actual field name to [ContainerNo] <p>Brian Famous<br><a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a><br><a href= > </a><br>

 

[mattfox]

Contianer # has nothing to do with the formula, it is a separate field that needs to be listed with the queried information.

 

[famousb]

is your IIf statement with the Sum function in the same query?<br>it would be asking for an aggregate value, and would mess up the rest of the query. <p>Brian Famous<br><a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a><br><a href= > </a><br>

 

[mattfox]

Yes.&nbsp;&nbsp;What can I do to make it not mess up the query?

 

[famousb]

take it out, and put it in an unbound field on the report itself. <p>Brian Famous<br><a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a><br><a href= > </a><br>

 

[mattfox]

Saved my life!!!<br><br>Thank You!!

 

[famousb]

i hope it all works out for you. let us know if you need anything else. <p>Brian Famous<br><a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a><br><a href= > </a><br>