Hello,
I created a search form that has text inputs for each of my fields. When the user clicks ok, I run a macro that opens a report using a query as a filter. This works fine, and the appropriate records are displayed in the report.
Unfortunately, I created four different queries with different criteria (ie, qry1 filters fields A-F, qry2 filters fields G-L, etc). Independently they work fine, but I would like to filter all fields (A-Z) at the same time. This hasn't worked for me.
I have one big table with the data as I was exporting from an excel file and all relationships are 1:1. Each query acts on different fields, but they contain all of the information from the table.
I've tried two things:
A) Simply combine the queries using copy and paste. Despite a close review of the code for parens out of place etc, this doesn't work. I've read the SQL over several times and don't see anything out of place, so I'm not sure what's up with this. Is there a way to make Access combine these so that my phantom problem disappears? I spent an entire day trying to figure out why copy-paste doesn't work
I'm in SQL view, and paste the parameters with the other parameters, etc. I add one at a time but every so often one will just break the code. Ideas?
B) Using a makeTable query, I tried making a table from two queries, which worked, but would not work with the other two. It appears that qry1 and qry2 actually reduce in size after I run them (ie go to 15 records from the 200 in the table) while qry3 & qry4 will filter correctly to the report but retain all the records (when I open the query, all 200 records are there). Is there a way to change these last two so that the query will only contain records that meet the criteria I specified in my form?
Thanks!
Here's a snippet of one of my queries. They're all built the same way. In some cases (A) I'm looking for exact matches, and in others (B) for ranges:
PARAMETERS [Forms]![Search]![txtfieldA] Short, [Forms]![Search]![txtfieldBmax] Short, [Forms]![Search]![txtfieldBmin] Short;
SELECT tblData.*
FROM tblData
WHERE ((Forms!Search!txtFieldA Is Null) OR (tblData.A=Forms!Search!txtFieldA)) AND ((Forms!Search!txtFieldBmax Is Null) OR (tblData.B) Between Forms!search!txtfieldBmax and Forms!Search!txtfieldBmin));
I created a search form that has text inputs for each of my fields. When the user clicks ok, I run a macro that opens a report using a query as a filter. This works fine, and the appropriate records are displayed in the report.
Unfortunately, I created four different queries with different criteria (ie, qry1 filters fields A-F, qry2 filters fields G-L, etc). Independently they work fine, but I would like to filter all fields (A-Z) at the same time. This hasn't worked for me.
I have one big table with the data as I was exporting from an excel file and all relationships are 1:1. Each query acts on different fields, but they contain all of the information from the table.
I've tried two things:
A) Simply combine the queries using copy and paste. Despite a close review of the code for parens out of place etc, this doesn't work. I've read the SQL over several times and don't see anything out of place, so I'm not sure what's up with this. Is there a way to make Access combine these so that my phantom problem disappears? I spent an entire day trying to figure out why copy-paste doesn't work
I'm in SQL view, and paste the parameters with the other parameters, etc. I add one at a time but every so often one will just break the code. Ideas?
B) Using a makeTable query, I tried making a table from two queries, which worked, but would not work with the other two. It appears that qry1 and qry2 actually reduce in size after I run them (ie go to 15 records from the 200 in the table) while qry3 & qry4 will filter correctly to the report but retain all the records (when I open the query, all 200 records are there). Is there a way to change these last two so that the query will only contain records that meet the criteria I specified in my form?
Thanks!
Here's a snippet of one of my queries. They're all built the same way. In some cases (A) I'm looking for exact matches, and in others (B) for ranges:
PARAMETERS [Forms]![Search]![txtfieldA] Short, [Forms]![Search]![txtfieldBmax] Short, [Forms]![Search]![txtfieldBmin] Short;
SELECT tblData.*
FROM tblData
WHERE ((Forms!Search!txtFieldA Is Null) OR (tblData.A=Forms!Search!txtFieldA)) AND ((Forms!Search!txtFieldBmax Is Null) OR (tblData.B) Between Forms!search!txtfieldBmax and Forms!Search!txtfieldBmin));
