Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Where Condition in DoCmd function

Discussion in 'Information Technology' started by Marixxe, Jul 28, 2006.

  1. Marixxe

    Marixxe
    Expand Collapse
    Guest

    Hello there!

    In access visual basic, I would like to ask how would I enter a where
    condition in a Docmd function, filtering a specific month and year.

    Example:

    Docmd.Openform "formname", acNormal,,(WHERE CONDITION)

    I would appreciate it very much if you could help me with my problem. Thank
    you.
     
  2. Loading...


  3. Ken Snell \(MVP\)

    Ken Snell \(MVP\)
    Expand Collapse
    Guest

    Generally, it would be something like this:

    DoCmd.OpenForm "FormName", acNormal, , "NameOfField=SomeValue"

    If you want to use a value from a control or variable, then this:

    DoCmd.OpenForm "FormName", acNormal, , "NameOfField=" & VariableName

    Note that the syntax varies if the field type is numeric, date, or text:

    Numeric:
    DoCmd.OpenForm "FormName", acNormal, , "NameOfField=" & VariableName

    Text (delimit with ' character):
    DoCmd.OpenForm "FormName", acNormal, , "NameOfField='" & VariableName & "'"

    Date/Time (delimit with # character):
    DoCmd.OpenForm "FormName", acNormal, , "NameOfField=#" &
    Format(VariableName, "m\/d\/yyyy") & "#"
    or
    DoCmd.OpenForm "FormName", acNormal, , "NameOfField=" & Format(VariableName,
    "\#m\/d\/yyyy\#")
    --

    Ken Snell
    <MS ACCESS MVP>





    "Marixxe" <Marixxe@discussions.microsoft.com> wrote in message
    news:6DF9A6BA-6D15-41B1-82A3-1EACAA3AA2E8@microsoft.com...
    > Hello there!
    >
    > In access visual basic, I would like to ask how would I enter a where
    > condition in a Docmd function, filtering a specific month and year.
    >
    > Example:
    >
    > Docmd.Openform "formname", acNormal,,(WHERE CONDITION)
    >
    > I would appreciate it very much if you could help me with my problem.
    > Thank
    > you.
     
  4. Marixxe

    Marixxe
    Expand Collapse
    Guest

    Thanks for the suggestion, but I think I'm mixed up. Anyway, I have here my
    coding:

    DoCmd.OpenForm "Income Statement Subform", acNormal, , DatePart("m",
    Forms![Income Statement Subform]![Voucher_Date]) = 1

    I have a crosstab query of Yearly Income Statement. Everytime I open the
    query, it ask what year will it show and when I inputed a year, the result
    shows the Account Description and its corresponding Amount for January,
    February, March and so on. What I did was, I made a form for this query. What
    I would like to happen to the form is that when I click in field name
    JANUARY, I want the form "INCOME STATEMENT SUBFORM" to open and show only the
    records for the month of JANUARY 2004. Please note that 2004 is the inputed
    year in the query awhile ago.

    I would appreciate it very much if you could help me with my problem. Thanks
    so much.



    "Ken Snell (MVP)" wrote:

    > Generally, it would be something like this:
    >
    > DoCmd.OpenForm "FormName", acNormal, , "NameOfField=SomeValue"
    >
    > If you want to use a value from a control or variable, then this:
    >
    > DoCmd.OpenForm "FormName", acNormal, , "NameOfField=" & VariableName
    >
    > Note that the syntax varies if the field type is numeric, date, or text:
    >
    > Numeric:
    > DoCmd.OpenForm "FormName", acNormal, , "NameOfField=" & VariableName
    >
    > Text (delimit with ' character):
    > DoCmd.OpenForm "FormName", acNormal, , "NameOfField='" & VariableName & "'"
    >
    > Date/Time (delimit with # character):
    > DoCmd.OpenForm "FormName", acNormal, , "NameOfField=#" &
    > Format(VariableName, "m\/d\/yyyy") & "#"
    > or
    > DoCmd.OpenForm "FormName", acNormal, , "NameOfField=" & Format(VariableName,
    > "\#m\/d\/yyyy\#")
    > --
    >
    > Ken Snell
    > <MS ACCESS MVP>
    >
    >
    >
    >
    >
    > "Marixxe" <Marixxe@discussions.microsoft.com> wrote in message
    > news:6DF9A6BA-6D15-41B1-82A3-1EACAA3AA2E8@microsoft.com...
    > > Hello there!
    > >
    > > In access visual basic, I would like to ask how would I enter a where
    > > condition in a Docmd function, filtering a specific month and year.
    > >
    > > Example:
    > >
    > > Docmd.Openform "formname", acNormal,,(WHERE CONDITION)
    > >
    > > I would appreciate it very much if you could help me with my problem.
    > > Thank
    > > you.

    >
    >
    >
     
  5. Ken Snell \(MVP\)

    Ken Snell \(MVP\)
    Expand Collapse
    Guest

    First item is that you have not made a string of the Where condition. Look
    at my example and see what is being done.

    Second item is that you must use an expression that sets the value of one of
    the fields in the second form's RecordSource. Your statement is just passing
    a boolean result value to the form (the result of the comparison of the
    DatePart function and the 1). Your expression does not include any field so
    the form cannot filter anything.

    For what you want to do, you must add a calculated field to the query that
    is the second form's RecordSource query. That calculated field needs to
    return the DatePart value on which you then will filter. So the calculated
    field would be something like this (using generic field names):
    TheMonth: DatePart("m", [NameOfDateFieldInTable])

    Then the filter statement in the DoCmd.OpenForm would be this:
    DoCmd.OpenForm "Income Statement Subform", acNormal, , "TheMonth =
    1"

    --

    Ken Snell
    <MS ACCESS MVP>



    "Marixxe" <Marixxe@discussions.microsoft.com> wrote in message
    news:C1679421-E1FC-4A80-A6D4-37C31791B6EF@microsoft.com...
    > Thanks for the suggestion, but I think I'm mixed up. Anyway, I have here
    > my
    > coding:
    >
    > DoCmd.OpenForm "Income Statement Subform", acNormal, , DatePart("m",
    > Forms![Income Statement Subform]![Voucher_Date]) = 1
    >
    > I have a crosstab query of Yearly Income Statement. Everytime I open the
    > query, it ask what year will it show and when I inputed a year, the result
    > shows the Account Description and its corresponding Amount for January,
    > February, March and so on. What I did was, I made a form for this query.
    > What
    > I would like to happen to the form is that when I click in field name
    > JANUARY, I want the form "INCOME STATEMENT SUBFORM" to open and show only
    > the
    > records for the month of JANUARY 2004. Please note that 2004 is the
    > inputed
    > year in the query awhile ago.
    >
    > I would appreciate it very much if you could help me with my problem.
    > Thanks
    > so much.
    >
    >
    >
    > "Ken Snell (MVP)" wrote:
    >
    >> Generally, it would be something like this:
    >>
    >> DoCmd.OpenForm "FormName", acNormal, , "NameOfField=SomeValue"
    >>
    >> If you want to use a value from a control or variable, then this:
    >>
    >> DoCmd.OpenForm "FormName", acNormal, , "NameOfField=" & VariableName
    >>
    >> Note that the syntax varies if the field type is numeric, date, or text:
    >>
    >> Numeric:
    >> DoCmd.OpenForm "FormName", acNormal, , "NameOfField=" & VariableName
    >>
    >> Text (delimit with ' character):
    >> DoCmd.OpenForm "FormName", acNormal, , "NameOfField='" & VariableName &
    >> "'"
    >>
    >> Date/Time (delimit with # character):
    >> DoCmd.OpenForm "FormName", acNormal, , "NameOfField=#" &
    >> Format(VariableName, "m\/d\/yyyy") & "#"
    >> or
    >> DoCmd.OpenForm "FormName", acNormal, , "NameOfField=" &
    >> Format(VariableName,
    >> "\#m\/d\/yyyy\#")
    >> --
    >>
    >> Ken Snell
    >> <MS ACCESS MVP>
    >>
    >>
    >>
    >>
    >>
    >> "Marixxe" <Marixxe@discussions.microsoft.com> wrote in message
    >> news:6DF9A6BA-6D15-41B1-82A3-1EACAA3AA2E8@microsoft.com...
    >> > Hello there!
    >> >
    >> > In access visual basic, I would like to ask how would I enter a where
    >> > condition in a Docmd function, filtering a specific month and year.
    >> >
    >> > Example:
    >> >
    >> > Docmd.Openform "formname", acNormal,,(WHERE CONDITION)
    >> >
    >> > I would appreciate it very much if you could help me with my problem.
    >> > Thank
    >> > you.

    >>
    >>
    >>
     
  6. Marixxe

    Marixxe
    Expand Collapse
    Guest

    Thank you very much for your help, Ken. Now I can proceed to my other
    concerns.


    "Ken Snell (MVP)" wrote:

    > First item is that you have not made a string of the Where condition. Look
    > at my example and see what is being done.
    >
    > Second item is that you must use an expression that sets the value of one of
    > the fields in the second form's RecordSource. Your statement is just passing
    > a boolean result value to the form (the result of the comparison of the
    > DatePart function and the 1). Your expression does not include any field so
    > the form cannot filter anything.
    >
    > For what you want to do, you must add a calculated field to the query that
    > is the second form's RecordSource query. That calculated field needs to
    > return the DatePart value on which you then will filter. So the calculated
    > field would be something like this (using generic field names):
    > TheMonth: DatePart("m", [NameOfDateFieldInTable])
    >
    > Then the filter statement in the DoCmd.OpenForm would be this:
    > DoCmd.OpenForm "Income Statement Subform", acNormal, , "TheMonth =
    > 1"
    >
    > --
    >
    > Ken Snell
    > <MS ACCESS MVP>
    >
    >
    >
    > "Marixxe" <Marixxe@discussions.microsoft.com> wrote in message
    > news:C1679421-E1FC-4A80-A6D4-37C31791B6EF@microsoft.com...
    > > Thanks for the suggestion, but I think I'm mixed up. Anyway, I have here
    > > my
    > > coding:
    > >
    > > DoCmd.OpenForm "Income Statement Subform", acNormal, , DatePart("m",
    > > Forms![Income Statement Subform]![Voucher_Date]) = 1
    > >
    > > I have a crosstab query of Yearly Income Statement. Everytime I open the
    > > query, it ask what year will it show and when I inputed a year, the result
    > > shows the Account Description and its corresponding Amount for January,
    > > February, March and so on. What I did was, I made a form for this query.
    > > What
    > > I would like to happen to the form is that when I click in field name
    > > JANUARY, I want the form "INCOME STATEMENT SUBFORM" to open and show only
    > > the
    > > records for the month of JANUARY 2004. Please note that 2004 is the
    > > inputed
    > > year in the query awhile ago.
    > >
    > > I would appreciate it very much if you could help me with my problem.
    > > Thanks
    > > so much.
    > >
    > >
    > >
    > > "Ken Snell (MVP)" wrote:
    > >
    > >> Generally, it would be something like this:
    > >>
    > >> DoCmd.OpenForm "FormName", acNormal, , "NameOfField=SomeValue"
    > >>
    > >> If you want to use a value from a control or variable, then this:
    > >>
    > >> DoCmd.OpenForm "FormName", acNormal, , "NameOfField=" & VariableName
    > >>
    > >> Note that the syntax varies if the field type is numeric, date, or text:
    > >>
    > >> Numeric:
    > >> DoCmd.OpenForm "FormName", acNormal, , "NameOfField=" & VariableName
    > >>
    > >> Text (delimit with ' character):
    > >> DoCmd.OpenForm "FormName", acNormal, , "NameOfField='" & VariableName &
    > >> "'"
    > >>
    > >> Date/Time (delimit with # character):
    > >> DoCmd.OpenForm "FormName", acNormal, , "NameOfField=#" &
    > >> Format(VariableName, "m\/d\/yyyy") & "#"
    > >> or
    > >> DoCmd.OpenForm "FormName", acNormal, , "NameOfField=" &
    > >> Format(VariableName,
    > >> "\#m\/d\/yyyy\#")
    > >> --
    > >>
    > >> Ken Snell
    > >> <MS ACCESS MVP>
    > >>
    > >>
    > >>
    > >>
    > >>
    > >> "Marixxe" <Marixxe@discussions.microsoft.com> wrote in message
    > >> news:6DF9A6BA-6D15-41B1-82A3-1EACAA3AA2E8@microsoft.com...
    > >> > Hello there!
    > >> >
    > >> > In access visual basic, I would like to ask how would I enter a where
    > >> > condition in a Docmd function, filtering a specific month and year.
    > >> >
    > >> > Example:
    > >> >
    > >> > Docmd.Openform "formname", acNormal,,(WHERE CONDITION)
    > >> >
    > >> > I would appreciate it very much if you could help me with my problem.
    > >> > Thank
    > >> > you.
    > >>
    > >>
    > >>

    >
    >
    >
     

Share This Page