UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Query Bracket [censored]!, Access 2016    
 
   
AP_Ben
post Feb 26 2020, 07:16 AM
Post#1



Posts: 41
Joined: 11-February 20



Hi, I'm trying to construct a VBA query that will produce the correct results. If I get the syntax accepted it produces all records, and any attempt to fix it gives me an error.

I decided to count the left brackets "(" and then the right brackets ")" to see where it was uneven, but there seem to be more fo one than the other, evenin the SQL produced byt eh query designer, which makes no sense to me. (clearly means something is missing in my understanding!)

Access generated SQL
SQL
SELECT [audit trail].*
FROM [audit trail]
WHERE ((([audit trail].Type)="SR" Or ([audit trail].Type)="BP") AND (([audit trail].DebitCode)="1200")) OR ((([audit trail].CreditCode)="1200"));

now mine using the field AcctRef which does contain the same value (currently)
SQL
SELECT [audit trail].*
FROM [audit trail]
WHERE (([type] = "SR" OR [type] = "BP" ) AND (([debitcode] = [AcctRef]) OR (([CreditCode] = [AcctRef])) ) ORDER BY TxDate

that produces an error missing operator.

to try and be clear what the query result should be is transactions that have a type of either 'SR' or 'BP' (ignoring the single quotes) AND have either their (debitcode value as say 1200) or their (creditcode value as 1200)

Needless to say I have tried a number of different permutations of bracketing - the transaction types are processed correctly (they are produced by ticking boxes on the form) however even if I get acceptable syntax, all records are shown regardless of their creditcode/debitcode values.

I can't find a reference that explains the use of brackets in queries - I'm sure one must exist somewhere - and when access does its own thing it chucks so many brackets in there I can't figure out what its doing, nor as a consequence spot if it is doing something wrong (i.e. the results may not be exactly what I expect)

Cheers,
Ben
Go to the top of the page
 
cheekybuddha
post Feb 26 2020, 07:34 AM
Post#2


UtterAccess Moderator
Posts: 12,622
Joined: 6-December 03
From: Telegraph Hill


This is always tricky, but try this:
CODE
SELECT at.*
FROM [audit trail] at
WHERE (at.[type] = 'SR' AND (at.DebitCode = 1200 OR at.CreditCode = 1200))
   OR (at.[type] = 'BP' AND (at.DebitCode = 1200 OR at.CreditCode = 1200))
;


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Feb 26 2020, 07:36 AM
Post#3


UtterAccess Moderator
Posts: 12,622
Joined: 6-December 03
From: Telegraph Hill


Where is field AcctRef?

--------------------


Regards,

David Marten
Go to the top of the page
 
AP_Ben
post Feb 26 2020, 07:58 AM
Post#4



Posts: 41
Joined: 11-February 20



Thanks David, I shall experiment with your suggestion and post back shortly. AcctRef is on the form, and shown with debug.print immediately before the SQLstatement is constructed
Go to the top of the page
 
AP_Ben
post Feb 26 2020, 08:02 AM
Post#5



Posts: 41
Joined: 11-February 20



Sorry, looking at your example I didn't mention that there area potential 4 transaction types which might need to be included (SR, BR, BP and PP) which might make that statement quite clumsy, however I will in any event test with both a manually typed '1200' and using the [AcctRef] field.

Ben
Go to the top of the page
 
cheekybuddha
post Feb 26 2020, 08:08 AM
Post#6


UtterAccess Moderator
Posts: 12,622
Joined: 6-December 03
From: Telegraph Hill


Perhaps it will work like this - but you need a full reference to the AcctRef control:
CODE
SELECT at.*
FROM [audit trail] at
WHERE at.[type] = IN ('SR', 'BR', 'BP', 'PP')
  AND (at.DebitCode = [Forms]![YourFormName]![AcctRef] OR at.CreditCode = [Forms]![YourFormName]![AcctRef])
;


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
BruceM
post Feb 26 2020, 08:22 AM
Post#7


UtterAccess VIP
Posts: 8,056
Joined: 24-May 10
From: Downeast Maine


One of the problems may be that Type is a reserved word (it is a field property). It would be better to use something else, but if you must use Type you should enclose it in square brackets.

The first version has the same number of left and right parentheses. Access litters queries with brackets that are not needed. They may have some small bearing on the efficiency, but can make the code difficult to follow. Parentheses are needed for logical groupings, in the same way parentheses control a mathematical operation.

I realize this isn't about math. It seems your intent is to limit the records to those where Type is either of two values, and CreditCode or DebitCode = AcctRef. All you should need are the two logical groupings:

WHERE ([audit trail].[Type] = "SR" Or [audit trail].[Type] = "BP") AND ([audit trail].DebitCode="1200" OR [audit trail].CreditCode = "1200");

Or maybe AcctRef instead of the literal value 1200. Are DebitCode and CreditCode text fields?

If you type the SQL directly, and there is only one table (or if the field names are all unique with more than one table, but in that case I would use the table names for my own benefit), you can just use the field names. If you assemble the same query in the query designer Access will use table names and add parentheses. Other database engines will optimize the SQL also. It's not just an Access thing.

I don't know of a reference, but I can say that I have never found a problem with removing the parentheses from something such as ([audit trail].Type). The parentheses Access adds do no harm, but can be confusing, that's all.

A text editor designed for code can be very helpful. I use Notepad ++, which is free. I put each parentheses on its own line.
Attached File  Picture1.jpg ( 19.11K )Number of downloads: 0

As you can see, there is a line connecting the parentheses. A closing parentheses lines up with the previous opening parentheses. This image shows something apparently unintended: The CreditCode test is entirely separate from the DebitCode test. The SQL requires that DebitCode = 1200 (because of the AND), but will find any record, no matter the [Type], if CreditCode = 1200. That's how I'm reading it, anyhow. Here it is with the extra parentheses removed:
Attached File  Picture3.jpg ( 13.49K )Number of downloads: 0

Somehow the logic in the first query went awry.

Adding Type values does not affect the situation I described, as long as they are bracketed as intended. Other responses have showed this, so I won't repeat it. I'm just pointing out some troubleshooting techniques for SQL, or any code that uses parentheses for logical groupings.
Go to the top of the page
 
AP_Ben
post Feb 26 2020, 08:33 AM
Post#8



Posts: 41
Joined: 11-February 20



posting this here so I can see the code highlighted - I just realised that whilst the original nominal code (AcctRef) was a number, both debitcode and creditcode are short text fields. This has doubtless thrown a lot of my results out of the window as they may have failed anyway. I can try and fix for this by either converting acctref to a string beforehand, or converting debitcode and creditcode to numbers.

Presumably I had a reason for doing this at design time but whatever it might have been its long since forgotten. As most things in the application are currently working happily (though many refinements in the UI and extra functionality still to come) changing them now would be a challenge. On the flip side, the longer I leave them the worse it will be if I do decide to change later!

SQL
SELECT [audit trail].* FROM [audit trail] WHERE ([type]= 'SR' AND ([DebitCode] = '1200' OR [CreditCode] = '1200')) OR ([type]='BP' AND ([DebitCode] = '1200' OR [CreditCode] = '1200')) ;

(which works)

I shall now look at your IN statement option, which looks promising. I shall be sure to use the full field reference
Ben
Go to the top of the page
 
AP_Ben
post Feb 26 2020, 08:55 AM
Post#9



Posts: 41
Joined: 11-February 20



Bruce, I think that helped iconfused.gif

I downloaded Notepad++ which I used to have but hardly ever used on my previous hard drive, and have been experimenting with bracket outlining - not too sure where I am going with that yet but will be good once I get my head around it properly.

I should have read the reserved words at the outset, clearly I knew some to avoid but was anxious to get started. Hmmm.

The only thing that is certain is that by the time I get this working as I want, I shall know more than I did beforehand and that can only be a good thing.

back to my experiments!
Go to the top of the page
 
AP_Ben
post Feb 26 2020, 09:24 AM
Post#10



Posts: 41
Joined: 11-February 20



So why doesn't it like even this simplified form:

SELECT [audit trail].* FROM [audit trail] WHERE
(
[audit trail].[type] = IN
(
'SR', BR', 'BP', 'PP'
)
)

syntax error: missing operator
(seems to have lost its indentation, and I can't figure how to post inline screenshot)
This post has been edited by AP_Ben: Feb 26 2020, 09:26 AM
Go to the top of the page
 
cheekybuddha
post Feb 26 2020, 09:28 AM
Post#11


UtterAccess Moderator
Posts: 12,622
Joined: 6-December 03
From: Telegraph Hill


You are missing a single quote before BR

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Feb 26 2020, 09:30 AM
Post#12


UtterAccess Moderator
Posts: 12,622
Joined: 6-December 03
From: Telegraph Hill


I recommend using as dew brackets as necessary to avoid confusion:
CODE
SELECT *
FROM [audit trail]
WHERE [type] = IN ('SR', 'BR', 'BP', 'PP');

--------------------


Regards,

David Marten
Go to the top of the page
 
BruceM
post Feb 26 2020, 09:31 AM
Post#13


UtterAccess VIP
Posts: 8,056
Joined: 24-May 10
From: Downeast Maine


The = sign is not supposed to be there. "IN" means, essentially, "in the list enclosed in parentheses". = IN doesn't work, as I recall.
Go to the top of the page
 
cheekybuddha
post Feb 26 2020, 09:34 AM
Post#14


UtterAccess Moderator
Posts: 12,622
Joined: 6-December 03
From: Telegraph Hill


Oh yeah! That too!

--------------------


Regards,

David Marten
Go to the top of the page
 
AP_Ben
post Feb 26 2020, 09:36 AM
Post#15



Posts: 41
Joined: 11-February 20



You are of course right David, however fixing it still gives the same error.
this is the actual line of code as it now appears:

CODE
strSQL = "SELECT * FROM [audit trail] WHERE ([type] = IN ('SR', BR', 'BP', 'PP')) " & ";"


Go to the top of the page
 
AP_Ben
post Feb 26 2020, 09:40 AM
Post#16



Posts: 41
Joined: 11-February 20



I haven't been able to find a reference to the IN function either as yet, however
CODE
SELECT * FROM [audit trail] WHERE ([type] IN ('SR', BR', 'BP', 'PP'));


still gives the syntax error
Go to the top of the page
 
cheekybuddha
post Feb 26 2020, 09:42 AM
Post#17


UtterAccess Moderator
Posts: 12,622
Joined: 6-December 03
From: Telegraph Hill


Are you doing this in a query or in VBA?

--------------------


Regards,

David Marten
Go to the top of the page
 
AP_Ben
post Feb 26 2020, 09:59 AM
Post#18



Posts: 41
Joined: 11-February 20



I'm doing this in VBA, trying to copy the code that would have been produced if I made a query using the designer, so the user can choose off the form which transaction types to include in the display.

I have just had some success, using my original code to check the checkboxes, and now using the AcctRef field converted to a text string. However it now comes up with a parameter box asking for the value of textAcct (my defined string equivalent of the numeric acctRef) and when I type in 1200 or whatever, it gives me the correct results (at first glance anyway)

so now I possibly need to create an invisible text box on the form, fill it with textAcct, and then include the reference to it in the SQL string.

Which seems a bit back-to-front, and maybe a good old cuppa tea will straighten the braincells a little on that one, but it looks like some variant of that should work

This is the initial bit of the string construction, which seems to work ok
CODE
strWhere = "WHERE (("

If (Me.chkSR) Then
strWhere = strWhere & "[type] = ""SR"" OR "
End If

If (Me.chkBR) Then
strWhere = strWhere & "[type] = ""BR"" OR "
End If

If (Me.chkPP) Then
strWhere = strWhere & "[type] = ""PP"" OR "
End If

If (Me.chkBP) Then
strWhere = strWhere & "[type] = ""BP"" OR "
End If


I'm stripping off the last 'OR' etc
Ben
Go to the top of the page
 
cheekybuddha
post Feb 26 2020, 10:07 AM
Post#19


UtterAccess Moderator
Posts: 12,622
Joined: 6-December 03
From: Telegraph Hill


I was more curious about Post#16.

If you are doing that in a query I wondered where the syntax error might be. It looks good to me, even if you have unnecessary brackets.

If you were using it in a VBA string then the error might be caused by something else.


I just noticed you omitted the single quote before BR again!

--------------------


Regards,

David Marten
Go to the top of the page
 
AP_Ben
post Feb 26 2020, 10:27 AM
Post#20



Posts: 41
Joined: 11-February 20



wasn't me gov, the computer dunnit!

so re-editing that line again (and not fixing the wrong commented-out line like might have happened previously) it seems to run without an error, and certainly produces some results which I assume at this point are correct. So I now am able to use the IN expression. As I have a string construct that works now I'll leave the reconstruction using 'in' for another time, but am adding this to my internal knowedgebase document


The full string I am currently generating (which works) - with only the SR checkbox ticked - is
CODE
SELECT * FROM [audit trail] WHERE (([type] = "SR" ) AND (([debitcode] = [Forms]![frmBankActivity]![textAcct]) OR ([CreditCode] = [Forms]![frmBankActivity]![textAcct]) ))


I am missing the ORDER BY at the end I just noticed, but that was working before so should be just a question of uncommenting that, removing all the other spurious comments and debug lines, and seeing if what I have left is clear and easy to read and follow. (ticking the other checkboxes also works btw)

And I probably will need to add a date range clause as well at some point.

However many thanks to both of you and the quick responses which have helped me fix this

Ben
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    8th April 2020 - 05:06 PM