stop accessing changing my sql

[Jaye7]

I have various scripts in one criteria row in my query and access does it's jobs and puts the data on numerous rows which makes it really difficult to modify my script easily.

I read that you can change your sql so that access won't change it but I am not sure where to change the script and what is needed.

My sql is

SELECT DISTINCT [Jobs - Job Control].FONDesc, [Jobs - Job Control].FONID, [Stk - Stock category - System].SysAbb, [Jobs - Job Control].Qty, [Jobs - Status financial].StatusAbb, [Jobs - Job Control].CASID, [Jobs - Job Control].FONDate, [Jobs - Job Control].FONDateAdj, [Jobs - Job Control].FONDateApp, [Jobs - Job Control].FONSLIInvDate, [Jobs - Job Control].StatusID, [Jobs - Status financial].StatusDesc, [Jobs - Job Control].SysID, [Stk - Stock category - System].SysDesc, [Jobs - Job Control].AgentRef, [Jobs - Job Control].ClientRef, [Jobs - Job Control].FONConNote, Q_KlikTek_Partial.KlikTek
  FROM (([Jobs - Job Control] LEFT JOIN [Jobs - Status financial] ON [Jobs - Job Control].StatusID = [Jobs - Status financial].StatusID) LEFT JOIN [Stk - Stock category - System] ON [Jobs - Job Control].SysID = [Stk - Stock category - System].SysID) LEFT JOIN Q_KlikTek_Partial ON [Jobs - Job Control].FONID = Q_KlikTek_Partial.FON
  WHERE ((([Jobs - Job Control].FONDesc) Like "*" & [Forms]![FONMain]![txt1] & "*" And ([Jobs - Job Control].FONDesc) Like "*" & [Forms]![FONMain]![txt2] & "*" And ([Jobs - Job Control].FONDesc) Like "*" & [Forms]![FONMain]![txt3] & "*" And ([Jobs - Job Control].FONDesc) Like "*" & [Forms]![FONMain]![txt4] & "*" And ([Jobs - Job Control].FONDesc) Like "*" & [Forms]![FONMain]![txt5] & "*" And ([Jobs - Job Control].FONDesc) Like "*" & [Forms]![FONMain]![txt6] & "*" And ([Jobs - Job Control].FONDesc) Like "*" & [Forms]![FONMain]![txt7] & "*" And ([Jobs - Job Control].FONDesc) Like "*" & [Forms]![FONMain]![txt8] & "*") AND (([Jobs - Job Control].CASID)=[FORMS]![FONMain]![txtAgent] Or [FORMS]![FONMain]![txtAgent] Is Null) AND (([Jobs - Job Control].FONSLIInvDate) Between [FORMS]![FONMain]![txtFromDate] And [FORMS]![FONMain]![txtToDate]) AND (([Jobs - Job Control].StatusID)=[FORMS]![FONMain]![txtStatus] Or [FORMS]![FONMain]![txtStatus] Is Null) AND (([Jobs - Job Control].SysID)=[FORMS]![FONMain]![txtSystem] Or [FORMS]![FONMain]![txtSystem] Is Null) AND (([Jobs - Job Control].AgentRef) Like [FORMS]![FONMain]![txtAgentRef] Or [FORMS]![FONMain]![txtAgentRef] Is Null) AND (([Jobs - Job Control].ClientRef) Like [FORMS]![FONMain]![txtAgentRef] Or [FORMS]![FONMain]![txtAgentRef] Is Null) AND (([Jobs - Job Control].FONConNote) Like [FORMS]![FONMain]![txtConNoteRef] Or [FORMS]![FONMain]![txtConNoteRef] Is Null) AND ((Q_KlikTek_Partial.KlikTek)=IIf([Forms]![FONMain]![fmeKlikTek]=2,-1,0) Or [Forms]![FONMain]![txtKlikTek] Is Null))
  ORDER BY [Jobs - Job Control].FONID DESC;

 

[pbaldy]

Yes, Access likes to "fix" your SQL.

The only way to stop it I'm aware of is to work in SQL view only. If you switch to design view, Access tries to visually represent the SQL, and often mangles it.

 

[Jaye7]

Oh, on google people stated that you can modify the sql string to stop it from changing it, I just am not sure how to change my sql.

This thread states that you can modify the sql to stop it happening.

http://www.access-programmers.co.uk/forums/showthread.php?t=161585

 

[Galaxiom]

Access will mess with your query. Working in SQL puts it off for a while but when you save it in design mode or close it will revert to its own version.

It can be stopped by appending a line that just reads UNION when you save the query.
This makes it into a Union query that the editor cannot handle so it leaves the SQL alone.

Of course this means the query won't run either.

I could not see anything in the linked thread that suggests it can be stopped.

The 1=1 is an inelegant technique used by some developers when concatenating SQL in VBA.

 

[namliam]

If you have the query being run from a form or something you can store your required SQL in VBA and stick it into the query object when required.

 

[Jaye7]

Hi Namliam,

Glad you replied as the thread link is one where you replied to.

I have currently use vba, however the script that I am using I have 10 different variations and then my boss wants to add tables and so forth in and then I have to start from scratch again, whereas if the query was readily available for at least one of the variations I can just modify that, as currently I list all the query criteria's in a spreadsheet and copy and paste them into the query.
I can use vba quite well in excel but modifying access scripts are new to me.

 

[Jaye7]

Thanks Galaxiom, I do need it to run though, so that's not going to work.

 

[namliam]

Didnt realize I was on the linked thread, though the linked thread is about creating SQL in VBA, once you stick it into an access object access is liable to add brackets and what else it deems needed... also it will remove any formatting you may have....
As far as I know there is no way to stop access from doing that bar sticking your sql into VBA and/or have a Notepad/Word/excel version tucked away somewhere ....

If you want to adjust SQL on the fly for non-developers, your only way to go about it is to do that in VBA...

Sample for that would be:

Dim mySelect as string, myFrom as string, myWhere as string

mySelect = " Select bla " & _
                 ", blabla ... "
myFrom = "   From        Thistable " & _
           " Inner Join  ThatTable on ... "
myWhere = " Where 1=1 "
If not isnull(thisControlOntheform) then
' Number fields
    myWhere = mywhere & " And SomeFieldIntable = " & thisControlOntheform & " " 
' Text fields
    myWhere = mywhere & " And SomeFieldIntable = """ & thisControlOntheform & """ "
' Date fields
    myWhere = mywhere & " And SomeFieldIntable = #" & thisControlOntheform & "# "
Endif
Currentdb.querydefs("qrySomething").sql = myselect & myfrom & mywhere

You can do endless variations on this to generate whatever sql is needed for the required request.

Take special notice of always having a space at the front and back of each added line to prevent unwanted concatinations, sure one space will be enough but if you are in the habbit of adding one in front and back, it dont hurt when you for get one.

While 1=1 may be inelegant it works and is readely used in reporting bigger environments like OBW or BODI... Even 1=2 is used a lot to (temporarily) disable certain brackets of where/and/or clauses for debugging purposses without having to worry about bracketing or the proper and/or syntax. It is a powerfull tool to use IMHO. not perse only an 'inelegant technique used in VBA'

It can also be used in the UNION construct, by adding WHERE 1=2 meaning the added union query will not add a record to your query, but still functions as a union... thus forcing the behaviour you are looking for. Only "but" is the requirement for the same number of fields in both queries and the same field types.... which may be tricky, or if only is an extra "thing", to do

 

[Jaye7]

Thanks Namliam