Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    Join Date
    Apr 2009
    Posts
    4

    Question Unanswered: VBA DoCMD Where Condition

    Hi,
    I am trying to use DoCMD to open a report and use two where conditions. I am having difficulty finding the correct syntax to use. This is what i have:

    Dim strWhereMonth As String
    Dim strWhereYear As String

    strWhereMonth = "[Month] = Forms![Sales View Dialog Box]!SelectMonth"
    strWhereYear = "[Year] = Forms![Sales View Dialog Box]!SelectYear"

    DoCmd.OpenReport "Sales By month", acViewPreview, , strWhereYear And strWhereMonth


    For some reason it does not like the use of my AND. I have tried putting quotation marks here, there and everywhere but can't figure it out. Any insight would be appreciated!

  2. #2
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    If this report ALWAYS references these two values;

    Forms!NameOfFormButtonIsOn.visible = false
    Docmd.OpenReport "Sales By Month", acViewPreview

    Reference the form values in the query behind the report;

    Under the column for [Month] - Forms![Sales View Dialog Box]!SelectMonth
    Under the column for [Year] - Forms![Sales View Dialog Box]!SelectYear

    Then during the report close event, make the form visible again Forms!NameOfFormButtonIsOn.visible = true

    If not, you are better off modifying the query at runtime - there are many examples of this technique elsewhere in this forum.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  3. #3
    Join Date
    Apr 2009
    Posts
    4

    Cool

    Alright i got it working. It seemed to simply be a syntax error. I'm not sure if VBA lets you use more than one string for a where condition, but i got around it by:

    DoCmd.OpenReport "Sales By month", acViewPreview, , "[Month] = Forms![Sales View Dialog Box]!SelectMonth And [Year] = Forms![Sales View Dialog Box]!SelectYear"

    The And has to be within the quotation marks, and if there's more than one field name you need to use boxed parentheses. Does anybody know if there's a site with all these syntax requirements listed?

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    FYI, you can use more than one string in the wherecondition:

    DoCmd.OpenReport "Sales By month", acViewPreview, , strOne & strTwo

    but the concatenation of the two strings would have to evaluate to a syntactically correct clause, such as what you ended up with. Using your first effort, I think this would have worked:

    DoCmd.OpenReport "Sales By month", acViewPreview, , strWhereYear & " And " & strWhereMonth

    because as you pointed out, the AND needed to be inside the quotes. By the way, the square brackets are required because of the inadvisable spaces in the form names, and probably the reserved words used as field names (month and year), not because there is more than one field being tested.
    Paul

  5. #5
    Join Date
    Apr 2009
    Posts
    4
    that seems to make sense but yet when i tried to run it with & " And " & in the middle (just as you had stated) it came back with an error stating that its too complicated to be evaluated

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Actually I just noticed you would also need to do this:

    strWhereMonth = "[Month] = " & Forms![Sales View Dialog Box]!SelectMonth
    Paul

  7. #7
    Join Date
    May 2009
    Posts
    14

    the same problem, help me!

    Hi everybody,

    I have the same problem. I`m trying to run this command but I don`t know how it's doesn't work. Follow below the code:

    Private Sub Command2_Click()

    Dim conta As Double

    Dim Modu As String

    Modu = "[MODULO] = '" & Me.lst_modulo & "'"

    conta = "[Conta_contabil] = '" & Me.txt_Conta_contabil & "'"

    DoCmd.OpenReport "teste_outline 1", acViewPreview, Cons_trans, conta & " and " & Modu

    End Sub

    If everyone can help me, I thanks a lot.

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Define "doesn't work"; error, incorrect results, etc. You've defined conta as a Double, then tried to stuff a string into it, which won't work. Depending on the data types of the 2 fields, each may or may not need the single quotes around the value; see here:

    Open a second form to the record

    I note you have both a filter and wherecondition specified. Is that on purpose?
    Paul

  9. #9
    Join Date
    May 2009
    Posts
    14
    ok.

    I have one text box that recieve a number (double) and on the same form I have one list box that recieve a text. I want use the Openreport command to print the report with these two filters. Is It possible? With the line code that I`ve already sent I didn`t get the report.

    Thanks a lot with your help and compreehension.

  10. #10
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    The variables you declare here must both be strings, as you're putting a string into each. The data type of the field determines whether or not you need single quotes, as noted in the link.
    Paul

  11. #11
    Join Date
    May 2009
    Posts
    14
    The tips that you sent me on the webpage works with one text box or listbox per form. In my case, I have two different filters in one form (one string and other double) and I want print a report. How can I put both command in one code line command?

    regarding

  12. #12
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Have you changed the declaration of conta to String as I advised? Do that, use the numeric format for the numeric field and the text format for the other, and it should work.
    Paul

  13. #13
    Join Date
    May 2009
    Posts
    14
    Paul,

    I make changes as you advised, but I have a syntax error on the following command line

    DoCmd.OpenReport "Report_modulo", acViewPreview, Cons_trans, Modu & "and " conta

    Follow below the code. If I make any mistake please correct me, please.

    Private Sub Command2_Click()

    Dim Modu As String

    Dim conta As String



    conta = " Conta_contábil = " & Me.Txt_Conta_contabil ---> here I enter with number


    Modu = " MODULO = '" & Me.lst_modulo & "'" ---> here I select the text



    DoCmd.OpenReport "Report_modulo", acViewPreview, Cons_trans, Modu & "and " conta


    End Sub

    Regarding
    Attached Thumbnails Attached Thumbnails syntax_error.JPG  

  14. #14
    Join Date
    May 2009
    Posts
    14
    Hi Paul, after post this comment I saw My mistake. After correct the mistake I realize that the variable conta doens't filter on CmdOpenreport command.

    What did I do wrong?

    Follow below the code Line after I had corrected

    DoCmd.OpenReport "Rel_Trans", acViewPreview, Cons_trans, conta & " and " & Modu

    Thanks in advance.

  15. #15
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You didn't address what's in the filter argument. The report name has also changed. Have you set a breakpoint and made sure the variables contain what you expect?
    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •