|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
April 9th, 2010, 02:55 PM
posted to microsoft.public.access.forms
|
|||
|
|||
Unbound Form for Criteria
Hi everyone,
How can I make an unbound form pull criteria for: Month vs. Previous month Current Qtr vs. Previous Qtr Current Qtr vs. Previous Year’s Qtr I’ve been around the world and back again on this. Is it a matter of creating expressions for these in a form or should this be a part of the design of my query. My end result is to have a report showing one period vs. the other period and the variance. Please point me in the right direction…please! Thank you, Kay 
|
|
#2
April 9th, 2010, 04:24 PM
posted to microsoft.public.access.forms
|
|||
|
|||
|
Unbound Form for Criteria
It will take more than an unbound form for the criteria to get your data.
You will have to add a calculated field to apply the criteria to. I think the easiest for the operator is an Option Group with the choices. I assume that you want to count or sum a field so this is the sum -- SELECT IIF([Forms]![YourForm]![Frame0] = 1, Sum(IIF(Format([YourDate], "yyyymm") = Format(Date(), "yyyymm"), [FieldToSum], 0), IIF([Forms]![YourForm]![Frame0] = 2, Sum(IIF(Format([YourDate], "yyyyq") = Format(Date(), "yyyyq"), [FieldToSum], 0), IIF([Forms]![YourForm]![Frame0] = 3, Sum(IIF(Format([YourDate], "yyyyq") = Format(Date(), "yyyyq"), [FieldToSum], 0), "Error"))) AS Current_Interval, IIF([Forms]![YourForm]![Frame0] = 1, Sum(IIF(Format([YourDate], "yyyymm") = Format(DateAdd("m", -1,Date()), "yyyymm"), [FieldToSum], 0), IIF([Forms]![YourForm]![Frame0] = 2, Sum(IIF(Format([YourDate], "yyyyq") = Format(DateAdd("q", -1,Date()), "yyyyq"), [FieldToSum], 0), IIF([Forms]![YourForm]![Frame0] = 3, Sum(IIF(Format([YourDate], "yyyyq") = Format(DateAdd("yyyy", -1,Date()), "yyyyq"), [FieldToSum], 0), "Error"))) AS Previous_Interval FROM YourTable; -- Build a little, test a little. "AccessKay" wrote: Hi everyone, How can I make an unbound form pull criteria for: Month vs. Previous month Current Qtr vs. Previous Qtr Current Qtr vs. Previous Year’s Qtr I’ve been around the world and back again on this. Is it a matter of creating expressions for these in a form or should this be a part of the design of my query. My end result is to have a report showing one period vs. the other period and the variance. Please point me in the right direction…please! Thank you, Kay
|
|
#3
April 9th, 2010, 05:46 PM
posted to microsoft.public.access.forms
|
|||
|
|||
|
Unbound Form for Criteria
Thanks Karl. This is a good idea about using an option box. I'm going to
try this with the details you supplied. Have a good weekend! "KARL DEWEY" wrote: It will take more than an unbound form for the criteria to get your data. You will have to add a calculated field to apply the criteria to. I think the easiest for the operator is an Option Group with the choices. I assume that you want to count or sum a field so this is the sum -- SELECT IIF([Forms]![YourForm]![Frame0] = 1, Sum(IIF(Format([YourDate], "yyyymm") = Format(Date(), "yyyymm"), [FieldToSum], 0), IIF([Forms]![YourForm]![Frame0] = 2, Sum(IIF(Format([YourDate], "yyyyq") = Format(Date(), "yyyyq"), [FieldToSum], 0), IIF([Forms]![YourForm]![Frame0] = 3, Sum(IIF(Format([YourDate], "yyyyq") = Format(Date(), "yyyyq"), [FieldToSum], 0), "Error"))) AS Current_Interval, IIF([Forms]![YourForm]![Frame0] = 1, Sum(IIF(Format([YourDate], "yyyymm") = Format(DateAdd("m", -1,Date()), "yyyymm"), [FieldToSum], 0), IIF([Forms]![YourForm]![Frame0] = 2, Sum(IIF(Format([YourDate], "yyyyq") = Format(DateAdd("q", -1,Date()), "yyyyq"), [FieldToSum], 0), IIF([Forms]![YourForm]![Frame0] = 3, Sum(IIF(Format([YourDate], "yyyyq") = Format(DateAdd("yyyy", -1,Date()), "yyyyq"), [FieldToSum], 0), "Error"))) AS Previous_Interval FROM YourTable; -- Build a little, test a little. "AccessKay" wrote: Hi everyone, How can I make an unbound form pull criteria for: Month vs. Previous month Current Qtr vs. Previous Qtr Current Qtr vs. Previous Year’s Qtr I’ve been around the world and back again on this. Is it a matter of creating expressions for these in a form or should this be a part of the design of my query. My end result is to have a report showing one period vs. the other period and the variance. Please point me in the right direction…please! Thank you, Kay
|
|
#4
April 9th, 2010, 10:24 PM
posted to microsoft.public.access.forms
|
|||
|
|||
|
Unbound Form for Criteria
Hi Karl,
I created the option group on a form and replaced my names in the expressions you gave me but I’m afraid that I don’t know where to put this. Is this one long expression that I put in a calculated field or is it part of my SQL??? Forgive my ignorance…this is new to me. Thanks! "KARL DEWEY" wrote: It will take more than an unbound form for the criteria to get your data. You will have to add a calculated field to apply the criteria to. I think the easiest for the operator is an Option Group with the choices. I assume that you want to count or sum a field so this is the sum -- SELECT IIF([Forms]![YourForm]![Frame0] = 1, Sum(IIF(Format([YourDate], "yyyymm") = Format(Date(), "yyyymm"), [FieldToSum], 0), IIF([Forms]![YourForm]![Frame0] = 2, Sum(IIF(Format([YourDate], "yyyyq") = Format(Date(), "yyyyq"), [FieldToSum], 0), IIF([Forms]![YourForm]![Frame0] = 3, Sum(IIF(Format([YourDate], "yyyyq") = Format(Date(), "yyyyq"), [FieldToSum], 0), "Error"))) AS Current_Interval, IIF([Forms]![YourForm]![Frame0] = 1, Sum(IIF(Format([YourDate], "yyyymm") = Format(DateAdd("m", -1,Date()), "yyyymm"), [FieldToSum], 0), IIF([Forms]![YourForm]![Frame0] = 2, Sum(IIF(Format([YourDate], "yyyyq") = Format(DateAdd("q", -1,Date()), "yyyyq"), [FieldToSum], 0), IIF([Forms]![YourForm]![Frame0] = 3, Sum(IIF(Format([YourDate], "yyyyq") = Format(DateAdd("yyyy", -1,Date()), "yyyyq"), [FieldToSum], 0), "Error"))) AS Previous_Interval FROM YourTable; -- Build a little, test a little. "AccessKay" wrote: Hi everyone, How can I make an unbound form pull criteria for: Month vs. Previous month Current Qtr vs. Previous Qtr Current Qtr vs. Previous Year’s Qtr I’ve been around the world and back again on this. Is it a matter of creating expressions for these in a form or should this be a part of the design of my query. My end result is to have a report showing one period vs. the other period and the variance. Please point me in the right direction…please! Thank you, Kay
|
|
#5
April 10th, 2010, 05:44 PM
posted to microsoft.public.access.forms
|
|||
|
|||
|
Unbound Form for Criteria
It is a complete SQL statement for two calculated fields but you can paste
them in design view. Current_Interval: SELECT IIF([Forms]![YourForm]![Frame0] = 1, Sum(IIF(Format([YourDate], "yyyymm") = Format(Date(), "yyyymm"), [FieldToSum], 0), IIF([Forms]![YourForm]![Frame0] = 2, Sum(IIF(Format([YourDate], "yyyyq") = Format(Date(), "yyyyq"), [FieldToSum], 0), IIF([Forms]![YourForm]![Frame0] = 3, Sum(IIF(Format([YourDate], "yyyyq") = Format(Date(), "yyyyq"), [FieldToSum], 0), "Error"))) Previous_Interval: IIF([Forms]![YourForm]![Frame0] = 1, Sum(IIF(Format([YourDate], "yyyymm") = Format(DateAdd("m", -1,Date()), "yyyymm"), [FieldToSum], 0), IIF([Forms]![YourForm]![Frame0] = 2, Sum(IIF(Format([YourDate], "yyyyq") = Format(DateAdd("q", -1,Date()), "yyyyq"), [FieldToSum], 0), IIF([Forms]![YourForm]![Frame0] = 3, Sum(IIF(Format([YourDate], "yyyyq") = Format(DateAdd("yyyy", -1,Date()), "yyyyq"), [FieldToSum], 0), "Error"))) -- Build a little, test a little. "AccessKay" wrote: Hi Karl, I created the option group on a form and replaced my names in the expressions you gave me but I’m afraid that I don’t know where to put this. Is this one long expression that I put in a calculated field or is it part of my SQL??? Forgive my ignorance…this is new to me. Thanks! "KARL DEWEY" wrote: It will take more than an unbound form for the criteria to get your data. You will have to add a calculated field to apply the criteria to. I think the easiest for the operator is an Option Group with the choices. I assume that you want to count or sum a field so this is the sum -- SELECT IIF([Forms]![YourForm]![Frame0] = 1, Sum(IIF(Format([YourDate], "yyyymm") = Format(Date(), "yyyymm"), [FieldToSum], 0), IIF([Forms]![YourForm]![Frame0] = 2, Sum(IIF(Format([YourDate], "yyyyq") = Format(Date(), "yyyyq"), [FieldToSum], 0), IIF([Forms]![YourForm]![Frame0] = 3, Sum(IIF(Format([YourDate], "yyyyq") = Format(Date(), "yyyyq"), [FieldToSum], 0), "Error"))) AS Current_Interval, IIF([Forms]![YourForm]![Frame0] = 1, Sum(IIF(Format([YourDate], "yyyymm") = Format(DateAdd("m", -1,Date()), "yyyymm"), [FieldToSum], 0), IIF([Forms]![YourForm]![Frame0] = 2, Sum(IIF(Format([YourDate], "yyyyq") = Format(DateAdd("q", -1,Date()), "yyyyq"), [FieldToSum], 0), IIF([Forms]![YourForm]![Frame0] = 3, Sum(IIF(Format([YourDate], "yyyyq") = Format(DateAdd("yyyy", -1,Date()), "yyyyq"), [FieldToSum], 0), "Error"))) AS Previous_Interval FROM YourTable; -- Build a little, test a little. "AccessKay" wrote: Hi everyone, How can I make an unbound form pull criteria for: Month vs. Previous month Current Qtr vs. Previous Qtr Current Qtr vs. Previous Year’s Qtr I’ve been around the world and back again on this. Is it a matter of creating expressions for these in a form or should this be a part of the design of my query. My end result is to have a report showing one period vs. the other period and the variance. Please point me in the right direction…please! Thank you, Kay
|
|
#6
April 12th, 2010, 03:11 PM
posted to microsoft.public.access.forms
|
|||
|
|||
|
Unbound Form for Criteria
Thanks Karl. I tried both ways over the weekend and kept getting errors.
When I tried to put it in design view, I received a syntax error about the subquery expression is incorrect and then when I tried to place it in the SQL, I received a missing operator error. Any ideas about what might be wrong? "KARL DEWEY" wrote: It is a complete SQL statement for two calculated fields but you can paste them in design view. Current_Interval: SELECT IIF([Forms]![YourForm]![Frame0] = 1, Sum(IIF(Format([YourDate], "yyyymm") = Format(Date(), "yyyymm"), [FieldToSum], 0), IIF([Forms]![YourForm]![Frame0] = 2, Sum(IIF(Format([YourDate], "yyyyq") = Format(Date(), "yyyyq"), [FieldToSum], 0), IIF([Forms]![YourForm]![Frame0] = 3, Sum(IIF(Format([YourDate], "yyyyq") = Format(Date(), "yyyyq"), [FieldToSum], 0), "Error"))) Previous_Interval: IIF([Forms]![YourForm]![Frame0] = 1, Sum(IIF(Format([YourDate], "yyyymm") = Format(DateAdd("m", -1,Date()), "yyyymm"), [FieldToSum], 0), IIF([Forms]![YourForm]![Frame0] = 2, Sum(IIF(Format([YourDate], "yyyyq") = Format(DateAdd("q", -1,Date()), "yyyyq"), [FieldToSum], 0), IIF([Forms]![YourForm]![Frame0] = 3, Sum(IIF(Format([YourDate], "yyyyq") = Format(DateAdd("yyyy", -1,Date()), "yyyyq"), [FieldToSum], 0), "Error"))) -- Build a little, test a little. "AccessKay" wrote: Hi Karl, I created the option group on a form and replaced my names in the expressions you gave me but I’m afraid that I don’t know where to put this. Is this one long expression that I put in a calculated field or is it part of my SQL??? Forgive my ignorance…this is new to me. Thanks! "KARL DEWEY" wrote: It will take more than an unbound form for the criteria to get your data. You will have to add a calculated field to apply the criteria to. I think the easiest for the operator is an Option Group with the choices. I assume that you want to count or sum a field so this is the sum -- SELECT IIF([Forms]![YourForm]![Frame0] = 1, Sum(IIF(Format([YourDate], "yyyymm") = Format(Date(), "yyyymm"), [FieldToSum], 0), IIF([Forms]![YourForm]![Frame0] = 2, Sum(IIF(Format([YourDate], "yyyyq") = Format(Date(), "yyyyq"), [FieldToSum], 0), IIF([Forms]![YourForm]![Frame0] = 3, Sum(IIF(Format([YourDate], "yyyyq") = Format(Date(), "yyyyq"), [FieldToSum], 0), "Error"))) AS Current_Interval, IIF([Forms]![YourForm]![Frame0] = 1, Sum(IIF(Format([YourDate], "yyyymm") = Format(DateAdd("m", -1,Date()), "yyyymm"), [FieldToSum], 0), IIF([Forms]![YourForm]![Frame0] = 2, Sum(IIF(Format([YourDate], "yyyyq") = Format(DateAdd("q", -1,Date()), "yyyyq"), [FieldToSum], 0), IIF([Forms]![YourForm]![Frame0] = 3, Sum(IIF(Format([YourDate], "yyyyq") = Format(DateAdd("yyyy", -1,Date()), "yyyyq"), [FieldToSum], 0), "Error"))) AS Previous_Interval FROM YourTable; -- Build a little, test a little. "AccessKay" wrote: Hi everyone, How can I make an unbound form pull criteria for: Month vs. Previous month Current Qtr vs. Previous Qtr Current Qtr vs. Previous Year’s Qtr I’ve been around the world and back again on this. Is it a matter of creating expressions for these in a form or should this be a part of the design of my query. My end result is to have a report showing one period vs. the other period and the variance. Please point me in the right direction…please! Thank you, Kay
|
|
#7
April 13th, 2010, 07:46 PM
posted to microsoft.public.access.forms
|
|||
|
|||
|
Unbound Form for Criteria
Karl or anyone...
I dissected this SQL and I can’t find a single thing wrong with it. I keep getting an error message for missing operator when I try to do it the SQL way. I really want it to work because it’s perfect otherwise. "KARL DEWEY" wrote: It is a complete SQL statement for two calculated fields but you can paste them in design view. Current_Interval: SELECT IIF([Forms]![YourForm]![Frame0] = 1, Sum(IIF(Format([YourDate], "yyyymm") = Format(Date(), "yyyymm"), [FieldToSum], 0), IIF([Forms]![YourForm]![Frame0] = 2, Sum(IIF(Format([YourDate], "yyyyq") = Format(Date(), "yyyyq"), [FieldToSum], 0), IIF([Forms]![YourForm]![Frame0] = 3, Sum(IIF(Format([YourDate], "yyyyq") = Format(Date(), "yyyyq"), [FieldToSum], 0), "Error"))) Previous_Interval: IIF([Forms]![YourForm]![Frame0] = 1, Sum(IIF(Format([YourDate], "yyyymm") = Format(DateAdd("m", -1,Date()), "yyyymm"), [FieldToSum], 0), IIF([Forms]![YourForm]![Frame0] = 2, Sum(IIF(Format([YourDate], "yyyyq") = Format(DateAdd("q", -1,Date()), "yyyyq"), [FieldToSum], 0), IIF([Forms]![YourForm]![Frame0] = 3, Sum(IIF(Format([YourDate], "yyyyq") = Format(DateAdd("yyyy", -1,Date()), "yyyyq"), [FieldToSum], 0), "Error"))) -- Build a little, test a little. "AccessKay" wrote: Hi Karl, I created the option group on a form and replaced my names in the expressions you gave me but I’m afraid that I don’t know where to put this. Is this one long expression that I put in a calculated field or is it part of my SQL??? Forgive my ignorance…this is new to me. Thanks! "KARL DEWEY" wrote: It will take more than an unbound form for the criteria to get your data. You will have to add a calculated field to apply the criteria to. I think the easiest for the operator is an Option Group with the choices. I assume that you want to count or sum a field so this is the sum -- SELECT IIF([Forms]![YourForm]![Frame0] = 1, Sum(IIF(Format([YourDate], "yyyymm") = Format(Date(), "yyyymm"), [FieldToSum], 0), IIF([Forms]![YourForm]![Frame0] = 2, Sum(IIF(Format([YourDate], "yyyyq") = Format(Date(), "yyyyq"), [FieldToSum], 0), IIF([Forms]![YourForm]![Frame0] = 3, Sum(IIF(Format([YourDate], "yyyyq") = Format(Date(), "yyyyq"), [FieldToSum], 0), "Error"))) AS Current_Interval, IIF([Forms]![YourForm]![Frame0] = 1, Sum(IIF(Format([YourDate], "yyyymm") = Format(DateAdd("m", -1,Date()), "yyyymm"), [FieldToSum], 0), IIF([Forms]![YourForm]![Frame0] = 2, Sum(IIF(Format([YourDate], "yyyyq") = Format(DateAdd("q", -1,Date()), "yyyyq"), [FieldToSum], 0), IIF([Forms]![YourForm]![Frame0] = 3, Sum(IIF(Format([YourDate], "yyyyq") = Format(DateAdd("yyyy", -1,Date()), "yyyyq"), [FieldToSum], 0), "Error"))) AS Previous_Interval FROM YourTable; -- Build a little, test a little. "AccessKay" wrote: Hi everyone, How can I make an unbound form pull criteria for: Month vs. Previous month Current Qtr vs. Previous Qtr Current Qtr vs. Previous Year’s Qtr I’ve been around the world and back again on this. Is it a matter of creating expressions for these in a form or should this be a part of the design of my query. My end result is to have a report showing one period vs. the other period and the variance. Please point me in the right direction…please! Thank you, Kay
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
