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 derfloh 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: *

  1. WillRiley

    Sorting items ListBox

    Here's an example Sub Adv_Filter() Dim lLastRow As Long Sheet3.Range("B1:B" & Range("B65536") _ .End(xlUp).Row).ClearContents lLastRow = Sheet4.Range("B65536").End(xlUp).Row Sheet4.Range("B1:B" & lLastRow&) _ .AdvancedFilter xlFilterCopy, , Sheet3.Range("B1"), True Sheet3.Range("B2:B" &...
  2. WillRiley

    Sorting items ListBox

    Why not use the advanced filter to copy unique values from colB to another location in the workbook & use that sorted range as your listbox source ?
  3. WillRiley

    MS Query SQL problem

    This works for me SELECT cust.so, Count(cust.no) FROM pdunity.dbo.cust cust WHERE (cust.status<>'active') GROUP BY cust.so Do you have your aggregate functions set up correctly... Do you get the right number of records if you exclude the GROUP BY statement? Will
  4. WillRiley

    Selecting only the maximum values

    Cheers, Where do I place the subquery SQL expression? Sorry if that's a bit basic... Will
  5. WillRiley

    Selecting only the maximum values

    Sorry guys.... It was my syntax... I forgot the bit that said FROM gchg AS AKA The following now works in SQL & Crystal SELECT gchg."effective", gchg."id" FROM "pdunity"."dbo"."gchg" gchg WHERE gchg."effective" = ( SELECT MAX(AKA."effective") FROM gchg AS AKA WHERE...
  6. WillRiley

    Selecting only the maximum values

    Hmmmm, Strangely enough it works in SQL Analyzer SELECT gchg.effective, gchg.id FROM gchg WHERE gchg.effective = (SELECT MAX(AKA.effective) FROM gchg AKA WHERE AKA.id = gchg.id) ORDER BY id ASC, effective DESC which is cool.... not sure what is wrong with my Crystal syntax...
  7. WillRiley

    Selecting only the maximum values

    LB, I am assuming that AKA is a second alias table of Orders... ? Is this right... On that basis I added another alias of gchg, calling it gchg_1 and used what I thjought was the amended SQL following your suggested syntax... SELECT gchg."effective", gchg."id" FROM...
  8. WillRiley

    Selecting only the maximum values

    Dave/Vidru.... Don't want to return all the records & then group them... the results set will be used in another report... hence the need to filter the recordset on selection... thanks anyway Gary, I will explore the view issue & get back tomorrow... Thanks
  9. WillRiley

    Selecting only the maximum values

    Gary, Thanks But if I post your code into the Show SQL query dialog box, it seems to convert it to SELECT gchg."effective", gchg."id" FROM "pdunity"."dbo"."gchg" gchg when I run it.... which suggests that i cannot use Max in the Show SQL Query box.... am I being thick... ? :)...
  10. WillRiley

    Selecting only the maximum values

    GJ Parker, I am on 8.5.... we do have & intend to upgrade to 9 but it won't be for a little while yet... skuhlman, Thanks for the code.... but I can't seem to get the correct SQL syntax for Crystal.... your code may well work in Access, but then so does the code I posted... If I paste your...
  11. WillRiley

    Selecting only the maximum values

    Hi, I have a table with multiple records for each ID. The records all have an effective date & I wish to return only the 75 or so records out of 20,000 that are the most recent for each id... i.e. for each ID, return the record corresponding to the maximum effective date... In access, I...
  12. WillRiley

    Select Case or some such If type statement

    OK, I have a working solution using what I would have liked to avoid... a very lengthy IF statement. If anyone can see a way to make this neater/faster, please jump in, Thanks to anyone who looked at this in the meantime //special step rate calculator If {dstt.avg_bal} >=0 And...
  13. WillRiley

    Nested If in Expression Builder

    Switch is a function available in most reporting tools...Crystal, Access, VBA etc The syntax is Switch(expression1,var1,expression2,var2,....etc) i.e. Switch({dmd.type} <> "credit", (({dstt.avg_bal}*(({?Base}-{@CrRate})/100))/365)*{dstt.num_days}, {dmd.type}="credit"...
  14. WillRiley

    Select Case or some such If type statement

    Hi people, I have the following poser that I could do with some help on. I am trying to match the average balance of an account with a set of "levels" that determine what rate of interest is to be applied to the said account. Trouble I am having is that of the 7 levels, not all of them are...
  15. WillRiley

    Fields not showing Portrait -&gt; Landscape

    My first post for some time.... This is a frustrating little problem... I created a report in with an original page set-up of Portrait, then later changed it to landscape in order to make it look better; however the fields that I have placed in the additional page width given by Landscape do...
  16. WillRiley

    Select Distinct Records

    Please can anyone help me with this one... hopefully an easy one for some of you... My table contains a number of records for the field &quot;groupid&quot; (i.e. a new recod with the same groupid is created whenever one of the other fields gets updated) I am querying a table and want to select...
  17. WillRiley

    Changing the datasource on existing reports

    Graham, Not sure if this will help, but on Crystal 7, you can use the 'set location' function from the 'database' menu option. You can either log on to a different server or choose new data sources by selecting the 'log on server' button. Once you have done this, select 'done' and you should get...

Part and Inventory Search

Back
Top