Search results for query: *

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

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 ?

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

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

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

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

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

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

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... ? :)...

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

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

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

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

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

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

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

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