Recent content by zimmer9

I use the following code in my Query. WHERE C.DateLost Between DateSerial(Year(Now())-1,Month(Now())+6,1) And DateSerial(Year(Now()),Month(Now()),0) GROUP BY C.CustomerNumber, C.DateLost, "LT6"; WHERE C.DateLost Between DateSerial(Year(Now) - 1, Month(Now), 1) And DateSerial(Year(Now) - 1...

I have a query that will create a report. The SQL statements that comprise this query are a series of 5 Union statements broken out by date ranges. The last part of each of the SQL statements in each of the Unions are as follows: The first union is for a date range within the last 6 months...

PHV, you cease to amaze me. Thanks again. You answered my somewhat convoluted question with clarity.

I created 5 SELECT UNION statements as follows. I created 1 aggregate record per Customer Number to get their total MarketValue/Cash value (the sum of 2 separate fields). The reason I used a Union was because ultimately I will be creating an Aging report that breaks down into different time...

I think I can get either of the following to work: SELECT Sum(qry7.Total) AS SumOfTotal, qry7.CAT1, qry7.CAT2, qry7.CAT3, qry7.CAT4, qry7.CAT5, qry7.CAT6, Count(qry7.CustomerNumber) AS CountOfCustomerNumber FROM qry7 GROUP BY qry7.CAT1, qry7.CAT2, qry7.CAT3, qry7.CAT4, qry7.CAT5, qry7.CAT6...

I also created the wrong 2nd snapshot of my first query. The first query should read as follows: CustNum Date Lost CAT1 CAT2 CAT3 023414 10/01/2004 L2 036983 12/01/2004 L2 047370 11/01/2004 L1 062992 11/01/2004 L1 066173...

Let me clarify one point: the purpose of my second query was to twofold: 1) to consolidate the dollar amounts within each dollar range 2) to provide a count of unique customers within each dollar range.

I ran the following query and got the result set which I will split into 2 sections below: SELECT C.CustomerNumber, C.DateLost, IIf(Sum(Nz(P.MarketValue,0)+Nz(P.CashBalance,0))<1000,"L1") AS CAT1, IIf(Sum(Nz(P.MarketValue,0)+Nz(P.CashBalance,0)) Between 1000 And 9999.99,"L2") AS CAT2...

I run the following query named Query5 and get the results that follow: SELECT C.CustomerNumber, C.DateLost, Sum(Nz(P.MarketValue,0)+Nz(P.CashBalance,0)) AS TOTAL, "LT6" AS AGE FROM tblCustomers AS C INNER JOIN tblProducts AS P ON C.CustomerNumber = P.CustomerNumber WHERE (((C.DateLost) Between...

PHV you receive an honorary knighthood from me. Thanks Sir PHV for the suggested SQL. It was right on the money.

I executed the following query: SELECT tblCustomers.CustomerNumber, tblCustomers.DateLost, , tblProducts.MarketValue, tblProducts.CashBalance FROM tblCustomers INNER JOIN tblProducts ON tblCustomers.CustomerNumber = tblProducts.CustomerNumber GROUP BY tblCustomers.CustomerNumber...

Leslie I want to thank you for the detailed explanation. You went the extra mile with an example which reinforced your point. Thanks for your time.

I made the change to use the < sign. The error I get is "You tried to execute a query that does not include the specified expression Date Lost between 8/1/2003 and 1/31/2004 as part of an aggregate function". Do I have to create a subselect ? I guess the problem is that when I try and create...

My goal is to create the following 2 Aging report formats: Report As of 2/16/2005 (Date range is 8/1/2004 to 1/31/05) Less Than 6 Months Old # of Accounts ACCT VALUE ------------------------- ------------- ---------- 100K and Above 50 K up to less than 100K under...

I have 2 listboxes on a form from which the user can make a selection of state codes. I have set up 2 radio buttons adjacent to the listboxes to allow the user to toggle between these 2 listboxes. One of the listboxes sits on top of the other (superimposed) because I wanted to have the listbox...