|
| 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 14th, 2010, 04:41 PM
posted to microsoft.public.access.queries
|
|||
|
|||
Finding missing operator
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! 
|
|
#2
April 14th, 2010, 04:43 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Finding missing operator
Posting your SQL here would help us to help you.
-- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "AccessKay" wrote: 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!
|
|
#3
April 14th, 2010, 05:12 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Finding missing operator
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" wrote: Posting your SQL here would help us to help you. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "AccessKay" wrote: 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!
|
|
#4
April 14th, 2010, 05:32 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Finding missing operator
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. "AccessKay" wrote: 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" wrote: Posting your SQL here would help us to help you. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "AccessKay" wrote: 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!
|
|
#5
April 14th, 2010, 05:43 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Finding missing operator
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? Jerry Whittle wrote: 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! -- Message posted via http://www.accessmonster.com
|
|
#6
April 14th, 2010, 07:39 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Finding missing operator
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. "AccessKay via AccessMonster.com" wrote: 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? Jerry Whittle wrote: 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! -- Message posted via http://www.accessmonster.com .
|
|
#7
April 14th, 2010, 07:56 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Finding missing operator
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! Jerry Whittle wrote: 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! -- Message posted via http://www.accessmonster.com
|
|
#8
April 14th, 2010, 09:05 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Finding missing operator
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. "AccessKay via AccessMonster.com" wrote: 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! Jerry Whittle wrote: 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! -- Message posted via http://www.accessmonster.com .
|
|
#9
April 14th, 2010, 09:24 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Finding missing operator
Good thinking Jerry. I will do so and see how it goes. Thanks for your help.
Jerry Whittle wrote: 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. 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 [quoted text clipped - 16 lines] Thank you! -- Message posted via http://www.accessmonster.com
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
