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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Why This Code Converted, Any Version    
 
   
access2009eg
post Sep 14 2019, 05:48 PM
Post#1



Posts: 1,126
Joined: 19-February 08



Hello Friends;
why this short code converted to the second long code ?
is there any way to prevent that ?

The reason is
very difficult to fix
very difficult to read
very difficult to manipulate

Notes:
I don't need another solution
Just want to know the reason

CODE
SELECT Table1.id, Table1.Mname, Table1.Job, Table1.salaray, Table1.gov, Table1.dep
FROM Table1
WHERE (((Table1.Mname)=[Forms]![Form1]![Mname] Or [Forms]![Form1]![Mname] Is Null) AND ((Table1.Job)=[Forms]![Form1]![Job] Or [Forms]![Form1]![Job] Is Null) AND ((Table1.salaray)=[Forms]![Form1]![salaray] Or [Forms]![Form1]![salaray] Is Null));



CODE
SELECT Table1.id, Table1.Mname, Table1.Job, Table1.salaray, Table1.gov, Table1.dep
FROM Table1
WHERE (((Table1.Mname)=[Forms]![Form1]![Mname]) AND ((Table1.Job)=[Forms]![Form1]![Job]) AND ((Table1.salaray)=[Forms]![Form1]![salaray])) OR (((Table1.Job)=[Forms]![Form1]![Job]) AND ((Table1.salaray)=[Forms]![Form1]![salaray]) AND (([Forms]![Form1]![Mname]) Is Null)) OR (((Table1.Mname)=[Forms]![Form1]![Mname]) AND ((Table1.salaray)=[Forms]![Form1]![salaray]) AND (([Forms]![Form1]![Job]) Is Null)) OR (((Table1.salaray)=[Forms]![Form1]![salaray]) AND (([Forms]![Form1]![Mname]) Is Null) AND (([Forms]![Form1]![Job]) Is Null)) OR (((Table1.Mname)=[Forms]![Form1]![Mname]) AND ((Table1.Job)=[Forms]![Form1]![Job]) AND (([Forms]![Form1]![salaray]) Is Null)) OR (((Table1.Job)=[Forms]![Form1]![Job]) AND (([Forms]![Form1]![Mname]) Is Null) AND (([Forms]![Form1]![salaray]) Is Null)) OR (((Table1.Mname)=[Forms]![Form1]![Mname]) AND (([Forms]![Form1]![Job]) Is Null) AND (([Forms]![Form1]![salaray]) Is Null)) OR ((([Forms]![Form1]![Mname]) Is Null) AND (([Forms]![Form1]![Job]) Is Null) AND (([Forms]![Form1]![salaray]) Is Null));

This post has been edited by access2009eg: Sep 14 2019, 05:48 PM

--------------------
I would like to Thank you for your help
Go to the top of the page
 
GroverParkGeorge
post Sep 14 2019, 06:10 PM
Post#2


UA Admin
Posts: 35,873
Joined: 20-June 02
From: Newcastle, WA


Where and how did this SQL (it's not code) "get converted"?

Without context, it's really hard to guess what you are asking.


--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
access2009eg
post Sep 14 2019, 06:33 PM
Post#3



Posts: 1,126
Joined: 19-February 08



using reference to control on a form inside query grid QBE

(Table1.Mname)=[Forms]![Form1]![Mname] Or [Forms]![Form1]![Mname] Is Null

if i reference to many control on the form on query

and close the query

and reopen it again

it converted to complex code in second code

--------------------
I would like to Thank you for your help
Go to the top of the page
 
GroverParkGeorge
post Sep 14 2019, 07:04 PM
Post#4


UA Admin
Posts: 35,873
Joined: 20-June 02
From: Newcastle, WA


Thanks for the background.

It appears that you are creating this query in the query grid by adding additional control references in the criteria rows. That's going to add to the number of references in the corresponding SQL, as you see. Access does its best to convert the VISUAL interface of the QBE to valid SQL, so that's what is happening.

In other words, every time you add something to a criteria in the QBE, Access has to add additional elements in the SQL.
This post has been edited by GroverParkGeorge: Sep 14 2019, 07:08 PM

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
orange999
post Sep 14 2019, 07:18 PM
Post#5



Posts: 1,968
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


It appears that your queries are being converted to the following (formatted with Poor SQL)

CODE
SELECT Table1.id
    ,Table1.Mname
    ,Table1.Job
    ,Table1.salaray
    ,Table1.gov
    ,Table1.dep
FROM Table1
WHERE (
        ((Table1.Mname) = [Forms] ! [Form1] ! [Mname])
        AND ((Table1.Job) = [Forms] ! [Form1] ! [Job])
        AND ((Table1.salaray) = [Forms] ! [Form1] ! [salaray])
        )
    OR (
        ((Table1.Job) = [Forms] ! [Form1] ! [Job])
        AND ((Table1.salaray) = [Forms] ! [Form1] ! [salaray])
        AND (([Forms] ! [Form1] ! [Mname]) IS NULL)
        )
    OR (
        ((Table1.Mname) = [Forms] ! [Form1] ! [Mname])
        AND ((Table1.salaray) = [Forms] ! [Form1] ! [salaray])
        AND (([Forms] ! [Form1] ! [Job]) IS NULL)
        )
    OR (
        ((Table1.salaray) = [Forms] ! [Form1] ! [salaray])
        AND (([Forms] ! [Form1] ! [Mname]) IS NULL)
        AND (([Forms] ! [Form1] ! [Job]) IS NULL)
        )
    OR (
        ((Table1.Mname) = [Forms] ! [Form1] ! [Mname])
        AND ((Table1.Job) = [Forms] ! [Form1] ! [Job])
        AND (([Forms] ! [Form1] ! [salaray]) IS NULL)
        )
    OR (
        ((Table1.Job) = [Forms] ! [Form1] ! [Job])
        AND (([Forms] ! [Form1] ! [Mname]) IS NULL)
        AND (([Forms] ! [Form1] ! [salaray]) IS NULL)
        )
    OR (
        ((Table1.Mname) = [Forms] ! [Form1] ! [Mname])
        AND (([Forms] ! [Form1] ! [Job]) IS NULL)
        AND (([Forms] ! [Form1] ! [salaray]) IS NULL)
        )
    OR (
        (([Forms] ! [Form1] ! [Mname]) IS NULL)
        AND (([Forms] ! [Form1] ! [Job]) IS NULL)
        AND (([Forms] ! [Form1] ! [salaray]) IS NULL)
        );


I don't know if this is helpful, since I believe George gave the answer.

But it seems to me that the query itself is looking for records with each of the 3 fields with or without values.
I could be wrong, but I think you'd get the same result set (values) if you removed the where clause completely.

--------------------
Good luck with your project!
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    17th October 2019 - 08:21 AM