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
|
|||
|
|||
What's wrong with this query?
SELECT [AccountID], [ClearDate]
SWITCH([ClearDate]=[ClearDate],2,TRUE,1) AS Expr1 FROM qry_Detail_Transactions LEFT JOIN qry_OpeningBalance_ClearDate ON qry_Detail_Transactions.[AccountID] = qry_OpeningBalance_ClearDate.AccountID ORDER BY AccountID, ClearDate; I get a syntax error message (missing operator) in query expression at the cleardate SWITCH line...can't seem to figure it out. Thanks. |
#2
|
|||
|
|||
What's wrong with this query?
You are missing a comma after the first [ClearDate] field in the first line.
Not sure why you're using the Switch function here, as your first expression will always be True? -- Ken Snell MS ACCESS MVP The above syntax will always result "Craig" wrote in message ... SELECT [AccountID], [ClearDate] SWITCH([ClearDate]=[ClearDate],2,TRUE,1) AS Expr1 FROM qry_Detail_Transactions LEFT JOIN qry_OpeningBalance_ClearDate ON qry_Detail_Transactions.[AccountID] = qry_OpeningBalance_ClearDate.AccountID ORDER BY AccountID, ClearDate; I get a syntax error message (missing operator) in query expression at the cleardate SWITCH line...can't seem to figure it out. Thanks. |
#3
|
|||
|
|||
What's wrong with this query?
Thanks. Actually, this was the original query situation -
I was trying to get a query to return a 1 or 2 based on account and trans type = Opening Balance. So, the result set would look like this: Acct Trans Type Expr1 1111 Debit 1 1111 Credit 1 1111 Opening Bal 2 1111 Transfer 2 1111 Wire 2 1111 Debit 2 2222 Credit 1 2222 Transfer 1 2222 Wire 1 2222 Opening Bal 2 2222 Debit 2 2222 Credit 2 .. .. etc. qryOBDate: SELECT Account, TransDate As OBTransDate FROM yourtable WHERE [Trans Type] = 'Opening Bal'; Then join original table to this query and test to get 1 or 2. something like: SELECT Account, [Trans Type], SWITCH([TransDate]=[OBTransDate],2,TRUE,1) AS Expr1 FROM yourtable LEFT JOIN qryOBDate ON yourtable.Account = qryOBDate.Account ORDER BY Account, TransDate; -----Original Message----- You are missing a comma after the first [ClearDate] field in the first line. Not sure why you're using the Switch function here, as your first expression will always be True? -- Ken Snell MS ACCESS MVP The above syntax will always result "Craig" wrote in message ... SELECT [AccountID], [ClearDate] SWITCH([ClearDate]=[ClearDate],2,TRUE,1) AS Expr1 FROM qry_Detail_Transactions LEFT JOIN qry_OpeningBalance_ClearDate ON qry_Detail_Transactions.[AccountID] = qry_OpeningBalance_ClearDate.AccountID ORDER BY AccountID, ClearDate; I get a syntax error message (missing operator) in query expression at the cleardate SWITCH line...can't seem to figure it out. Thanks. . |
#4
|
|||
|
|||
What's wrong with this query?
If the only issue you're having is how to best display a 1 or 2, replace the
SWITCH([TransDate]=[OBTransDate],2,TRUE,1) AS Expr1 with this (([TransDate] [OBTransDate]) + 2) AS Expr1 -- Ken Snell MS ACCESS MVP "Craig" wrote in message ... Thanks. Actually, this was the original query situation - I was trying to get a query to return a 1 or 2 based on account and trans type = Opening Balance. So, the result set would look like this: Acct Trans Type Expr1 1111 Debit 1 1111 Credit 1 1111 Opening Bal 2 1111 Transfer 2 1111 Wire 2 1111 Debit 2 2222 Credit 1 2222 Transfer 1 2222 Wire 1 2222 Opening Bal 2 2222 Debit 2 2222 Credit 2 . . etc. qryOBDate: SELECT Account, TransDate As OBTransDate FROM yourtable WHERE [Trans Type] = 'Opening Bal'; Then join original table to this query and test to get 1 or 2. something like: SELECT Account, [Trans Type], SWITCH([TransDate]=[OBTransDate],2,TRUE,1) AS Expr1 FROM yourtable LEFT JOIN qryOBDate ON yourtable.Account = qryOBDate.Account ORDER BY Account, TransDate; -----Original Message----- You are missing a comma after the first [ClearDate] field in the first line. Not sure why you're using the Switch function here, as your first expression will always be True? -- Ken Snell MS ACCESS MVP The above syntax will always result "Craig" wrote in message ... SELECT [AccountID], [ClearDate] SWITCH([ClearDate]=[ClearDate],2,TRUE,1) AS Expr1 FROM qry_Detail_Transactions LEFT JOIN qry_OpeningBalance_ClearDate ON qry_Detail_Transactions.[AccountID] = qry_OpeningBalance_ClearDate.AccountID ORDER BY AccountID, ClearDate; I get a syntax error message (missing operator) in query expression at the cleardate SWITCH line...can't seem to figure it out. Thanks. . |
#5
|
|||
|
|||
What's wrong with this query?
"Craig" wrote SELECT [AccountID], [ClearDate] SWITCH([ClearDate]=[ClearDate],2,TRUE,1) AS Expr1 FROM qry_Detail_Transactions LEFT JOIN qry_OpeningBalance_ClearDate ON qry_Detail_Transactions.[AccountID] = qry_OpeningBalance_ClearDate.AccountID ORDER BY AccountID, ClearDate; I get a syntax error message (missing operator) in query expression at the cleardate SWITCH line...can't seem to figure it out. Hi Craig, Please provide copies of SQLs for your 2 queries. The Switch was used because from your earlier post there was no indication whether all accounts would for sure have an "OpeningBalance" record. If that were the case, then it would have handled a null match giving expr1=1 for all records for those accounts that did not have an "OpeningBal" record. In fact, I had to guess there might be a transaction date for every record. In your SQL above, the date comparisons should have been between a transdate in original table with transdate from "OpeningBal query." I would think you would have wanted to alias [ClearDate] in your qry_OpeningBalance_ClearDate. And if you did not, how does Access know which [ClearDate] you are referring to in the SWITCH function? Sigh..... Gary Walter |
Thread Tools | |
Display Modes | |
|
|