Where Condition in DoCmd function

[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.

 

[Ken Snell \(MVP\)]

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\#")

 

[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\)]

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>

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.

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\#")

 

[Guest]

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

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>

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.

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>





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.