Unbound Form for Criteria

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
 
K

KARL DEWEY

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;
 
A

AccessKay

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

AccessKay

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

KARL DEWEY

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

AccessKay

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

AccessKay

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Dealing with period variances 4
Reference page header to query parameters 2
Allen Browne’s date range vba for more reports 2
Filtering a Combo Box based on a field on a form 1
Overlapping Dates and Filtering Records 1
Chart from previous period 1
Wrong Form opening 5
Link criteria on Open Form Command Button 5

Top