Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search results for query: *

  • Users: zimmer9
  • Order by date
  1. zimmer9

    Dynamic SQL ?

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

    Dynamic SQL ?

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

    SQL UNION

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

    SQL UNION

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

    Problem with Count in SQL/Distinct Count

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

    Problem with Count in SQL/Distinct Count

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

    Problem with Count in SQL/Distinct Count

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

    Problem with Count in SQL/Distinct Count

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

    Query vs. Subquery

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

    SQL Aggregate function

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

    SQL Aggregate function

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

    SQL STATEMENT

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

    SQL STATEMENT

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

    SQL STATEMENT

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

    Multiselect Listbox Potential Error

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

    Status Bar traffic lights

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

    Listbox Creation

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

    Listbox Creation

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

    Date variable

    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(" &...
  20. zimmer9

    Date variable

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

Part and Inventory Search

Back
Top