Finding missing operator

[AccessKay]

I was hoping that someone could give me some possible reason as to why I’d
get a missing operator error when my SQL is correct. It’s an IIf-DateSerial
function for two calculated fields. If it’s not my SQL, then what could it
be?

Thank you!

 

[Jerry Whittle]

Posting your SQL here would help us to help you.

 

[AccessKay]

It's for two calculated fields that are going to be used for an option group
in a form.

SELECT IIF([Forms]![frmTest]![Frame0] = 1, Sum(IIF(Format([TransDate],
"yyyymm") = Format(Date(), "yyyymm"), [Total_Lbr_Cost], 0),

IIF([Forms]![FrmTest]![ Frame0] = 2, Sum(IIF(Format([TransDate], "yyyyq") =
Format(Date(), "yyyyq"), [Total_Lbr_Cost], 0), IIF([Forms]![FrmTest]![
Frame0] =
3, Sum(IIF(Format([TransDate], "yyyyq") = Format(Date(), "yyyyq"),
[Total_Lbr_Cost], 0), "Error"))) AS Current_Interval,

IIF([Forms]![FrmTest]![ Frame0] = 1, Sum(IIF(Format([TransDate], "yyyymm") =
Format(DateAdd("m", -1,Date()), "yyyymm"), [Total_Lbr_Cost], 0),

IIF([Forms]![FrmTest]![ Frame0] = 2, Sum(IIF(Format([TransDate], "yyyyq") =
Format(DateAdd("q", -1,Date()), "yyyyq"), [Total_Lbr_Cost], 0),

IIF([Forms]![FrmTest]![ Frame0] = 3, Sum(IIF(Format([TransDate], "yyyyq") =
Format(DateAdd("yyyy", -1,Date()), "yyyyq"), [Total_Lbr_Cost], 0),
"Error"))) AS
Previous_Interval

FROM tblTrans_Mstr;

 

[Jerry Whittle]

I don't see the DateSerial function in your SQL. Are you saying that you are
going to apply the DateSerial function to data returned by this query and the
query does work OK?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

It's for two calculated fields that are going to be used for an option group
in a form.

SELECT IIF([Forms]![frmTest]![Frame0] = 1, Sum(IIF(Format([TransDate],
"yyyymm") = Format(Date(), "yyyymm"), [Total_Lbr_Cost], 0),

IIF([Forms]![FrmTest]![ Frame0] = 2, Sum(IIF(Format([TransDate], "yyyyq") =
Format(Date(), "yyyyq"), [Total_Lbr_Cost], 0), IIF([Forms]![FrmTest]![
Frame0] =
3, Sum(IIF(Format([TransDate], "yyyyq") = Format(Date(), "yyyyq"),
[Total_Lbr_Cost], 0), "Error"))) AS Current_Interval,

IIF([Forms]![FrmTest]![ Frame0] = 1, Sum(IIF(Format([TransDate], "yyyymm") =
Format(DateAdd("m", -1,Date()), "yyyymm"), [Total_Lbr_Cost], 0),

IIF([Forms]![FrmTest]![ Frame0] = 2, Sum(IIF(Format([TransDate], "yyyyq") =
Format(DateAdd("q", -1,Date()), "yyyyq"), [Total_Lbr_Cost], 0),

IIF([Forms]![FrmTest]![ Frame0] = 3, Sum(IIF(Format([TransDate], "yyyyq") =
Format(DateAdd("yyyy", -1,Date()), "yyyyq"), [Total_Lbr_Cost], 0),
"Error"))) AS
Previous_Interval

FROM tblTrans_Mstr;

Posting your SQL here would help us to help you.

 

[Jerry Whittle]

I've never used anything like that in an option group. Does the query run
correctly by itself?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

no...sorry about that Jerry. I was studying DateSerial and had it on my
brain. Forget that. It's just an option group. This SQL should work for
two calculated fields right?

I don't see the DateSerial function in your SQL. Are you saying that you are
going to apply the DateSerial function to data returned by this query and the
query does work OK?

It's for two calculated fields that are going to be used for an option group
in a form.

[quoted text clipped - 29 lines]

Thank you!

 

[Jerry Whittle]

Let's just try to get the query to work first. I don't know what you are
trying to do is wrong. It's just that I haven't even done anything like that
with an option group.

Start by simplifying things and move from there. Remove one of the nested
IIf statement and see if it runs OK. If so run it with just the other nested
IIf.

If neither runs, simplify one or the other until it runs then build it back
up. Then add a simple version of the other one and build from there.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

No, when I try to run it, that's when I receive the missing operator error.
By your response, I take it that I'm doing this all wrong. What I need is an
input form to pull three reports for the following date periods:
Month vs. Previous month
Current Qtr vs. Previous Qtr
Current Qtr vs. Previous Year’s Qtr

I was trying to set up an option box and insert this SQL into my query. I'm
so lost...this shouldn't be so difficult but it is for me. Any suggestion
about how I can do this?

Thanks again!

I've never used anything like that in an option group. Does the query run
correctly by itself?

no...sorry about that Jerry. I was studying DateSerial and had it on my
brain. Forget that. It's just an option group. This SQL should work for

[quoted text clipped - 8 lines]

Thank you!