Auto Populate Query

G

Graham Feeley

I have 2 tables as follows
AllRunners
Results
I have a query that works fine from a command button

SELECT AllRunners.RCDATE, AllRunners.track, AllRunners.RCTIME,
AllRunners.raceno, AllRunners.dist, AllRunners.strs, AllRunners.fsts,
AllRunners.df1, AllRunners.df2, AllRunners.df3, AllRunners.T1,
AllRunners.T2, AllRunners.T3, AllRunners.B1, AllRunners.B2, AllRunners.B3,
AllRunners.R1, AllRunners.R2, AllRunners.R3, AllRunners.D1, AllRunners.D2,
AllRunners.D3, AllRunners.C1, AllRunners.C2, AllRunners.C3, AllRunners.P1,
AllRunners.p2, AllRunners.p3, AllRunners.p4, Results.first
FROM AllRunners INNER JOIN Results ON (AllRunners.raceno =
Results.["raceno"]) AND (AllRunners.track = Results.["track"]) AND
(AllRunners.RCDATE = Results.MyDate)
WHERE (((AllRunners.RCDATE)=[Forms]![Form3]![Combo5])) AND
(([AllRunners]![P1])=[Results]![first]))
ORDER BY AllRunners.RCTIME;

On form 3 I have 1 combobox which selects a date
also have a text box names "Text15"
and a command button that runs a query

I would like to have this part of the query changable via the text box "AND
(([AllRunners]![P1])"


So really in essence i would like to change the sql to read instead of P1
to be any of these fields

df1
df2
df3
t1
t2
t3
b1
b2
b3
r1
r2
r3
d1
d2
d3
c1
c2
c3
p1
p2
p3
p4
and this would come from whatever I type into the Textbox15
Thanks in anticipation of a answer thats works
Graham
 
D

Damon Heron

How about (snippet):
(([AllRunners].[" & Text15 & "])=[Results]![first]))ORDER BY.....

Having said that, I question the design of your table. It seems like a flat
file rather than relational. All of those fields, df1 thru p4, without my
knowing what data they represent, could be one field like AllRunners.TypeID.
Then another table tblType, with TypeID and runnertype fields, where
runnertype holds df1 thru p4, would make your sql much simpler and make the
addition of a new type simpler also.

HTH
Damon
 
G

Graham Feeley

Yes Damon you are correct as far as table design goes.
I will give it some thought today and will get back to you on this one and
thanx for your reply with a answer

Regards
Graham

Damon Heron said:
How about (snippet):
(([AllRunners].[" & Text15 & "])=[Results]![first]))ORDER BY.....

Having said that, I question the design of your table. It seems like a
flat file rather than relational. All of those fields, df1 thru p4,
without my knowing what data they represent, could be one field like
AllRunners.TypeID. Then another table tblType, with TypeID and runnertype
fields, where runnertype holds df1 thru p4, would make your sql much
simpler and make the addition of a new type simpler also.

HTH
Damon

Graham Feeley said:
I have 2 tables as follows
AllRunners
Results
I have a query that works fine from a command button

SELECT AllRunners.RCDATE, AllRunners.track, AllRunners.RCTIME,
AllRunners.raceno, AllRunners.dist, AllRunners.strs, AllRunners.fsts,
AllRunners.df1, AllRunners.df2, AllRunners.df3, AllRunners.T1,
AllRunners.T2, AllRunners.T3, AllRunners.B1, AllRunners.B2,
AllRunners.B3, AllRunners.R1, AllRunners.R2, AllRunners.R3,
AllRunners.D1, AllRunners.D2, AllRunners.D3, AllRunners.C1,
AllRunners.C2, AllRunners.C3, AllRunners.P1, AllRunners.p2,
AllRunners.p3, AllRunners.p4, Results.first
FROM AllRunners INNER JOIN Results ON (AllRunners.raceno =
Results.["raceno"]) AND (AllRunners.track = Results.["track"]) AND
(AllRunners.RCDATE = Results.MyDate)
WHERE (((AllRunners.RCDATE)=[Forms]![Form3]![Combo5])) AND
(([AllRunners]![P1])=[Results]![first]))
ORDER BY AllRunners.RCTIME;

On form 3 I have 1 combobox which selects a date
also have a text box names "Text15"
and a command button that runs a query

I would like to have this part of the query changable via the text box
"AND (([AllRunners]![P1])"


So really in essence i would like to change the sql to read instead of P1
to be any of these fields

df1
df2
df3
t1
t2
t3
b1
b2
b3
r1
r2
r3
d1
d2
d3
c1
c2
c3
p1
p2
p3
p4
and this would come from whatever I type into the Textbox15
Thanks in anticipation of a answer thats works
Graham
Click to expand...
Click to expand...
 
Top