Unbound Form for Criteria

  • Thread starter Thread starter AccessKay
  • Start date Start date
A

AccessKay

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
 
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;
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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.


:

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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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.


:

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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top