Help with query using multiple variables

Current query fields:
Account, SumPrincPmts, Transactions, PostDate

I am needing to run a query from a form (form name: frmbuyout) that
places the results in a report (Test). I need this query to Filter my
results by date and where the Principal pmts (are listed in table as a
LTD Sum) are less than the Total Transactions by account(currently in
the table the transactions are listed individually and not a sum of).
With the code below I can filter the information by date but I am
having trouble finding a way to also filter by pmts value being less
than the sum of the total transactions (this will need to be
determined by acct). I truly hope that this is not completely
confusing. Thank you in advance for any and all help.




Private Sub Command4_Click()
  Dim strReport As String     'Name of report to open.
    Dim strField As String      'Name of your date field.
    Dim strWhere As String      'Where condition for OpenReport.
    Const conDateFormat = "\#mm\/dd\/yyyy\#"


    strReport = "Test"
    strField = "PostDate"
    strWhere = "("


        If IsNull(Me.txtStartDate) Then
            If Not IsNull(Me.txtEndDate) Then   'End date, but no
start.
                strWhere = strWhere & strField & " < " &
Format(Me.txtEndDate, conDateFormat)
            End If
        Else
            If IsNull(Me.txtEndDate) Then       'Start date, but no
End.
            strWhere = strWhere & strField & " > " &
Format(Me.txtStartDate, conDateFormat)
         Else                                'Both start and end
dates.
            strWhere = strWhere & strField & " Between " &
Format(Me.txtStartDate, conDateFormat) _
                & " And " & Format(Me.txtEndDate, conDateFormat)
            End If
            End If
    strWhere = strWhere & ")"
    strWhere = strWhere



    DoCmd.OpenReport strReport, acViewPreview, , strWhere

End Sub

0
sallen
4/20/2007 9:10:18 PM
access.queries 6343 articles. 1 followers. Follow

0 Replies
582 Views

Similar Articles

[PageSpeed] 41

Reply:

Similar Artilces:

Conditional Comment using Cell value as part of it
What I'm trying to do is the following: if cell(a1) is different from 0(zero) then, cell(b1) should have a comment that say, the value of cell(c1) is the payment, next line the value of cell(a1) was refund, next line Total for today = cell(c1)-cell(a1) Example a1 = $10.00 c1 = $30.00 B1 (comment): $30.00 is the payment (c1) $10.00 was refund (a1) Total for today: $20.00 (c1-a1) can anyone help me on that? thanks in advance ...

Why is .NET 2.0 required to deploy an app using VC2005 C++ Setup project?
I just created a setup project for my mfc app. I noticed that in the prerequisites section the .NET 2.0 is checked by default. My app does not need the framework (at least this is what I think). If I clear the checkbox, build the project, and then try to deploy it on a machine with ..NET 1.1 the installer fails with a message complaining about missing .NET 2.0. I'm not sure if this the installer that needs the framework or my app. Are there any mfc8 dlls dependant on the framework? Thanks Hi, > I just created a setup project for my mfc app. I noticed that in the > p...

How to create report from Pivot Table View of Union Query?
Hi all, I have create a Union ALL Query. I want to generate a report from it. The Pivot Table View of this Union Query shows exactly what I want (having Row Heading, Column Heading and Detailed Data), so how can I make it as a Report? I have tried to create new report, but there is no options of making a pivot table (something like Crosstab Query and Pivot Table View of the Union Query). Also tried to export to Excel, but it shows only count of records. I want all detailed records. Thank you. -- Message posted via http://www.accessmonster.com Hi Duane, I tried to create a crosstab qu...

Outlook 2003 opens multiple instances
When I double click the desktop icon for outlook it opens twice or has two instances available in the task manager window. Once I disable or end these and reopen outlook it is fine. Also, we are currently running Viewmail and Viewfax from Active Voice. Any help would be grealty appreciated. Charles Garcia wrote: > When I double click the desktop icon for outlook it opens > twice or has two instances available in the task manager > window. Once I disable or end these and reopen outlook it > is fine. This happens randomly for me too. In fact, for a long time it used to a...

Multiple workbooks open -- closing workbooks
Dear Clever People, I hope this is not the "silly question of the day". We were wondering (when you have multiple workbooks open and you close one workbook) why are all other workbooks forced to be closed, too? Is it possible to ever close one workbook and leave all the others open? Thank you, Janet Close it by going to file>close or by pressing Ctrl + F4 or by clicking the smaller X below the big red X -- Regards, Peo Sjoblom "Janet Panighetti" <JanetPanighetti@discussions.microsoft.com> wrote in message news:1C9BC30B-BE1A-4605-B215-99C07CB1D366...

Extended Pricing price groups should allow multiple Units of Meas.
We categorize our items without regard to units of measure, and want to make an overall change to a particular category of items, e.g. increase the discount percent for all items in that category. But we cannot group these items into one price group because they have different units of measure. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft ...

Need one help
Dear Sir/Madam, I need some help that I am working in cousulting firm and we providing solution to clinet as web base and mail base. We are microsoft ceritfied partner and we using win2k3 ent. server on this server exchange2k3 and other is win2k3 with IMail. Till upto now we are proving clients mail solution on Imail base but now we are planing to charge more for mail solution and those are interested to pay then we give them exchange mail solution. Now I having one question in my mind is that in IMail I create diffrent diffrent domain for different web site according to clients. Now o...

Installing CRM3, multiple errors
I have installed CRM3 on a clean SBS2003 machine (under VirtualPC), but have got a series of errors and cannot now acess CRM at all. I followed the steps in the Implmentation Guide. I had to change the web site port to be 80 and the alias to be able to access it (as crm3demo). I installed CRM3 under Administrator login and was initially able to open CRM correctly, login as Administrator, go into the Administrator user and uncheck the restricted access check box. Everything appeared normal. Then I set up the sample data users in Active Directory and tried installing the sample data by ...

please help #8
I am trying to switch info from one XP machine running office 2000 to another XP machine running office 2000 and I have a ton of Macros. How do I successfully move these Macros in excel, so I don't have to recreate them? Hi if you have stored your macros in your personal.xls just copy thie file to your new machine -- Regards Frank Kabel Frankfurt, Germany Bob Garber wrote: > I am trying to switch info from one XP machine running > office 2000 to another XP machine running office 2000 and > I have a ton of Macros. How do I successfully move these > Macros in excel, so I...

Varying colors on a line chart with multiple series
In Excel 2003, is it possible to change the color of part of a line on a chart with multiple data series? I can do it on a single series chart. Hi, Yes, you would need to select the segment of the line and then format it. So select the series pause and then select the segment apply formatting Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "LKP" <LKP@discussions.microsoft.com> wrote in message news:CB5CF5B8-5E3C-4288-A7B1-63958DB0C688@microsoft.com... > In Excel 2003, is it possible to change the color of part of a line on a > chart with ...

Thanks for all the help on Year-to-date query!
It's so great to know that there are folks out there that can take the time to help someone whose 78 year old brain is slowly turning to gorgonzola cheese! Again, thanX Mikie Francisco 9 @ ATT N E T ...

Query from Multiple Workbooks
Hello, I have 12 files in a directory with a sheet name Sales containing Sales data for each month having more than half a million rows in each workbook sheets The field names are all the same in all the files like date, item, itemgroup, branch, unit, salesman, amount etc I am looking for a way to get a query from all these workbook and get a summarised report based on criteria like sales by salesman, by month, by product, by product group, by date, by branch etc. Is this will be possible through code?. Itry to use MS query but not successful Using Excel 2007 and Win XP ...

How to use Find (Ctrl-F) to find non-alpha
Is there some way to search a sheet for anything BUT a-z or A-Z? tx ;-) Finding numerals or a mixture of numbers and letters (and other characters) works ok for me. What are you having trouble with? On 10/22/2010 16:31, Heather Mills wrote: > Is there some way to search a sheet for anything BUT a-z or A-Z? > > tx ;-) -- Dave Peterson On Fri, 22 Oct 2010 17:54:28 -0500, Dave Peterson <petersod@XSPAMverizon.net> wrote: >Finding numerals or a mixture of numbers and letters (and other characters) >works ok for me. > >What are you having trouble with? > >...

VC++ 2005
I am attempting to compile a project in Visual Studio 2005 which compiles and links properly in VC++ 6.0. It seems to have problems with C++ i/o functions. Here is the error I get: g:\src\analyzer\digitdialpages.h(136) : error C2146: syntax error : missing ';' before identifier 'm_InFile' Line 136: ifstream m_InFile; The statment: include <fstream> is at the top of the source file. Some assistance would be helpful. We didn't have any problems with using ifstream or ofstream in VC++ 6.0. Thanks. On Tue, 12 Dec 2006 18:22:47 -0500, "Raj Kulkarni...

Query to Redistribute a Table
Hi I am struggling with how to query a table with the current structure: Field1 Field2 ...Field14 Jan Feb Mar Apr... x y ...z 10 15 13 16... etc, It represents sales per month to customers per delivery adress, product and so forth.The numbers under each month respresents the sales for that month. I need to be able to query so I can see all detalis (ie fileds 1-14) per row per month, ie redistribute that original table so I can use month as a field and still use all the original fields. My new table needs to be something link Month Field 1 Field2 ... Field14 New ...

How do I create custom border using peoples names?
There are many it could be done. I'd set up text boxes and go from there. -- JoAnn Paules MVP Microsoft [Publisher] "Val" <Val@discussions.microsoft.com> wrote in message news:BFF16BB3-4C90-4E05-B0AA-303874A445AA@microsoft.com... > In addition to JoAnn's reply, you could use WordArt as well. WordArt allows you the opportunity to rotate, flip, arrange etc. and resize as well as save as an image. -- Brian Kvalheim Microsoft Publisher MVP http://www.publishermvps.com This posting is provided "AS IS" with no warranties, and confers no rights. &...

Help with mutiple profiles / accounts
Hi there, I have multiple different pop3 accounts i would like to use from 1 profile, is this possible? Please help. I have 3 different pop 3 accounts which i have setup. I also have 3 different data folders that the mail from each account drops into, this works fine. However, how do I set it so that when I reply to an email from 'pop1' it uses the pop1 smtp server and account details and when I reply or create emails for 'pop2' it uses pop2 details (the same for pop3). This was possible in outlook express, in the email window, there was a simply a drop down from which...

Exchange Server Problem (I need help)
Hello to you all! The medical clinic I work at has a network. We all have our own email address. Mine, for example is lpeacock@elcardiologist.com. The problem is this. I can send out email as much as I want and they go where I need them to go. However, when anyone attempts to send any of us email, to our work, we don't get them. Now we had experianced some MAJOR problems recently in the past to where the actual IT lady had to set everything back up because things just went down. So our email accounts worked without any problems. But now, we can't recieve anything from an...

OL2003 Comining Multiple Contacts
As I mentioned in another post, I'm playing around with Outlook. Here's my Contacts problem. I was using Outlook at work and synchronizing my Contacts with my Palm Pilot. So that part of the transition was a breeze. But I was using a completely different email program at work which has it's own address book. I know how to import that address book into Outlook, but the end result will include be a whole bunch of partial duplicate entries where say, I have name and address information in the old Palm/Outlook database but only an email address in the email address book. Any sugge...

Help with array formula
I need help with an array formula, I have to columns, I need to find the min, the max and average form the two columns added, but I cant create a new column. E.g.; name s1 s2 John 1 1 Peter 3 5 Frank 6 3 min= 2 ( John) Max= 9 (Frank) average= 6.33 Thanks One way (array entered): =MIN(B2:B4+C2:C4) =MAX(B2:B4+C2:C4) =AVERAGE(B2:B4+C2:C4) In article <1164729405.003619.272780@j72g2000cwa.googlegroups.com>, GastonFranzini@gmail.com wrote: > I need help with an array formula, > I have to columns, I need to find ...

Help On User Form
Hi All, Can any one help me in this .I have made a orderform with some combobox and tickboxes a simple form i want to collect the data at the end of the sheet .Like when i select certain item from drop down box is there away that data can be collected at the end of the sheet.I Many Thanks in Advance Mastee -- mastee ------------------------------------------------------------------------ mastee's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=23826 View this thread: http://www.excelforum.com/showthread.php?threadid=398070 Good morning Mastee You really ne...

Exchange 2003 And Multiple Managers
We need to deploy our organisation to allow for some users to have multiple Managers. For example, a secretary can report to a number of different Managers depending on what work they are performing at the time. Any help on how this would be achieved would be greatly appreciated. "Davie" <dxw@mms.co.uk> wrote: >We need to deploy our organisation to allow for some users to have multiple >Managers. For example, a secretary can report to a number of different >Managers depending on what work they are performing at the time. > >Any help on how this would ...

opening multiple blank documents on start-up
Hi, I'm trying to figure out how I can open multiple blank documents on my (MDI) application's startup. (using VC++ 6.0) I've looked at the commandline info stuff, but it looks like I can only tell it to do one FileNew. Basically I just want, say, n new documents created (along with their views, presumably) when I start my program. Anyone have any ideas on how I might do this, or can suggest where I should look for answers? thanks! Brian In principle: make the doc template a member object in your app.h CMultiDocTemplate* m_pMyDocTemplate; use this pointer in the .cpp temp...

Update Query ctnd:
Hello, I have tried the code from Arvin however I now get 2 paramater prompts. The 1st is for "transactions.Datefield" and the 2nd "Please enter Waiting or Pending" UPDATE transactions SET transactions.Status = "Cleared" WHERE (((transactions.datefield)<Date()-10) And ((transactions.Status)=[Please enter Waiting or Pending])); Can I run this procedure atomatically when the database opens without the prompts & user input Thanks The first prompt implies that datefield isn't the actual name of the field in your transactions table. The second prompt...

Query All records except latest
Access 2003 I need to limit my query to ALL RECORDS EXCEPT the most current record. Any ideas on how to do this -- deb Deb - It will look something like this, assuming no more than one record per date per key field. You can adjust to use date/time if that is needed. It depends on what makes your records unique. Try something like this, substituting your table and fieldnames... Select myKeyfield, field1, field2, field3, myDate FROM myTable Where myDate < (Select max(myDate) from myTable AS T2 WHERE T2.myKeyfield = myTable.myKeyfield) -- Daryl S "d...