Query builder - changing the query (1 Viewer)

DavidCon · Oct 9, 2018

Hi All,

This is driving me made and I can't find the solution if you please advise?

I build the following query in access 2010

Code:

SELECT TblReconciliations.RecMonthEndDate, TblSuppliers.SupplierName, TblReconciliations.RecUser, TblStatementInvoices.InvoiceNumber, TblStatementInvoices.InvoicePurchaseOrder, TblStatementInvoices.InvoiceDate, TblStatementInvoices.InvoiceValue, TblStatementInvoices.InvoiceTotalAccrued, TblStatementInvoices.InvoiceHospital, TblInvoiceStatus.StatusDesc, TblStatementInvoices.InvoiceOnNewDart, TblStatementInvoices.InvoiceOnOldDart
FROM TblInvoiceStatus INNER JOIN (TblSuppliers INNER JOIN (TblReconciliations INNER JOIN TblStatementInvoices ON TblReconciliations.RecID = TblStatementInvoices.InvoiceRecID) ON TblSuppliers.SupplierID = TblReconciliations.RecSupplier) ON TblInvoiceStatus.StatusID = TblStatementInvoices.InvoiceLiabilityStatus
WHERE (((TblReconciliations.RecMonthEndDate)=[forms]![MonthEnd]![CmbSelectMth]) AND ((TblSuppliers.SupplierName)=[Forms]![MonthEnd]![CmdSupplierSelect] Or (TblSuppliers.SupplierName)=[Forms]![MonthEnd]![CmdSupplierSelect]) AND ((TblInvoiceStatus.StatusDesc)=[Forms]![MonthEnd]![CMBStatusDesc] Or (TblInvoiceStatus.StatusDesc)=[Forms]![MonthEnd]![CmdSupplierSelect]) AND ((TblReconciliations.RecMonthEndSubmitted)=True));

The query works as intended when I test it but the funny thing is Access then changes the sql which breaks it. Here is the amended SQL

Code:

SELECT TblReconciliations.RecMonthEndDate, TblSuppliers.SupplierName, TblReconciliations.RecUser, TblStatementInvoices.InvoiceNumber, TblStatementInvoices.InvoicePurchaseOrder, TblStatementInvoices.InvoiceDate, TblStatementInvoices.InvoiceValue, TblStatementInvoices.InvoiceTotalAccrued, TblStatementInvoices.InvoiceHospital, TblInvoiceStatus.StatusDesc, TblStatementInvoices.InvoiceOnNewDart, TblStatementInvoices.InvoiceOnOldDart
FROM TblInvoiceStatus INNER JOIN (TblSuppliers INNER JOIN (TblReconciliations INNER JOIN TblStatementInvoices ON TblReconciliations.RecID = TblStatementInvoices.InvoiceRecID) ON TblSuppliers.SupplierID = TblReconciliations.RecSupplier) ON TblInvoiceStatus.StatusID = TblStatementInvoices.InvoiceLiabilityStatus
WHERE (((TblReconciliations.RecMonthEndDate)=[forms]![MonthEnd]![CmbSelectMth]) AND ((TblSuppliers.SupplierName)=[Forms]![MonthEnd]![CmdSupplierSelect]) AND ((TblInvoiceStatus.StatusDesc)=[Forms]![MonthEnd]![CMBStatusDesc]) AND ((TblReconciliations.RecMonthEndSubmitted)=True)) OR (((TblReconciliations.RecMonthEndDate)=[forms]![MonthEnd]![CmbSelectMth]) AND ((TblInvoiceStatus.StatusDesc)=[Forms]![MonthEnd]![CMBStatusDesc]) AND ((TblReconciliations.RecMonthEndSubmitted)=True) AND (([Forms]![MonthEnd]![CmdSupplierSelect]) Is Null)) OR (((TblReconciliations.RecMonthEndDate)=[forms]![MonthEnd]![CmbSelectMth]) AND ((TblSuppliers.SupplierName)=[Forms]![MonthEnd]![CmdSupplierSelect]) AND ((TblReconciliations.RecMonthEndSubmitted)=True) AND (([Forms]![MonthEnd]![CmdSupplierSelect]) Is Null)) OR (((TblReconciliations.RecMonthEndDate)=[forms]![MonthEnd]![CmbSelectMth]) AND ((TblReconciliations.RecMonthEndSubmitted)=True) AND (([Forms]![MonthEnd]![CmdSupplierSelect]) Is Null And ([Forms]![MonthEnd]![CmdSupplierSelect]) Is Null));

Any help will be appreciated - I have no idea how to fix this!

Minty · Oct 9, 2018

Access wouldn't add those "And YourFormReferences is Null" on it's own, you must have added them in the criteria.

When formatted for readability your cut and paste looks wrong and appears to have the same criteria twice in the second version;

Code:

SELECT TblReconciliations.RecMonthEndDate, TblSuppliers.SupplierName, TblReconciliations.RecUser, TblStatementInvoices.InvoiceNumber, 
TblStatementInvoices.InvoicePurchaseOrder, TblStatementInvoices.InvoiceDate, TblStatementInvoices.InvoiceValue, 
TblStatementInvoices.InvoiceTotalAccrued, TblStatementInvoices.InvoiceHospital, TblInvoiceStatus.StatusDesc, 
TblStatementInvoices.InvoiceOnNewDart, TblStatementInvoices.InvoiceOnOldDart
FROM TblInvoiceStatus INNER JOIN (TblSuppliers INNER JOIN (TblReconciliations INNER JOIN TblStatementInvoices 
ON TblReconciliations.RecID = TblStatementInvoices.InvoiceRecID) 
ON TblSuppliers.SupplierID = TblReconciliations.RecSupplier) 
ON TblInvoiceStatus.StatusID = TblStatementInvoices.InvoiceLiabilityStatus
WHERE (((TblReconciliations.RecMonthEndDate)=[forms]![MonthEnd]![CmbSelectMth]) 
AND ((TblSuppliers.SupplierName)=[Forms]![MonthEnd]![CmdSupplierSelect] 
Or (TblSuppliers.SupplierName)=[Forms]![MonthEnd]![CmdSupplierSelect]) 
AND ((TblInvoiceStatus.StatusDesc)=[Forms]![MonthEnd]![CMBStatusDesc] 
Or (TblInvoiceStatus.StatusDesc)=[Forms]![MonthEnd]![CmdSupplierSelect]) 
AND ((TblReconciliations.RecMonthEndSubmitted)=True));


SELECT TblReconciliations.RecMonthEndDate, TblSuppliers.SupplierName, TblReconciliations.RecUser, TblStatementInvoices.InvoiceNumber, 
TblStatementInvoices.InvoicePurchaseOrder, TblStatementInvoices.InvoiceDate, TblStatementInvoices.InvoiceValue, 
TblStatementInvoices.InvoiceTotalAccrued, TblStatementInvoices.InvoiceHospital, TblInvoiceStatus.StatusDesc, 
TblStatementInvoices.InvoiceOnNewDart, TblStatementInvoices.InvoiceOnOldDart
FROM TblInvoiceStatus INNER JOIN (TblSuppliers INNER JOIN (TblReconciliations INNER JOIN TblStatementInvoices 
ON TblReconciliations.RecID = TblStatementInvoices.InvoiceRecID) 
ON TblSuppliers.SupplierID = TblReconciliations.RecSupplier) 
ON TblInvoiceStatus.StatusID = TblStatementInvoices.InvoiceLiabilityStatus
WHERE (((TblReconciliations.RecMonthEndDate)=[forms]![MonthEnd]![CmbSelectMth]) 
AND ((TblSuppliers.SupplierName)=[Forms]![MonthEnd]![CmdSupplierSelect]) 
AND ((TblInvoiceStatus.StatusDesc)=[Forms]![MonthEnd]![CMBStatusDesc]) 
AND ((TblReconciliations.RecMonthEndSubmitted)=True)) 
OR (((TblReconciliations.RecMonthEndDate)=[forms]![MonthEnd]![CmbSelectMth]) 
AND ((TblInvoiceStatus.StatusDesc)=[Forms]![MonthEnd]![CMBStatusDesc]) 
AND ((TblReconciliations.RecMonthEndSubmitted)=True) 
AND (([Forms]![MonthEnd]![CmdSupplierSelect]) Is Null)) 
OR (((TblReconciliations.RecMonthEndDate)=[forms]![MonthEnd]![CmbSelectMth]) 
AND ((TblSuppliers.SupplierName)=[Forms]![MonthEnd]![CmdSupplierSelect]) 
AND ((TblReconciliations.RecMonthEndSubmitted)=True) 
AND (([Forms]![MonthEnd]![CmdSupplierSelect]) Is Null)) 
OR (((TblReconciliations.RecMonthEndDate)=[forms]![MonthEnd]![CmbSelectMth]) 
AND ((TblReconciliations.RecMonthEndSubmitted)=True) 
AND (([Forms]![MonthEnd]![CmdSupplierSelect]) Is Null 
And ([Forms]![MonthEnd]![CmdSupplierSelect]) Is Null));

plog · Oct 9, 2018

I'm not going to wade through the code to fix it, but my guess is its because you've ambigously combined ANDs and ORs:

Code:

... Or (TblSuppliers.SupplierName)=[Forms]![MonthEnd]![CmdSupplierSelect]) AND ...

When you do that people nor computers know what you actually mean--ANDs and ORs don't play well together logically which is why you need to seperate them by parenthesis. Access knows this and does so. But in doing so it makes guesses and adds code to account for the parenthesis splitting up your ANDs.

Here's an example:

true OR false AND false AND true

Does that resolve to true or false? The answer is--it depends:

true OR ((false AND false) AND (true)) = true
(true OR false) AND (false AND true) = false

The placement of parenthesis can make that initial statement true or false. I believe that's what happened to your code and Access guessed what you meant and added parenthesis as it saw fit--and didn't choose what you wanted.

You can fix your initial code by adding parentheis to seperate your ANDs and ORs correctly. Then when you go into design view Access won't guess and add the extra code.

CJ_London · Oct 9, 2018

what does 'break it' mean? you get an error? the wrong result?

the two sets of criteria are not the same - I think you must have an access gremlin who has added additional criteria such as

[Forms]![MonthEnd]![CmdSupplierSelect]) Is Null

Or perhaps you added it unwittingly?

DavidCon · Oct 10, 2018

Thanks for your responses (I rushed the last post as it was late in the day and I had had enough!)

Do you mind if I back up slightly here and explain what I’m trying to achieve.

Basically I have three combo boxes on a form which I want the query to reference

Each of the combo boxes are pulling data from either a table or a query.

These are: CmbSelectMth, CmdSupplierSelect & CMBStatusDesc

What I would like to happen is: if CmdSupplierSelect & CMBStatusDesc is left blank then the query would return everything and if a selection is made in CmdSupplierSelect & CMBStatusDesc then the query would just return that criteria.

They way I’m trying to do this is:

CmdSupplierSelect
· [Forms]![MonthEnd]![CMBStatusDesc] Or [Forms]![MonthEnd]![CMBStatusDesc] Is Null

CMBStatusDesc
· [Forms]![MonthEnd]![CMBStatusDesc] Or [Forms]![MonthEnd]![CMBStatusDesc] Is Null

I guess the question is am I approaching this correctly and is there alternative way I can display all records if nothing is selected?

This is the sql when I put it in query builder:

Code:

[SIZE=3][FONT=Calibri]WHERE (((TblReconciliations.RecMonthEndDate)=[forms]![MonthEnd]![CmbSelectMth]) [/FONT][/SIZE]
[SIZE=3][FONT=Calibri]AND ((TblSuppliers.SupplierName)=[Forms]![MonthEnd]![CmdSupplierSelect] [/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Or [Forms]![MonthEnd]![CmdSupplierSelect] Is Null) [/FONT][/SIZE]
[SIZE=3][FONT=Calibri]AND ((TblInvoiceStatus.StatusDesc)=[Forms]![MonthEnd]![CMBStatusDesc][/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Or [Forms]![MonthEnd]![CMBStatusDesc] Is Null) [/FONT][/SIZE]
[FONT=Calibri][SIZE=3]AND ((TblReconciliations.RecMonthEndSubmitted)=True));[/SIZE][/FONT]

And this is what Access is converting it too:

Code:

[SIZE=3][FONT=Calibri]WHERE (((TblReconciliations.RecMonthEndDate)=[forms]![MonthEnd]![CmbSelectMth]) [/FONT][/SIZE]
[SIZE=3][FONT=Calibri]AND ((TblSuppliers.SupplierName)=[Forms]![MonthEnd]![CmdSupplierSelect]) [/FONT][/SIZE]
[SIZE=3][FONT=Calibri]AND ((TblInvoiceStatus.StatusDesc)=[Forms]![MonthEnd]![CMBStatusDesc]) [/FONT][/SIZE]
[SIZE=3][FONT=Calibri]AND ((TblReconciliations.RecMonthEndSubmitted)=True)) [/FONT][/SIZE]
[SIZE=3][FONT=Calibri]OR (((TblReconciliations.RecMonthEndDate)=[forms]![MonthEnd]![CmbSelectMth]) [/FONT][/SIZE]
[SIZE=3][FONT=Calibri]AND ((TblInvoiceStatus.StatusDesc)=[Forms]![MonthEnd]![CMBStatusDesc]) [/FONT][/SIZE]
[SIZE=3][FONT=Calibri]AND ((TblReconciliations.RecMonthEndSubmitted)=True) [/FONT][/SIZE]
[SIZE=3][FONT=Calibri]AND (([Forms]![MonthEnd]![CmdSupplierSelect]) Is Null)) [/FONT][/SIZE]
[SIZE=3][FONT=Calibri]OR (((TblReconciliations.RecMonthEndDate)=[forms]![MonthEnd]![CmbSelectMth]) [/FONT][/SIZE]
[SIZE=3][FONT=Calibri]AND ((TblSuppliers.SupplierName)=[Forms]![MonthEnd]![CmdSupplierSelect]) [/FONT][/SIZE]
[SIZE=3][FONT=Calibri]AND ((TblReconciliations.RecMonthEndSubmitted)=True)[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]AND (([Forms]![MonthEnd]![CmdSupplierSelect]) Is Null))[/FONT][/SIZE]
[FONT=Calibri][SIZE=3]OR (((TblReconciliations.RecMonthEndDate)=[forms]![MonthEnd]![CmbSelectMth]) [/SIZE][/FONT]
[SIZE=3][FONT=Calibri]AND ((TblReconciliations.RecMonthEndSubmitted)=True) [/FONT][/SIZE]
[SIZE=3][FONT=Calibri]AND (([Forms]![MonthEnd]![CmdSupplierSelect]) Is Null [/FONT][/SIZE]
[FONT=Calibri][SIZE=3]And ([Forms]![MonthEnd]![CmdSupplierSelect]) Is Null));[/SIZE][/FONT]

CJ_London · Oct 10, 2018

Just type

[Forms]![MonthEnd]![CMBStatusDesc] Or [Forms]![MonthEnd]![CMBStatusDesc] Is Null

as one line in your criteria for your StatusDesc field

put the same for the other fields on the same criteria line

Access will rewrite the sql as required to apply the logic. When you save the query and reopen it, it will reorganise the query grid accordingly

Alternatively surround your criteria with brackets

([Forms]![MonthEnd]![CMBStatusDesc] Or [Forms]![MonthEnd]![CMBStatusDesc] Is Null)

query grid will still be reorganised, but perhaps in a more readable layout

Minty · Oct 10, 2018

In addition to CJ's advice, your logic description and your original query are slightly at odds I think (and if not I've misunderstood, so apologies in advance).

I think you want the criteria on separate lines (OR's) or you will only get an answer if all the criteria are completed ?

DavidCon · Oct 10, 2018

Thanks all for the replies. Got this working as intended

Code:

[SIZE=3][FONT=Calibri]WHERE (((TblReconciliations.RecMonthEndDate)=[forms]![MonthEnd]![CmbSelectMth]) [/FONT][/SIZE]
[SIZE=3][FONT=Calibri]AND ((([TblSuppliers].[SupplierName])=[Forms]![MonthEnd]![CmdSupplierSelect] [/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Or [Forms]![MonthEnd]![CmdSupplierSelect] Is Null)<>False) [/FONT][/SIZE]
[SIZE=3][FONT=Calibri]AND ((([TblInvoiceStatus].[StatusDesc])=[Forms]![MonthEnd]![CMBStatusDesc] [/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Or [Forms]![MonthEnd]![CMBStatusDesc] Is Null)<>False) [/FONT][/SIZE]
[FONT=Calibri][SIZE=3]AND ((TblReconciliations.RecMonthEndSubmitted)=True));[/SIZE][/FONT]

Query builder - changing the query (1 Viewer)

DavidCon

Registered User.

Minty

AWF VIP

plog

Banishment Pending

CJ_London

Super Moderator

DavidCon

Registered User.

CJ_London

Super Moderator

Minty

AWF VIP

DavidCon

Registered User.

Users who are viewing this thread