Query Not Updating

R

Ryan

Im using SQL to design a query in Access 2000. I am having it query a large
set of data based on what information is selected from the combo boxes in a
form.

SELECT tbl_DRDATA.HDW, tbl_DRDATA.[DATE OPEN], tbl_DRDATA.[DATE CLOSED],
tbl_DRDATA.[DR NUMBER], tbl_DRDATA.CAUSE, tbl_DRDATA.DISPO, tbl_DRDATA.ORG,
tbl_DRDATA.NOTICED, tbl_DRDATA.LOC
FROM tbl_DRDATA


WHERE


(((tbl_DRDATA.EQUIP)=IIf([Forms]![CAT]![List73]="ALL",[tbl_DRDATA]![EQUIP],IIf([Forms]![CAT]![List73]=[tbl_DRDATA]![EQUIP],[tbl_DRDATA]![EQUIP])))

AND


((tbl_DRDATA.IPTverifcation)=IIf([Forms]![CAT]![Combo10]="ALL",[tbl_DRDATA]![IPTverifcation],IIf([tbl_DRDATA]![IPTverifcation]=[Forms]![CAT]![Combo10],[tbl_DRDATA]![IPTverifcation])))

AND

((tbl_DRDATA.HDW)=IIf([Forms]![CAT]![Combo9]="ALL",[tbl_DRDATA]![HDW],IIf([Forms]![CAT]![Combo9]=[tbl_DRDATA]![HDW],[tbl_DRDATA]![HDW])))

AND

((Month([DATE OPEN]))=IIf([Forms]![CAT]![Combo1]=Month([DATE
OPEN]),Month([DATE OPEN]),IIf([Forms]![CAT]![Combo1]="ALL",Month([DATE
OPEN]))))

AND

((Month([DATE Closed]))=IIf([Forms]![CAT]![Combo2]=Month([DATE
CLOSED]),Month([DATE CLOSED]),IIf([Forms]![CAT]![Combo2]="ALL",Month([DATE
CLOSED]))))

AND

((IIf(Month([tbl_DRDATA]![DATE OPEN])>=10,Year([tbl_DRDATA]![DATE
OPEN])+1,Year([tbl_DRDATA]![DATE
OPEN])))=IIf([Forms]![CAT]![Combo3]="ALL",IIf(Month([tbl_DRDATA]![DATE
OPEN])>=10,Year([tbl_DRDATA]![DATE OPEN])+1,Year([tbl_DRDATA]![DATE
OPEN])),IIf([Forms]![CAT]![Combo3]=IIf(Month([tbl_DRDATA]![DATE
OPEN])>=10,Year([tbl_DRDATA]![DATE OPEN])+1,Year([tbl_DRDATA]![DATE
OPEN])),IIf(Month([tbl_DRDATA]![DATE OPEN])>=10,Year([tbl_DRDATA]![DATE
OPEN])+1,Year([tbl_DRDATA]![DATE OPEN])))))

AND

((IIf(Month([tbl_DRDATA]![DATE CLOSED])>=10,Year([tbl_DRDATA]![DATE
CLOSED])+1,Year([tbl_DRDATA]![DATE
CLOSED])))=IIf([Forms]![CAT]![Combo8]="ALL",IIf(Month([tbl_DRDATA]![DATE
CLOSED])>=10,Year([tbl_DRDATA]![DATE CLOSED])+1,Year([tbl_DRDATA]![DATE
CLOSED])),IIf([Forms]![CAT]![Combo4]=IIf(Month([tbl_DRDATA]![DATE
CLOSED])>=10,Year([tbl_DRDATA]![DATE CLOSED])+1,Year([tbl_DRDATA]![DATE
CLOSED])),IIf(Month([tbl_DRDATA]![DATE CLOSED])>=10,Year([tbl_DRDATA]![DATE
CLOSED])+1,Year([tbl_DRDATA]![DATE CLOSED])))))

AND


((tbl_DRDATA.CAUSE)=IIf([Forms]![CAT]![Combo5]="-1",[tbl_DRDATA]![CAUSE],IIf([Forms]![CAT]![Combo5]=[tbl_DRDATA]![EQUIP],[tbl_DRDATA]![CAUSE])))

AND


((tbl_DRDATA.DISPO)=IIf([Forms]![CAT]![Combo6]="-1",[tbl_DRDATA]![DISPO],IIf([Forms]![CAT]![Combo6]=[tbl_DRDATA]![DISPO],[tbl_DRDATA]![DISPO])))

AND


((tbl_DRDATA.FAULT)=IIf([Forms]![CAT]![Combo8]="-1",[tbl_DRDATA]![FAULT],IIf([Forms]![CAT]![Combo8]=[tbl_DRDATA]![FAULT],[tbl_DRDATA]![FAULT])))

AND


((tbl_DRDATA.ORG)=IIf([Forms]![CAT]![Combo11]="-1",[tbl_DRDATA]![ORG],IIf([Forms]![CAT]![Combo11]=[tbl_DRDATA]![ORG],[tbl_DRDATA]![ORG])))

AND

((tbl_DRDATA.NOTICED)=IIf([Forms]![CAT]![Combo12]="-1",[tbl_DRDATA]![NOTICED],IIf([Forms]![CAT]![Combo12]=[tbl_DRDATA]![NOTICED],[tbl_DRDATA]![NOTICED])))

AND


((tbl_DRDATA.LOC)=IIf([Forms]![CAT]![Combo13]="-1",[tbl_DRDATA]![LOC],IIf([Forms]![CAT]![Combo13]=[tbl_DRDATA]![LOC],[tbl_DRDATA]![LOC]))));



As you can see its just several if statements. When I click the run button
it just displays the columns I wanted but doesnt even ask me to input values
for the combo boxes. This is why I think when I run the form, that it is not
updating the query and in turn not updating my graphs. If anyone can see an
issue with this please let me know as it will be greatly appreciated.
 
R

Ryan

correction, I made a quick change and when I click the run query button or
try to open it, it now prompts me to enter in values for the combo boxes. The
only probly now is that when I select the values from the combo boxes in my
form, it does not update the query.

Ryan said:
Im using SQL to design a query in Access 2000. I am having it query a large
set of data based on what information is selected from the combo boxes in a
form.

SELECT tbl_DRDATA.HDW, tbl_DRDATA.[DATE OPEN], tbl_DRDATA.[DATE CLOSED],
tbl_DRDATA.[DR NUMBER], tbl_DRDATA.CAUSE, tbl_DRDATA.DISPO, tbl_DRDATA.ORG,
tbl_DRDATA.NOTICED, tbl_DRDATA.LOC
FROM tbl_DRDATA


WHERE


(((tbl_DRDATA.EQUIP)=IIf([Forms]![CAT]![List73]="ALL",[tbl_DRDATA]![EQUIP],IIf([Forms]![CAT]![List73]=[tbl_DRDATA]![EQUIP],[tbl_DRDATA]![EQUIP])))

AND


((tbl_DRDATA.IPTverifcation)=IIf([Forms]![CAT]![Combo10]="ALL",[tbl_DRDATA]![IPTverifcation],IIf([tbl_DRDATA]![IPTverifcation]=[Forms]![CAT]![Combo10],[tbl_DRDATA]![IPTverifcation])))

AND

((tbl_DRDATA.HDW)=IIf([Forms]![CAT]![Combo9]="ALL",[tbl_DRDATA]![HDW],IIf([Forms]![CAT]![Combo9]=[tbl_DRDATA]![HDW],[tbl_DRDATA]![HDW])))

AND

((Month([DATE OPEN]))=IIf([Forms]![CAT]![Combo1]=Month([DATE
OPEN]),Month([DATE OPEN]),IIf([Forms]![CAT]![Combo1]="ALL",Month([DATE
OPEN]))))

AND

((Month([DATE Closed]))=IIf([Forms]![CAT]![Combo2]=Month([DATE
CLOSED]),Month([DATE CLOSED]),IIf([Forms]![CAT]![Combo2]="ALL",Month([DATE
CLOSED]))))

AND

((IIf(Month([tbl_DRDATA]![DATE OPEN])>=10,Year([tbl_DRDATA]![DATE
OPEN])+1,Year([tbl_DRDATA]![DATE
OPEN])))=IIf([Forms]![CAT]![Combo3]="ALL",IIf(Month([tbl_DRDATA]![DATE
OPEN])>=10,Year([tbl_DRDATA]![DATE OPEN])+1,Year([tbl_DRDATA]![DATE
OPEN])),IIf([Forms]![CAT]![Combo3]=IIf(Month([tbl_DRDATA]![DATE
OPEN])>=10,Year([tbl_DRDATA]![DATE OPEN])+1,Year([tbl_DRDATA]![DATE
OPEN])),IIf(Month([tbl_DRDATA]![DATE OPEN])>=10,Year([tbl_DRDATA]![DATE
OPEN])+1,Year([tbl_DRDATA]![DATE OPEN])))))

AND

((IIf(Month([tbl_DRDATA]![DATE CLOSED])>=10,Year([tbl_DRDATA]![DATE
CLOSED])+1,Year([tbl_DRDATA]![DATE
CLOSED])))=IIf([Forms]![CAT]![Combo8]="ALL",IIf(Month([tbl_DRDATA]![DATE
CLOSED])>=10,Year([tbl_DRDATA]![DATE CLOSED])+1,Year([tbl_DRDATA]![DATE
CLOSED])),IIf([Forms]![CAT]![Combo4]=IIf(Month([tbl_DRDATA]![DATE
CLOSED])>=10,Year([tbl_DRDATA]![DATE CLOSED])+1,Year([tbl_DRDATA]![DATE
CLOSED])),IIf(Month([tbl_DRDATA]![DATE CLOSED])>=10,Year([tbl_DRDATA]![DATE
CLOSED])+1,Year([tbl_DRDATA]![DATE CLOSED])))))

AND


((tbl_DRDATA.CAUSE)=IIf([Forms]![CAT]![Combo5]="-1",[tbl_DRDATA]![CAUSE],IIf([Forms]![CAT]![Combo5]=[tbl_DRDATA]![EQUIP],[tbl_DRDATA]![CAUSE])))

AND


((tbl_DRDATA.DISPO)=IIf([Forms]![CAT]![Combo6]="-1",[tbl_DRDATA]![DISPO],IIf([Forms]![CAT]![Combo6]=[tbl_DRDATA]![DISPO],[tbl_DRDATA]![DISPO])))

AND


((tbl_DRDATA.FAULT)=IIf([Forms]![CAT]![Combo8]="-1",[tbl_DRDATA]![FAULT],IIf([Forms]![CAT]![Combo8]=[tbl_DRDATA]![FAULT],[tbl_DRDATA]![FAULT])))

AND


((tbl_DRDATA.ORG)=IIf([Forms]![CAT]![Combo11]="-1",[tbl_DRDATA]![ORG],IIf([Forms]![CAT]![Combo11]=[tbl_DRDATA]![ORG],[tbl_DRDATA]![ORG])))

AND

((tbl_DRDATA.NOTICED)=IIf([Forms]![CAT]![Combo12]="-1",[tbl_DRDATA]![NOTICED],IIf([Forms]![CAT]![Combo12]=[tbl_DRDATA]![NOTICED],[tbl_DRDATA]![NOTICED])))

AND


((tbl_DRDATA.LOC)=IIf([Forms]![CAT]![Combo13]="-1",[tbl_DRDATA]![LOC],IIf([Forms]![CAT]![Combo13]=[tbl_DRDATA]![LOC],[tbl_DRDATA]![LOC]))));



As you can see its just several if statements. When I click the run button
it just displays the columns I wanted but doesnt even ask me to input values
for the combo boxes. This is why I think when I run the form, that it is not
updating the query and in turn not updating my graphs. If anyone can see an
issue with this please let me know as it will be greatly appreciated.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query running sum - help again 2
Help with date formula in query 1
Access shuts down, when i try to save this query 12
Query Too Complex with Total line 8
Crosstab Query Help 1
Multiple supervisors over a date range in query 1
Help -- Is my there any way to not count duplicate values here? 5
Reverse Axis in Crosstab Query 3

Top