Where Condition in DoCmd function

#1 (**permalink**) 28-Jul-2006, 08:16 AM

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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/10869-where-condition-in-docmd-function.html

Example:

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

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

*

#2 (**permalink**) 28-Jul-2006, 08:16 AM

Generally, it would be something like this:

DoCmd.OpenForm "FormName", acNormal, , "NameOfField=SomeValue"
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10869

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

"Marixxe" 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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10869
>
> Example:
>
> Docmd.Openform "formname", acNormal,,(WHERE CONDITION)
>
> I would appreciate it very much if you could help me with my problem.
> Thank
> you.

#3 (**permalink**) 28-Jul-2006, 08:16 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10869
>
> Text (delimit with ' character):
> DoCmd.OpenForm "FormName", acNormal, , "NameOfField='" & VariableName & "'"
>
> Date/Time (delimit with # character):
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10869
> DoCmd.OpenForm "FormName", acNormal, , "NameOfField=#" &
> Format(VariableName, "m\/d\/yyyy") & "#"
> or
> DoCmd.OpenForm "FormName", acNormal, , "NameOfField=" & Format(VariableName,
> "\#m\/d\/yyyy\#")
> --
>
> Ken Snell
>
>
>
>
>
>
> "Marixxe" 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 (**permalink**) 28-Jul-2006, 08:16 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10869
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

"Marixxe" 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") & "#"
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10869
>> or
>> DoCmd.OpenForm "FormName", acNormal, , "NameOfField=" &
>> Format(VariableName,
>> "\#m\/d\/yyyy\#")
>> --
>>
>> Ken Snell
>>
>>
>>
>>
>>
>>
>> "Marixxe" 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 (**permalink**) 28-Jul-2006, 08:17 AM

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
>
>
>
>
> "Marixxe" 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10869
> > 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:
> >>
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10869
> >> 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
> >>
> >>
> >>
> >>
> >>
> >>
> >> "Marixxe" 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.
> >>
> >>
> >>
>
>
>