What's wrong with this query?

  • Thread starter Thread starter Craig
  • Start date Start date
C

Craig

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


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


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