conditional criteria

D

DawnTreader

hello

i have a problem.

i have a query where i want to have to possible criteria for one field and i
need the query to choose what criteria it should use.

here is the sql as it currently is:

SELECT tblSiteInformation.SiteID, tblProductList.ProductID,
Nz([CompanyName]," ") AS Company, tblSiteInformation.SiteCommonName,
subtblCountry.Country, subtblCity.City, tblProductList.SerialNumber,
tblProductList.ProductTypeID, qryProductWarrantyClaimCount.[# Claims],
qryProductIssueCount.Issues, qryProductLastServiceDate.LastServiceReport,
tblSiteInformation.CountryID, tblProductList.DateDeleted,
tblServiceReps.ServiceRepID, tblServiceReps.Name,
tblProductList.CustomerOrder, tblProductList.WorkOrder
FROM tblServiceReps RIGHT JOIN (tblCustomerList RIGHT JOIN (subtblCountry
RIGHT JOIN (subtblCity RIGHT JOIN (tblSiteInformation RIGHT JOIN
(qryProductIssueCount RIGHT JOIN (qryProductLastServiceDate RIGHT JOIN
(qryProductWarrantyClaimCount RIGHT JOIN tblProductList ON
qryProductWarrantyClaimCount.ProductID = tblProductList.ProductID) ON
qryProductLastServiceDate.ProductID = tblProductList.ProductID) ON
qryProductIssueCount.ProductID = tblProductList.ProductID) ON
tblSiteInformation.SiteID = tblProductList.SiteID) ON subtblCity.CityID =
tblSiteInformation.CityID) ON subtblCountry.CountryID =
tblSiteInformation.CountryID) ON tblCustomerList.IMWCustomerID =
tblSiteInformation.CustomerID) ON tblServiceReps.ServiceRepID =
tblSiteInformation.ServiceRepID
WHERE (((tblProductList.ProductID) Like [forms]![zzMAINFORM]![txtProduct])
AND ((Nz([CompanyName]," ")) Like "*" &
[forms]![zzMAINFORM]![txtCustomerHidden] & "*") AND
((tblSiteInformation.SiteCommonName) Like "*" &
[Forms]![zzMAINFORM]![txtSiteHidden] & "*") AND
((tblProductList.SerialNumber) Like "*" & [forms]![zzMAINFORM]![txtSerial] &
"*") AND ((tblProductList.ProductTypeID) Like
[Forms]![zzMAINFORM]![txtFilterProductType] & "*") AND
((tblSiteInformation.CountryID) Like [forms]![zzMAINFORM]![txtCountry]) AND
((tblProductList.DateDeleted) Is Null) AND ((tblServiceReps.ServiceRepID)
Like [Forms]![zzMAINFORM]![txtFilterRep]) AND ((tblProductList.CustomerOrder)
Like [Forms]![zzMAINFORM]![txtFilterOrder] Or (tblProductList.CustomerOrder)
Is Null) AND ((tblProductList.WorkOrder) Like
[Forms]![zzMAINFORM]![txtFilterWO] Or (tblProductList.WorkOrder) Is Null))
ORDER BY Nz([CompanyName]," "), tblSiteInformation.SiteCommonName,
subtblCountry.Country, tblProductList.SerialNumber;

here is what i would like:

SELECT tblSiteInformation.SiteID, tblProductList.ProductID,
Nz([CompanyName]," ") AS Company, tblSiteInformation.SiteCommonName,
subtblCountry.Country, subtblCity.City, tblProductList.SerialNumber,
tblProductList.ProductTypeID, qryProductWarrantyClaimCount.[# Claims],
qryProductIssueCount.Issues, qryProductLastServiceDate.LastServiceReport,
tblSiteInformation.CountryID, tblProductList.DateDeleted,
tblServiceReps.ServiceRepID, tblServiceReps.Name,
tblProductList.CustomerOrder, tblProductList.WorkOrder
FROM tblServiceReps RIGHT JOIN (tblCustomerList RIGHT JOIN (subtblCountry
RIGHT JOIN (subtblCity RIGHT JOIN (tblSiteInformation RIGHT JOIN
(qryProductIssueCount RIGHT JOIN (qryProductLastServiceDate RIGHT JOIN
(qryProductWarrantyClaimCount RIGHT JOIN tblProductList ON
qryProductWarrantyClaimCount.ProductID = tblProductList.ProductID) ON
qryProductLastServiceDate.ProductID = tblProductList.ProductID) ON
qryProductIssueCount.ProductID = tblProductList.ProductID) ON
tblSiteInformation.SiteID = tblProductList.SiteID) ON subtblCity.CityID =
tblSiteInformation.CityID) ON subtblCountry.CountryID =
tblSiteInformation.CountryID) ON tblCustomerList.IMWCustomerID =
tblSiteInformation.CustomerID) ON tblServiceReps.ServiceRepID =
tblSiteInformation.ServiceRepID
WHERE (((tblProductList.ProductID) Like [forms]![zzMAINFORM]![txtProduct])
AND ((Nz([CompanyName]," ")) Like "*" &
[forms]![zzMAINFORM]![txtCustomerHidden] & "*") AND
((tblSiteInformation.SiteCommonName) Like "*" &
[Forms]![zzMAINFORM]![txtSiteHidden] & "*") AND
((tblProductList.SerialNumber) Like "*" & [forms]![zzMAINFORM]![txtSerial] &
"*") AND ((tblProductList.ProductTypeID) Like
[Forms]![zzMAINFORM]![txtFilterProductType] & "*") AND
((tblSiteInformation.CountryID) Like [forms]![zzMAINFORM]![txtCountry]) AND
((tblProductList.DateDeleted) Is Null) AND ((tblServiceReps.ServiceRepID)
Like [Forms]![zzMAINFORM]![txtFilterRep]) AND ((tblProductList.CustomerOrder)
Like [Forms]![zzMAINFORM]![txtFilterOrder] Or (tblProductList.CustomerOrder)
Is Null) AND
((tblProductList.WorkOrder)=IIf([Forms]![zzMAINFORM]![txtFilterWO]="*",(tblProductList.WorkOrder)
Like [Forms]![zzMAINFORM]![txtFilterWO] Or (tblProductList.WorkOrder) Is
Null,(tblProductList.WorkOrder) Like [Forms]![zzMAINFORM]![txtFilterWO])))
ORDER BY Nz([CompanyName]," "), tblSiteInformation.SiteCommonName,
subtblCountry.Country, tblProductList.SerialNumber;

the problem is that access returns a too complex error.

is there any other way i can do what i am attempting?
 
K

KARL DEWEY

I normally expect to see wildcards when LIKE is used. You have 5 place where
you used LIKE with no wildcards.
This makes no sense to me ---
AND ((tblProductList.WorkOrder) =IIf([Forms]![zzMAINFORM]![txtFilterWO]
="*",(tblProductList.WorkOrder) Like [Forms]![zzMAINFORM]![txtFilterWO]
- It is looking for an asterisk in [txtFilterWO] of the form. Why enter an
asterisk?
- Then it has WorkOrder) Like [txtFilterWO] ?

And again ---
Or (tblProductList.WorkOrder) Is Null, (tblProductList.WorkOrder) Like
[Forms]![zzMAINFORM]![txtFilterWO])))

--
KARL DEWEY
Build a little - Test a little


DawnTreader said:
hello

i have a problem.

i have a query where i want to have to possible criteria for one field and i
need the query to choose what criteria it should use.

here is the sql as it currently is:

SELECT tblSiteInformation.SiteID, tblProductList.ProductID,
Nz([CompanyName]," ") AS Company, tblSiteInformation.SiteCommonName,
subtblCountry.Country, subtblCity.City, tblProductList.SerialNumber,
tblProductList.ProductTypeID, qryProductWarrantyClaimCount.[# Claims],
qryProductIssueCount.Issues, qryProductLastServiceDate.LastServiceReport,
tblSiteInformation.CountryID, tblProductList.DateDeleted,
tblServiceReps.ServiceRepID, tblServiceReps.Name,
tblProductList.CustomerOrder, tblProductList.WorkOrder
FROM tblServiceReps RIGHT JOIN (tblCustomerList RIGHT JOIN (subtblCountry
RIGHT JOIN (subtblCity RIGHT JOIN (tblSiteInformation RIGHT JOIN
(qryProductIssueCount RIGHT JOIN (qryProductLastServiceDate RIGHT JOIN
(qryProductWarrantyClaimCount RIGHT JOIN tblProductList ON
qryProductWarrantyClaimCount.ProductID = tblProductList.ProductID) ON
qryProductLastServiceDate.ProductID = tblProductList.ProductID) ON
qryProductIssueCount.ProductID = tblProductList.ProductID) ON
tblSiteInformation.SiteID = tblProductList.SiteID) ON subtblCity.CityID =
tblSiteInformation.CityID) ON subtblCountry.CountryID =
tblSiteInformation.CountryID) ON tblCustomerList.IMWCustomerID =
tblSiteInformation.CustomerID) ON tblServiceReps.ServiceRepID =
tblSiteInformation.ServiceRepID
WHERE (((tblProductList.ProductID) Like [forms]![zzMAINFORM]![txtProduct])
AND ((Nz([CompanyName]," ")) Like "*" &
[forms]![zzMAINFORM]![txtCustomerHidden] & "*") AND
((tblSiteInformation.SiteCommonName) Like "*" &
[Forms]![zzMAINFORM]![txtSiteHidden] & "*") AND
((tblProductList.SerialNumber) Like "*" & [forms]![zzMAINFORM]![txtSerial] &
"*") AND ((tblProductList.ProductTypeID) Like
[Forms]![zzMAINFORM]![txtFilterProductType] & "*") AND
((tblSiteInformation.CountryID) Like [forms]![zzMAINFORM]![txtCountry]) AND
((tblProductList.DateDeleted) Is Null) AND ((tblServiceReps.ServiceRepID)
Like [Forms]![zzMAINFORM]![txtFilterRep]) AND ((tblProductList.CustomerOrder)
Like [Forms]![zzMAINFORM]![txtFilterOrder] Or (tblProductList.CustomerOrder)
Is Null) AND ((tblProductList.WorkOrder) Like
[Forms]![zzMAINFORM]![txtFilterWO] Or (tblProductList.WorkOrder) Is Null))
ORDER BY Nz([CompanyName]," "), tblSiteInformation.SiteCommonName,
subtblCountry.Country, tblProductList.SerialNumber;

here is what i would like:

SELECT tblSiteInformation.SiteID, tblProductList.ProductID,
Nz([CompanyName]," ") AS Company, tblSiteInformation.SiteCommonName,
subtblCountry.Country, subtblCity.City, tblProductList.SerialNumber,
tblProductList.ProductTypeID, qryProductWarrantyClaimCount.[# Claims],
qryProductIssueCount.Issues, qryProductLastServiceDate.LastServiceReport,
tblSiteInformation.CountryID, tblProductList.DateDeleted,
tblServiceReps.ServiceRepID, tblServiceReps.Name,
tblProductList.CustomerOrder, tblProductList.WorkOrder
FROM tblServiceReps RIGHT JOIN (tblCustomerList RIGHT JOIN (subtblCountry
RIGHT JOIN (subtblCity RIGHT JOIN (tblSiteInformation RIGHT JOIN
(qryProductIssueCount RIGHT JOIN (qryProductLastServiceDate RIGHT JOIN
(qryProductWarrantyClaimCount RIGHT JOIN tblProductList ON
qryProductWarrantyClaimCount.ProductID = tblProductList.ProductID) ON
qryProductLastServiceDate.ProductID = tblProductList.ProductID) ON
qryProductIssueCount.ProductID = tblProductList.ProductID) ON
tblSiteInformation.SiteID = tblProductList.SiteID) ON subtblCity.CityID =
tblSiteInformation.CityID) ON subtblCountry.CountryID =
tblSiteInformation.CountryID) ON tblCustomerList.IMWCustomerID =
tblSiteInformation.CustomerID) ON tblServiceReps.ServiceRepID =
tblSiteInformation.ServiceRepID
WHERE (((tblProductList.ProductID) Like [forms]![zzMAINFORM]![txtProduct])
AND ((Nz([CompanyName]," ")) Like "*" &
[forms]![zzMAINFORM]![txtCustomerHidden] & "*") AND
((tblSiteInformation.SiteCommonName) Like "*" &
[Forms]![zzMAINFORM]![txtSiteHidden] & "*") AND
((tblProductList.SerialNumber) Like "*" & [forms]![zzMAINFORM]![txtSerial] &
"*") AND ((tblProductList.ProductTypeID) Like
[Forms]![zzMAINFORM]![txtFilterProductType] & "*") AND
((tblSiteInformation.CountryID) Like [forms]![zzMAINFORM]![txtCountry]) AND
((tblProductList.DateDeleted) Is Null) AND ((tblServiceReps.ServiceRepID)
Like [Forms]![zzMAINFORM]![txtFilterRep]) AND ((tblProductList.CustomerOrder)
Like [Forms]![zzMAINFORM]![txtFilterOrder] Or (tblProductList.CustomerOrder)
Is Null) AND
((tblProductList.WorkOrder)=IIf([Forms]![zzMAINFORM]![txtFilterWO]="*",(tblProductList.WorkOrder)
Like [Forms]![zzMAINFORM]![txtFilterWO] Or (tblProductList.WorkOrder) Is
Null,(tblProductList.WorkOrder) Like [Forms]![zzMAINFORM]![txtFilterWO])))
ORDER BY Nz([CompanyName]," "), tblSiteInformation.SiteCommonName,
subtblCountry.Country, tblProductList.SerialNumber;

the problem is that access returns a too complex error.

is there any other way i can do what i am attempting?
Click to expand...
 
D

DawnTreader

Hello Karl

i used the design view to create this sql.

here is the situation. i have a form where i use a list box to list all the
"products". this list box is based on a query that pulls from several tables.
using text boxes on the form and "hidden" text boxes i can effectively create
filters for the users to type in or choose criteria.

the fields customer order and work order give me a problem. i used a combo
box with a query to allow the user to only choose what is in the database.
this combo box transfers the criteria to a text box which the query making
the list box uses as criteria.

in typing this out i think it might be easier to get rid of the combo boxes
and make the customer and work order filters work the way i did with the site
and customer filter systems. this would allow the user to type anything in
there.

still leaves me with the question, can you create if then criteria...

thanks for pointing out the error of my ways... :)

KARL DEWEY said:
I normally expect to see wildcards when LIKE is used. You have 5 place where
you used LIKE with no wildcards.
This makes no sense to me ---
AND ((tblProductList.WorkOrder) =IIf([Forms]![zzMAINFORM]![txtFilterWO]
="*",(tblProductList.WorkOrder) Like [Forms]![zzMAINFORM]![txtFilterWO]
- It is looking for an asterisk in [txtFilterWO] of the form. Why enter an
asterisk?
- Then it has WorkOrder) Like [txtFilterWO] ?

And again ---
Or (tblProductList.WorkOrder) Is Null, (tblProductList.WorkOrder) Like
[Forms]![zzMAINFORM]![txtFilterWO])))

--
KARL DEWEY
Build a little - Test a little


DawnTreader said:
hello

i have a problem.

i have a query where i want to have to possible criteria for one field and i
need the query to choose what criteria it should use.

here is the sql as it currently is:

SELECT tblSiteInformation.SiteID, tblProductList.ProductID,
Nz([CompanyName]," ") AS Company, tblSiteInformation.SiteCommonName,
subtblCountry.Country, subtblCity.City, tblProductList.SerialNumber,
tblProductList.ProductTypeID, qryProductWarrantyClaimCount.[# Claims],
qryProductIssueCount.Issues, qryProductLastServiceDate.LastServiceReport,
tblSiteInformation.CountryID, tblProductList.DateDeleted,
tblServiceReps.ServiceRepID, tblServiceReps.Name,
tblProductList.CustomerOrder, tblProductList.WorkOrder
FROM tblServiceReps RIGHT JOIN (tblCustomerList RIGHT JOIN (subtblCountry
RIGHT JOIN (subtblCity RIGHT JOIN (tblSiteInformation RIGHT JOIN
(qryProductIssueCount RIGHT JOIN (qryProductLastServiceDate RIGHT JOIN
(qryProductWarrantyClaimCount RIGHT JOIN tblProductList ON
qryProductWarrantyClaimCount.ProductID = tblProductList.ProductID) ON
qryProductLastServiceDate.ProductID = tblProductList.ProductID) ON
qryProductIssueCount.ProductID = tblProductList.ProductID) ON
tblSiteInformation.SiteID = tblProductList.SiteID) ON subtblCity.CityID =
tblSiteInformation.CityID) ON subtblCountry.CountryID =
tblSiteInformation.CountryID) ON tblCustomerList.IMWCustomerID =
tblSiteInformation.CustomerID) ON tblServiceReps.ServiceRepID =
tblSiteInformation.ServiceRepID
WHERE (((tblProductList.ProductID) Like [forms]![zzMAINFORM]![txtProduct])
AND ((Nz([CompanyName]," ")) Like "*" &
[forms]![zzMAINFORM]![txtCustomerHidden] & "*") AND
((tblSiteInformation.SiteCommonName) Like "*" &
[Forms]![zzMAINFORM]![txtSiteHidden] & "*") AND
((tblProductList.SerialNumber) Like "*" & [forms]![zzMAINFORM]![txtSerial] &
"*") AND ((tblProductList.ProductTypeID) Like
[Forms]![zzMAINFORM]![txtFilterProductType] & "*") AND
((tblSiteInformation.CountryID) Like [forms]![zzMAINFORM]![txtCountry]) AND
((tblProductList.DateDeleted) Is Null) AND ((tblServiceReps.ServiceRepID)
Like [Forms]![zzMAINFORM]![txtFilterRep]) AND ((tblProductList.CustomerOrder)
Like [Forms]![zzMAINFORM]![txtFilterOrder] Or (tblProductList.CustomerOrder)
Is Null) AND ((tblProductList.WorkOrder) Like
[Forms]![zzMAINFORM]![txtFilterWO] Or (tblProductList.WorkOrder) Is Null))
ORDER BY Nz([CompanyName]," "), tblSiteInformation.SiteCommonName,
subtblCountry.Country, tblProductList.SerialNumber;

here is what i would like:

SELECT tblSiteInformation.SiteID, tblProductList.ProductID,
Nz([CompanyName]," ") AS Company, tblSiteInformation.SiteCommonName,
subtblCountry.Country, subtblCity.City, tblProductList.SerialNumber,
tblProductList.ProductTypeID, qryProductWarrantyClaimCount.[# Claims],
qryProductIssueCount.Issues, qryProductLastServiceDate.LastServiceReport,
tblSiteInformation.CountryID, tblProductList.DateDeleted,
tblServiceReps.ServiceRepID, tblServiceReps.Name,
tblProductList.CustomerOrder, tblProductList.WorkOrder
FROM tblServiceReps RIGHT JOIN (tblCustomerList RIGHT JOIN (subtblCountry
RIGHT JOIN (subtblCity RIGHT JOIN (tblSiteInformation RIGHT JOIN
(qryProductIssueCount RIGHT JOIN (qryProductLastServiceDate RIGHT JOIN
(qryProductWarrantyClaimCount RIGHT JOIN tblProductList ON
qryProductWarrantyClaimCount.ProductID = tblProductList.ProductID) ON
qryProductLastServiceDate.ProductID = tblProductList.ProductID) ON
qryProductIssueCount.ProductID = tblProductList.ProductID) ON
tblSiteInformation.SiteID = tblProductList.SiteID) ON subtblCity.CityID =
tblSiteInformation.CityID) ON subtblCountry.CountryID =
tblSiteInformation.CountryID) ON tblCustomerList.IMWCustomerID =
tblSiteInformation.CustomerID) ON tblServiceReps.ServiceRepID =
tblSiteInformation.ServiceRepID
WHERE (((tblProductList.ProductID) Like [forms]![zzMAINFORM]![txtProduct])
AND ((Nz([CompanyName]," ")) Like "*" &
[forms]![zzMAINFORM]![txtCustomerHidden] & "*") AND
((tblSiteInformation.SiteCommonName) Like "*" &
[Forms]![zzMAINFORM]![txtSiteHidden] & "*") AND
((tblProductList.SerialNumber) Like "*" & [forms]![zzMAINFORM]![txtSerial] &
"*") AND ((tblProductList.ProductTypeID) Like
[Forms]![zzMAINFORM]![txtFilterProductType] & "*") AND
((tblSiteInformation.CountryID) Like [forms]![zzMAINFORM]![txtCountry]) AND
((tblProductList.DateDeleted) Is Null) AND ((tblServiceReps.ServiceRepID)
Like [Forms]![zzMAINFORM]![txtFilterRep]) AND ((tblProductList.CustomerOrder)
Like [Forms]![zzMAINFORM]![txtFilterOrder] Or (tblProductList.CustomerOrder)
Is Null) AND
((tblProductList.WorkOrder)=IIf([Forms]![zzMAINFORM]![txtFilterWO]="*",(tblProductList.WorkOrder)
Like [Forms]![zzMAINFORM]![txtFilterWO] Or (tblProductList.WorkOrder) Is
Null,(tblProductList.WorkOrder) Like [Forms]![zzMAINFORM]![txtFilterWO])))
ORDER BY Nz([CompanyName]," "), tblSiteInformation.SiteCommonName,
subtblCountry.Country, tblProductList.SerialNumber;

the problem is that access returns a too complex error.

is there any other way i can do what i am attempting?
Click to expand...
Click to expand...
 
K

KARL DEWEY

still leaves me with the question, can you create if then criteria...
You can use an IIF statement in criteria.
--
KARL DEWEY
Build a little - Test a little


DawnTreader said:
Hello Karl

i used the design view to create this sql.

here is the situation. i have a form where i use a list box to list all the
"products". this list box is based on a query that pulls from several tables.
using text boxes on the form and "hidden" text boxes i can effectively create
filters for the users to type in or choose criteria.

the fields customer order and work order give me a problem. i used a combo
box with a query to allow the user to only choose what is in the database.
this combo box transfers the criteria to a text box which the query making
the list box uses as criteria.

in typing this out i think it might be easier to get rid of the combo boxes
and make the customer and work order filters work the way i did with the site
and customer filter systems. this would allow the user to type anything in
there.

still leaves me with the question, can you create if then criteria...

thanks for pointing out the error of my ways... :)

KARL DEWEY said:
I normally expect to see wildcards when LIKE is used. You have 5 place where
you used LIKE with no wildcards.
This makes no sense to me ---
AND ((tblProductList.WorkOrder) =IIf([Forms]![zzMAINFORM]![txtFilterWO]
="*",(tblProductList.WorkOrder) Like [Forms]![zzMAINFORM]![txtFilterWO]
- It is looking for an asterisk in [txtFilterWO] of the form. Why enter an
asterisk?
- Then it has WorkOrder) Like [txtFilterWO] ?

And again ---
Or (tblProductList.WorkOrder) Is Null, (tblProductList.WorkOrder) Like
[Forms]![zzMAINFORM]![txtFilterWO])))

--
KARL DEWEY
Build a little - Test a little


DawnTreader said:
hello

i have a problem.

i have a query where i want to have to possible criteria for one field and i
need the query to choose what criteria it should use.

here is the sql as it currently is:

SELECT tblSiteInformation.SiteID, tblProductList.ProductID,
Nz([CompanyName]," ") AS Company, tblSiteInformation.SiteCommonName,
subtblCountry.Country, subtblCity.City, tblProductList.SerialNumber,
tblProductList.ProductTypeID, qryProductWarrantyClaimCount.[# Claims],
qryProductIssueCount.Issues, qryProductLastServiceDate.LastServiceReport,
tblSiteInformation.CountryID, tblProductList.DateDeleted,
tblServiceReps.ServiceRepID, tblServiceReps.Name,
tblProductList.CustomerOrder, tblProductList.WorkOrder
FROM tblServiceReps RIGHT JOIN (tblCustomerList RIGHT JOIN (subtblCountry
RIGHT JOIN (subtblCity RIGHT JOIN (tblSiteInformation RIGHT JOIN
(qryProductIssueCount RIGHT JOIN (qryProductLastServiceDate RIGHT JOIN
(qryProductWarrantyClaimCount RIGHT JOIN tblProductList ON
qryProductWarrantyClaimCount.ProductID = tblProductList.ProductID) ON
qryProductLastServiceDate.ProductID = tblProductList.ProductID) ON
qryProductIssueCount.ProductID = tblProductList.ProductID) ON
tblSiteInformation.SiteID = tblProductList.SiteID) ON subtblCity.CityID =
tblSiteInformation.CityID) ON subtblCountry.CountryID =
tblSiteInformation.CountryID) ON tblCustomerList.IMWCustomerID =
tblSiteInformation.CustomerID) ON tblServiceReps.ServiceRepID =
tblSiteInformation.ServiceRepID
WHERE (((tblProductList.ProductID) Like [forms]![zzMAINFORM]![txtProduct])
AND ((Nz([CompanyName]," ")) Like "*" &
[forms]![zzMAINFORM]![txtCustomerHidden] & "*") AND
((tblSiteInformation.SiteCommonName) Like "*" &
[Forms]![zzMAINFORM]![txtSiteHidden] & "*") AND
((tblProductList.SerialNumber) Like "*" & [forms]![zzMAINFORM]![txtSerial] &
"*") AND ((tblProductList.ProductTypeID) Like
[Forms]![zzMAINFORM]![txtFilterProductType] & "*") AND
((tblSiteInformation.CountryID) Like [forms]![zzMAINFORM]![txtCountry]) AND
((tblProductList.DateDeleted) Is Null) AND ((tblServiceReps.ServiceRepID)
Like [Forms]![zzMAINFORM]![txtFilterRep]) AND ((tblProductList.CustomerOrder)
Like [Forms]![zzMAINFORM]![txtFilterOrder] Or (tblProductList.CustomerOrder)
Is Null) AND ((tblProductList.WorkOrder) Like
[Forms]![zzMAINFORM]![txtFilterWO] Or (tblProductList.WorkOrder) Is Null))
ORDER BY Nz([CompanyName]," "), tblSiteInformation.SiteCommonName,
subtblCountry.Country, tblProductList.SerialNumber;

here is what i would like:

SELECT tblSiteInformation.SiteID, tblProductList.ProductID,
Nz([CompanyName]," ") AS Company, tblSiteInformation.SiteCommonName,
subtblCountry.Country, subtblCity.City, tblProductList.SerialNumber,
tblProductList.ProductTypeID, qryProductWarrantyClaimCount.[# Claims],
qryProductIssueCount.Issues, qryProductLastServiceDate.LastServiceReport,
tblSiteInformation.CountryID, tblProductList.DateDeleted,
tblServiceReps.ServiceRepID, tblServiceReps.Name,
tblProductList.CustomerOrder, tblProductList.WorkOrder
FROM tblServiceReps RIGHT JOIN (tblCustomerList RIGHT JOIN (subtblCountry
RIGHT JOIN (subtblCity RIGHT JOIN (tblSiteInformation RIGHT JOIN
(qryProductIssueCount RIGHT JOIN (qryProductLastServiceDate RIGHT JOIN
(qryProductWarrantyClaimCount RIGHT JOIN tblProductList ON
qryProductWarrantyClaimCount.ProductID = tblProductList.ProductID) ON
qryProductLastServiceDate.ProductID = tblProductList.ProductID) ON
qryProductIssueCount.ProductID = tblProductList.ProductID) ON
tblSiteInformation.SiteID = tblProductList.SiteID) ON subtblCity.CityID =
tblSiteInformation.CityID) ON subtblCountry.CountryID =
tblSiteInformation.CountryID) ON tblCustomerList.IMWCustomerID =
tblSiteInformation.CustomerID) ON tblServiceReps.ServiceRepID =
tblSiteInformation.ServiceRepID
WHERE (((tblProductList.ProductID) Like [forms]![zzMAINFORM]![txtProduct])
AND ((Nz([CompanyName]," ")) Like "*" &
[forms]![zzMAINFORM]![txtCustomerHidden] & "*") AND
((tblSiteInformation.SiteCommonName) Like "*" &
[Forms]![zzMAINFORM]![txtSiteHidden] & "*") AND
((tblProductList.SerialNumber) Like "*" & [forms]![zzMAINFORM]![txtSerial] &
"*") AND ((tblProductList.ProductTypeID) Like
[Forms]![zzMAINFORM]![txtFilterProductType] & "*") AND
((tblSiteInformation.CountryID) Like [forms]![zzMAINFORM]![txtCountry]) AND
((tblProductList.DateDeleted) Is Null) AND ((tblServiceReps.ServiceRepID)
Like [Forms]![zzMAINFORM]![txtFilterRep]) AND ((tblProductList.CustomerOrder)
Like [Forms]![zzMAINFORM]![txtFilterOrder] Or (tblProductList.CustomerOrder)
Is Null) AND
((tblProductList.WorkOrder)=IIf([Forms]![zzMAINFORM]![txtFilterWO]="*",(tblProductList.WorkOrder)
Like [Forms]![zzMAINFORM]![txtFilterWO] Or (tblProductList.WorkOrder) Is
Null,(tblProductList.WorkOrder) Like [Forms]![zzMAINFORM]![txtFilterWO])))
ORDER BY Nz([CompanyName]," "), tblSiteInformation.SiteCommonName,
subtblCountry.Country, tblProductList.SerialNumber;

the problem is that access returns a too complex error.

is there any other way i can do what i am attempting?
Click to expand...
Click to expand...
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

finding a range based on a value, from 2 separate queries 2
How to fix the one exception to the rule. 0
Concatenating the same field from multiple records 16
Question about Queries with Subqueries 1
How to use WHERE NOT EXISTS 2
help with figuring out how to make subqueries 0
parameter Query won't work 5
Multiple criteria 9

Top