This problem really eludes me

  • Thread starter Thread starter joe blow
  • Start date Start date
J

joe blow

Ok, 99% of the time when I have a problem, I can find the
solution here or at google, or the KB. But this one is
really agitating me.

I created a query from some linked tables. I wasn't sure
how to create the query in design view, so I wrote the SQL
statement instead. Everything went fine, syntax was ok, no
problems. I ran the query and everything was good.

Now, I noticed that when I created the SQL query and saved
it, Access kind of rearranged it to its own look (I had it
parsed and on mutiple lines for me to not get confused).
No problem, query still works ok. Then, when I closed the
query and opened it again, it wouldn't open and the ODBC
connector times out. So I checked the SQL view, and Access
changed it a second time, only this time it completely
reconstructed the entire sql string, so it doesn't match
what it had only a minute ago. This new sql string
(everything looks identical in design view) seems to be
too complicated, or has serious errors.

Is this how it always will be? I'd like to think The first
time it rearranged my sql string to the proper look was
fine, but a 2nd rearrangement cripples the query. So I
paste back the original sql string and perform the query,
and the data comes up immediately. After I save the query,
it mangles my sql string and the query times out again. Is
there a hidden setting that will tell access not to
fix/rearrange? I can post the before and after sql string,
if requested, so you can see what I mean.

TIA!
 
joe said:
Now, I noticed that when I created the SQL query and saved
it, Access kind of rearranged it to its own look (I had it
parsed and on mutiple lines for me to not get confused).
Click to expand...

Yes, I know that. No, you cannot tell Access not to rearrange the SQL. I
can't remember my workaround (wow! what a Help do I provide :-) ) but
you can always re-store the original SQL. That can be done from code:

dim qd as querydef
set qd=currentdb.querydefs("yourquery")
qd.sql=yourSQL ' implicit save
set qd=nothing
 
Ok, this is the before. This is how I think it should
look.

SELECT munis_prempmst.prem_lname,
munis_prempmst.prem_fname, munis_premppay.prep_emp,
munis_premppay.prep_pay, munis_premppay.prep_hrly_rt,
munis_premppay.prep_daily_rt, munis_premppay.prep_per_sal,
munis_premppay.prep_loc, munis_prlocatn.prln_long,
munis_prempmst.prem_act_stat, munis_prempmst.prem_p_freq,
munis_prempmst.prem_proj, munis_premppay.prep_emp
FROM munis_prempmst, munis_premppay, munis_prlocatn
WHERE ((munis_premppay.prep_emp)=(munis_prempmst.prem_emp)
And (munis_premppay.prep_proj)=(munis_prempmst.prem_proj))
And (munis_prempmst.prem_loc)=(munis_prlocatn.prln_code)
And ((munis_premppay.prep_pay)>=101 And
(munis_premppay.prep_pay)<=108) And
(((munis_premppay.prep_loc)>='T114' And
(munis_premppay.prep_loc)<'T130') Or
((munis_premppay.prep_loc)>'T130' And
(munis_premppay.prep_loc)<'T320') Or
((munis_premppay.prep_loc)>'T320' And
(munis_premppay.prep_loc)<'T340') Or
((munis_premppay.prep_loc)>'T340' And
(munis_premppay.prep_loc)<'T410') Or
((munis_premppay.prep_loc)>'T410' And
(munis_premppay.prep_loc)<'T420') Or
((munis_premppay.prep_loc)>'T420' And
(munis_premppay.prep_loc)<'T430') Or
((munis_premppay.prep_loc)>'T430' And
(munis_premppay.prep_loc)<'T440') Or
((munis_premppay.prep_loc)>'T440' And
(munis_premppay.prep_loc)<'T450') Or
((munis_premppay.prep_loc)>'T450' And
(munis_premppay.prep_loc)<='T910')) And
munis_prempmst.prem_act_stat='A' And
munis_prempmst.prem_p_freq='W' And
munis_prempmst.prem_proj=0
ORDER BY munis_premppay.prep_loc,
munis_prempmst.prem_lname, munis_prempmst.prem_fname,
munis_premppay.prep_emp;

After look, causing the timeout:

SELECT munis_prempmst.prem_lname,
munis_prempmst.prem_fname, munis_premppay.prep_emp,
munis_premppay.prep_pay, munis_premppay.prep_hrly_rt,
munis_premppay.prep_daily_rt, munis_premppay.prep_per_sal,
munis_premppay.prep_loc, munis_prlocatn.prln_long,
munis_prempmst.prem_act_stat, munis_prempmst.prem_p_freq,
munis_prempmst.prem_proj, munis_premppay.prep_emp
FROM munis_prempmst, munis_premppay, munis_prlocatn
WHERE (((munis_premppay.prep_emp)=([munis_prempmst].
[prem_emp])) AND ((munis_premppay.prep_pay)>=101 And
(munis_premppay.prep_pay)<=108) AND
((munis_premppay.prep_loc)>='T114' And
(munis_premppay.prep_loc)<'T130') AND
((munis_prempmst.prem_act_stat)='A') AND
((munis_prempmst.prem_p_freq)='W') AND
((munis_prempmst.prem_proj)=0) AND
((munis_premppay.prep_proj)=([munis_prempmst].
[prem_proj])) AND ((munis_prempmst.prem_loc)=
([munis_prlocatn].[prln_code]))) OR
(((munis_premppay.prep_emp)=([munis_prempmst].[prem_emp]))
AND ((munis_premppay.prep_pay)>=101 And
(munis_premppay.prep_pay)<=108) AND
((munis_premppay.prep_loc)>'T130' And
(munis_premppay.prep_loc)<'T320') AND
((munis_prempmst.prem_act_stat)='A') AND
((munis_prempmst.prem_p_freq)='W') AND
((munis_prempmst.prem_proj)=0) AND
((munis_premppay.prep_proj)=([munis_prempmst].
[prem_proj])) AND ((munis_prempmst.prem_loc)=
([munis_prlocatn].[prln_code]))) OR
(((munis_premppay.prep_emp)=([munis_prempmst].[prem_emp]))
AND ((munis_premppay.prep_pay)>=101 And
(munis_premppay.prep_pay)<=108) AND
((munis_premppay.prep_loc)>'T320' And
(munis_premppay.prep_loc)<'T340') AND
((munis_prempmst.prem_act_stat)='A') AND
((munis_prempmst.prem_p_freq)='W') AND
((munis_prempmst.prem_proj)=0) AND
((munis_premppay.prep_proj)=([munis_prempmst].
[prem_proj])) AND ((munis_prempmst.prem_loc)=
([munis_prlocatn].[prln_code]))) OR
(((munis_premppay.prep_emp)=([munis_prempmst].[prem_emp]))
AND ((munis_premppay.prep_pay)>=101 And
(munis_premppay.prep_pay)<=108) AND
((munis_premppay.prep_loc)>'T340' And
(munis_premppay.prep_loc)<'T410') AND
((munis_prempmst.prem_act_stat)='A') AND
((munis_prempmst.prem_p_freq)='W') AND
((munis_prempmst.prem_proj)=0) AND
((munis_premppay.prep_proj)=([munis_prempmst].
[prem_proj])) AND ((munis_prempmst.prem_loc)=
([munis_prlocatn].[prln_code]))) OR
(((munis_premppay.prep_emp)=([munis_prempmst].[prem_emp]))
AND ((munis_premppay.prep_pay)>=101 And
(munis_premppay.prep_pay)<=108) AND
((munis_premppay.prep_loc)>'T410' And
(munis_premppay.prep_loc)<'T420') AND
((munis_prempmst.prem_act_stat)='A') AND
((munis_prempmst.prem_p_freq)='W') AND
((munis_prempmst.prem_proj)=0) AND
((munis_premppay.prep_proj)=([munis_prempmst].
[prem_proj])) AND ((munis_prempmst.prem_loc)=
([munis_prlocatn].[prln_code]))) OR
(((munis_premppay.prep_emp)=([munis_prempmst].[prem_emp]))
AND ((munis_premppay.prep_pay)>=101 And
(munis_premppay.prep_pay)<=108) AND
((munis_premppay.prep_loc)>'T420' And
(munis_premppay.prep_loc)<'T430') AND
((munis_prempmst.prem_act_stat)='A') AND
((munis_prempmst.prem_p_freq)='W') AND
((munis_prempmst.prem_proj)=0) AND
((munis_premppay.prep_proj)=([munis_prempmst].
[prem_proj])) AND ((munis_prempmst.prem_loc)=
([munis_prlocatn].[prln_code]))) OR
(((munis_premppay.prep_emp)=([munis_prempmst].[prem_emp]))
AND ((munis_premppay.prep_pay)>=101 And
(munis_premppay.prep_pay)<=108) AND
((munis_premppay.prep_loc)>'T430' And
(munis_premppay.prep_loc)<'T440') AND
((munis_prempmst.prem_act_stat)='A') AND
((munis_prempmst.prem_p_freq)='W') AND
((munis_prempmst.prem_proj)=0) AND
((munis_premppay.prep_proj)=([munis_prempmst].
[prem_proj])) AND ((munis_prempmst.prem_loc)=
([munis_prlocatn].[prln_code]))) OR
(((munis_premppay.prep_emp)=([munis_prempmst].[prem_emp]))
AND ((munis_premppay.prep_pay)>=101 And
(munis_premppay.prep_pay)<=108) AND
((munis_premppay.prep_loc)>'T440' And
(munis_premppay.prep_loc)<'T450') AND
((munis_prempmst.prem_act_stat)='A') AND
((munis_prempmst.prem_p_freq)='W') AND
((munis_prempmst.prem_proj)=0) AND
((munis_premppay.prep_proj)=([munis_prempmst].
[prem_proj])) AND ((munis_prempmst.prem_loc)=
([munis_prlocatn].[prln_code]))) OR
(((munis_premppay.prep_emp)=([munis_prempmst].[prem_emp]))
AND ((munis_premppay.prep_pay)>=101 And
(munis_premppay.prep_pay)<=108) AND
((munis_premppay.prep_loc)>'T450' And
(munis_premppay.prep_loc)<='T910') AND
((munis_prempmst.prem_act_stat)='A') AND
((munis_prempmst.prem_p_freq)='W') AND
((munis_prempmst.prem_proj)=0) AND
((munis_premppay.prep_proj)=([munis_prempmst].
[prem_proj])) AND ((munis_prempmst.prem_loc)=
([munis_prlocatn].[prln_code])))
ORDER BY munis_premppay.prep_loc,
munis_prempmst.prem_lname, munis_prempmst.prem_fname,
munis_premppay.prep_emp;

If you can figure it out, your the best!
 
Hi Joe,

Hmmm.. Thanks for the sql text <g>.

I know Access rewrites the sql text of queries, particularly those involving
combinations of AND's and OR's. I haven't really examined your sql text in
detail, but could it have something to do with Access limitations on
queries?

This is from the help file:
Number of ANDs in a WHERE or HAVING clause 40


The sql text as you originally wrote it has less than 40 AND's but Access
modifies the text and somehow ends up with more than 40. I'm not sure if
this is the problem though. You might play around with the sql text created
by Access and reduce the number of AND's to below 40 and see if the query
all of a sudden starts to work again.

Immanuel Sibero





Joe Blow said:
Ok, this is the before. This is how I think it should
look.

SELECT munis_prempmst.prem_lname,
munis_prempmst.prem_fname, munis_premppay.prep_emp,
munis_premppay.prep_pay, munis_premppay.prep_hrly_rt,
munis_premppay.prep_daily_rt, munis_premppay.prep_per_sal,
munis_premppay.prep_loc, munis_prlocatn.prln_long,
munis_prempmst.prem_act_stat, munis_prempmst.prem_p_freq,
munis_prempmst.prem_proj, munis_premppay.prep_emp
FROM munis_prempmst, munis_premppay, munis_prlocatn
WHERE ((munis_premppay.prep_emp)=(munis_prempmst.prem_emp)
And (munis_premppay.prep_proj)=(munis_prempmst.prem_proj))
And (munis_prempmst.prem_loc)=(munis_prlocatn.prln_code)
And ((munis_premppay.prep_pay)>=101 And
(munis_premppay.prep_pay)<=108) And
(((munis_premppay.prep_loc)>='T114' And
(munis_premppay.prep_loc)<'T130') Or
((munis_premppay.prep_loc)>'T130' And
(munis_premppay.prep_loc)<'T320') Or
((munis_premppay.prep_loc)>'T320' And
(munis_premppay.prep_loc)<'T340') Or
((munis_premppay.prep_loc)>'T340' And
(munis_premppay.prep_loc)<'T410') Or
((munis_premppay.prep_loc)>'T410' And
(munis_premppay.prep_loc)<'T420') Or
((munis_premppay.prep_loc)>'T420' And
(munis_premppay.prep_loc)<'T430') Or
((munis_premppay.prep_loc)>'T430' And
(munis_premppay.prep_loc)<'T440') Or
((munis_premppay.prep_loc)>'T440' And
(munis_premppay.prep_loc)<'T450') Or
((munis_premppay.prep_loc)>'T450' And
(munis_premppay.prep_loc)<='T910')) And
munis_prempmst.prem_act_stat='A' And
munis_prempmst.prem_p_freq='W' And
munis_prempmst.prem_proj=0
ORDER BY munis_premppay.prep_loc,
munis_prempmst.prem_lname, munis_prempmst.prem_fname,
munis_premppay.prep_emp;

After look, causing the timeout:

SELECT munis_prempmst.prem_lname,
munis_prempmst.prem_fname, munis_premppay.prep_emp,
munis_premppay.prep_pay, munis_premppay.prep_hrly_rt,
munis_premppay.prep_daily_rt, munis_premppay.prep_per_sal,
munis_premppay.prep_loc, munis_prlocatn.prln_long,
munis_prempmst.prem_act_stat, munis_prempmst.prem_p_freq,
munis_prempmst.prem_proj, munis_premppay.prep_emp
FROM munis_prempmst, munis_premppay, munis_prlocatn
WHERE (((munis_premppay.prep_emp)=([munis_prempmst].
[prem_emp])) AND ((munis_premppay.prep_pay)>=101 And
(munis_premppay.prep_pay)<=108) AND
((munis_premppay.prep_loc)>='T114' And
(munis_premppay.prep_loc)<'T130') AND
((munis_prempmst.prem_act_stat)='A') AND
((munis_prempmst.prem_p_freq)='W') AND
((munis_prempmst.prem_proj)=0) AND
((munis_premppay.prep_proj)=([munis_prempmst].
[prem_proj])) AND ((munis_prempmst.prem_loc)=
([munis_prlocatn].[prln_code]))) OR
(((munis_premppay.prep_emp)=([munis_prempmst].[prem_emp]))
AND ((munis_premppay.prep_pay)>=101 And
(munis_premppay.prep_pay)<=108) AND
((munis_premppay.prep_loc)>'T130' And
(munis_premppay.prep_loc)<'T320') AND
((munis_prempmst.prem_act_stat)='A') AND
((munis_prempmst.prem_p_freq)='W') AND
((munis_prempmst.prem_proj)=0) AND
((munis_premppay.prep_proj)=([munis_prempmst].
[prem_proj])) AND ((munis_prempmst.prem_loc)=
([munis_prlocatn].[prln_code]))) OR
(((munis_premppay.prep_emp)=([munis_prempmst].[prem_emp]))
AND ((munis_premppay.prep_pay)>=101 And
(munis_premppay.prep_pay)<=108) AND
((munis_premppay.prep_loc)>'T320' And
(munis_premppay.prep_loc)<'T340') AND
((munis_prempmst.prem_act_stat)='A') AND
((munis_prempmst.prem_p_freq)='W') AND
((munis_prempmst.prem_proj)=0) AND
((munis_premppay.prep_proj)=([munis_prempmst].
[prem_proj])) AND ((munis_prempmst.prem_loc)=
([munis_prlocatn].[prln_code]))) OR
(((munis_premppay.prep_emp)=([munis_prempmst].[prem_emp]))
AND ((munis_premppay.prep_pay)>=101 And
(munis_premppay.prep_pay)<=108) AND
((munis_premppay.prep_loc)>'T340' And
(munis_premppay.prep_loc)<'T410') AND
((munis_prempmst.prem_act_stat)='A') AND
((munis_prempmst.prem_p_freq)='W') AND
((munis_prempmst.prem_proj)=0) AND
((munis_premppay.prep_proj)=([munis_prempmst].
[prem_proj])) AND ((munis_prempmst.prem_loc)=
([munis_prlocatn].[prln_code]))) OR
(((munis_premppay.prep_emp)=([munis_prempmst].[prem_emp]))
AND ((munis_premppay.prep_pay)>=101 And
(munis_premppay.prep_pay)<=108) AND
((munis_premppay.prep_loc)>'T410' And
(munis_premppay.prep_loc)<'T420') AND
((munis_prempmst.prem_act_stat)='A') AND
((munis_prempmst.prem_p_freq)='W') AND
((munis_prempmst.prem_proj)=0) AND
((munis_premppay.prep_proj)=([munis_prempmst].
[prem_proj])) AND ((munis_prempmst.prem_loc)=
([munis_prlocatn].[prln_code]))) OR
(((munis_premppay.prep_emp)=([munis_prempmst].[prem_emp]))
AND ((munis_premppay.prep_pay)>=101 And
(munis_premppay.prep_pay)<=108) AND
((munis_premppay.prep_loc)>'T420' And
(munis_premppay.prep_loc)<'T430') AND
((munis_prempmst.prem_act_stat)='A') AND
((munis_prempmst.prem_p_freq)='W') AND
((munis_prempmst.prem_proj)=0) AND
((munis_premppay.prep_proj)=([munis_prempmst].
[prem_proj])) AND ((munis_prempmst.prem_loc)=
([munis_prlocatn].[prln_code]))) OR
(((munis_premppay.prep_emp)=([munis_prempmst].[prem_emp]))
AND ((munis_premppay.prep_pay)>=101 And
(munis_premppay.prep_pay)<=108) AND
((munis_premppay.prep_loc)>'T430' And
(munis_premppay.prep_loc)<'T440') AND
((munis_prempmst.prem_act_stat)='A') AND
((munis_prempmst.prem_p_freq)='W') AND
((munis_prempmst.prem_proj)=0) AND
((munis_premppay.prep_proj)=([munis_prempmst].
[prem_proj])) AND ((munis_prempmst.prem_loc)=
([munis_prlocatn].[prln_code]))) OR
(((munis_premppay.prep_emp)=([munis_prempmst].[prem_emp]))
AND ((munis_premppay.prep_pay)>=101 And
(munis_premppay.prep_pay)<=108) AND
((munis_premppay.prep_loc)>'T440' And
(munis_premppay.prep_loc)<'T450') AND
((munis_prempmst.prem_act_stat)='A') AND
((munis_prempmst.prem_p_freq)='W') AND
((munis_prempmst.prem_proj)=0) AND
((munis_premppay.prep_proj)=([munis_prempmst].
[prem_proj])) AND ((munis_prempmst.prem_loc)=
([munis_prlocatn].[prln_code]))) OR
(((munis_premppay.prep_emp)=([munis_prempmst].[prem_emp]))
AND ((munis_premppay.prep_pay)>=101 And
(munis_premppay.prep_pay)<=108) AND
((munis_premppay.prep_loc)>'T450' And
(munis_premppay.prep_loc)<='T910') AND
((munis_prempmst.prem_act_stat)='A') AND
((munis_prempmst.prem_p_freq)='W') AND
((munis_prempmst.prem_proj)=0) AND
((munis_premppay.prep_proj)=([munis_prempmst].
[prem_proj])) AND ((munis_prempmst.prem_loc)=
([munis_prlocatn].[prln_code])))
ORDER BY munis_premppay.prep_loc,
munis_prempmst.prem_lname, munis_prempmst.prem_fname,
munis_premppay.prep_emp;

If you can figure it out, your the best!



-----Original Message-----
Hi Joe

Yes, I would want to see the sql text.

Immanuel Sibero





.
Click to expand...
Click to expand...
 
Thanks for the info

I seem to have found a weird thing. If I create the SQL and save it, I noticed that it works OK UNTIL I go into design mode. Then it mangles the sql command. If I toggle between SQL and data view, but avoid the design view, it works fine. So perhaps there's a limitation in the design view mode

Put that on the bizarre list

Again, thanks! Maybe I can construct it better to cut down on the AND/OR keyword

Joe
 
Back
Top