1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access Database Where Clause

Discussion in 'Business Applications' started by sthomp04, Nov 17, 2004.

Thread Status:
Not open for further replies.
  1. sthomp04

    sthomp04 Thread Starter

    Joined:
    Sep 5, 2003
    Messages:
    89
    Trying to open a report with criteria based on 3 input boxes on a form.

    Want to open the report based on a name and 2 dates.

    cboAMName - The name of my combo box where the Account Manager Name is gotten
    txtSD - Text box where the start date is entered
    txtED - Text box where the end date is entered

    I have tried the following combinations without success:

    docmd.OpenReport(rAccountManagerFeedbackReport,acViewNormal,, WHERE (((tTransaction.AccountManagerName)= ("& me.cboAMName &")) AND ((tTransaction.TransactionDate) Between ("& me.txtSD &") And ("& me.txtED &")));
    'Statement
    docmd.OpenReport(rAccountManagerFeedbackReport,acViewNormal,,WHERE (((tTransaction.AccountManagerName)= "& me.cboAMName &") AND ((tTransaction.TransactionDate) Between "& me.txtSD &" And "& me.txtED &"));
    docmd.OpenReport(rAccountManagerFeedbackReport,acViewNormal,,WHERE (((tTransaction.AccountManagerName)= me.cboAMName ) AND ((tTransaction.TransactionDate) Between me.txtSD And me.txtED ));
    docmd.OpenReport(rAccountManagerFeedbackReport,acViewNormal,,WHERE (((tTransaction.AccountManagerName)= 'me.cboAMName' ) AND ((tTransaction.TransactionDate) Between 'me.txtSD' And 'me.txtED' ));
    docmd.OpenReport(rAccountManagerFeedbackReport,acViewNormal,,WHERE (((tTransaction.AccountManagerName)= ("& me.cboAMName &")) AND ((tTransaction.TransactionDate) Between ("& me.txtSD &") And ("& me.txtED &")));


    'Without the WHERE
    docmd.OpenReport(rAccountManagerFeedbackReport,acViewNormal,, (((tTransaction.AccountManagerName)= "& me.cboAMName &") AND ((tTransaction.TransactionDate) Between "& me.txtSD &" And "& me.txtED &"))
    docmd.OpenReport(rAccountManagerFeedbackReport,acViewNormal,, (((tTransaction.AccountManagerName)= me.cboAMName ) AND ((tTransaction.TransactionDate) Between me.txtSD And me.txtED ))
    docmd.OpenReport(rAccountManagerFeedbackReport,acViewNormal,, (((tTransaction.AccountManagerName)= 'me.cboAMName' ) AND ((tTransaction.TransactionDate) Between 'me.txtSD' And 'me.txtED' ))
    docmd.OpenReport(rAccountManagerFeedbackReport,acViewNormal,, (((tTransaction.AccountManagerName)= ("& me.cboAMName &")) AND ((tTransaction.TransactionDate) Between ("& me.txtSD &") And ("& me.txtED &")))


    'Without the WHERE with the ;
    docmd.OpenReport(rAccountManagerFeedbackReport,acViewNormal,, (((tTransaction.AccountManagerName)= "& me.cboAMName &") AND ((tTransaction.TransactionDate) Between "& me.txtSD &" And "& me.txtED &"));
    docmd.OpenReport(rAccountManagerFeedbackReport,acViewNormal,, (((tTransaction.AccountManagerName)= me.cboAMName ) AND ((tTransaction.TransactionDate) Between me.txtSD And me.txtED ));
    docmd.OpenReport(rAccountManagerFeedbackReport,acViewNormal,, (((tTransaction.AccountManagerName)= 'me.cboAMName' ) AND ((tTransaction.TransactionDate) Between 'me.txtSD' And 'me.txtED' ));
    docmd.OpenReport(rAccountManagerFeedbackReport,acViewNormal,, (((tTransaction.AccountManagerName)= ("& me.cboAMName &")) AND ((tTransaction.TransactionDate) Between ("& me.txtSD &") And ("& me.txtED &")));
     
  2. sthomp04

    sthomp04 Thread Starter

    Joined:
    Sep 5, 2003
    Messages:
    89
    Ok, I got the first part of the where clause working but cant get the second part working

    Here is the code I currently have:

    part1 = "([qry_Transaction_Defect_Report]![AccountManagerName]=" & Chr(34) & Me.cboAMName & Chr(34) & " )"

    part2 = "AND ([qry_Transaction_Defect_Report]![TransactionDate]) BETWEEN (" & Chr(34) & Me.txtSD & Chr(34) & ") AND (" & Chr(34) & Me.txtED & Chr(34) & ")"

    whereclause = part1 & part2

    DoCmd.OpenReport "rAccount Manager Feedback Report", acPreview, "", whereclause


    Here is the error message I am recieving:

    Run-time error '3071':
    This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.
     
  3. sthomp04

    sthomp04 Thread Starter

    Joined:
    Sep 5, 2003
    Messages:
    89
    Ok, finally got it to work.

    Problem solved!

    In case anyone is curious, here is the final code:


    Dim whereclause As String
    Dim part1 As String
    Dim part2 As String

    part1 = "([qry_Transaction_Defect_Report]![AccountManagerName]=" & Chr(34) & Me.cboAMName & Chr(34) & " )"

    part2 = "AND ([qry_Transaction_Defect_Report]![TransactionDate]) BETWEEN #" & Me.txtSD & "# AND #" & Me.txtED & "#"

    whereclause = part1 & part2

    DoCmd.OpenReport "rAccount Manager Feedback Report", acPreview, "", whereclause
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/297373

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice