Search results for query: *

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...

I just added a Microsoft StatusBar Control (ActiveX Control version 6.0 (class MSComctlLib.SBarCtrl.2) to the bottom of 1 of my forms. How would I go about getting traffic light picture icons and paint the status bar red, yellow or green. I wanted to make the status bar green for informational...

May I pose another Listbox question: I have a multiselect listbox named lstProperty Let's say the user can select from among items 1, 2, 3, and 4 in this list box named lstProperty. Item 1 has the value CASH Item 2 has the value BONDS Item 3 has the value IRAS Item 4 has the value STOCKS I...

Suppose you want to provide a user with a list box in which the choices are the current year, up to 10 prior years and up to 10 future years. In other words, the choices in the list box should be 2015, 2014, 2013, 2012, 2011, 2010, 2009, 2008, 2007, 2006, 2005, 2004, 2003, 2002, 2001, 1999...

Thanks PHV, the following code works great. How would I interpret the following string variable which is storing part of an entire SQL statement (written in Access VBA) ? strProdFall = " (((C.DateLost)<=DateSerial(" &...

I have created a Dynamic SQL query in concatenated strings. The first concatenation creates the SELECT ... FROM ... WHERE part of a SQL statement. I am having trouble with the second string concatenation. It consists of the following statement which gives me a syntax error. I can execute this...