Need Help WITH SQL Statement

[Guest]

I am building a web application that uses an access database. I am using multiple select boxes to allow the user to select multiple salesmen, customers, etc. and I am generating the SQL statement on the next page. In addition to this, the user is required to put a date range. Therefore, my SQL WHERE statement looks something like this

WHERE (saleDate > txtFromDate AND salesDate < txtToDate) AND (SalesmenID = selSalesmenID(0) OR SalesmenID = selSalesmenID(1)) AND (CustomerID = selCustomerID(0) OR selCustomerID(1))

I want the user to be able to select multiple customers and salesmen and them any combination of those selections to show up in the result set. The problem is that only the records with the selSalesmenID(0) and selCustomerID(0) are showing up.

How do I get all customer and salesmen combinations to show up?

Thank you,
Jason

 

[Graham R Seach]

Jason,

I'm afraid there's nothing for it - you'll have to build the SQL query
yourself in code.

By the way, instead of using the (x > y AND x < z) construct for the dates,
you could use the (x BETWEEN y AND z) construct.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

I am building a web application that uses an access database. I am using

multiple select boxes to allow the user to select multiple salesmen,
customers, etc. and I am generating the SQL statement on the next page. In
addition to this, the user is required to put a date range. Therefore, my
SQL WHERE statement looks something like this

WHERE (saleDate > txtFromDate AND salesDate < txtToDate) AND (SalesmenID =

selSalesmenID(0) OR SalesmenID = selSalesmenID(1)) AND (CustomerID =
selCustomerID(0) OR selCustomerID(1))

I want the user to be able to select multiple customers and salesmen and

them any combination of those selections to show up in the result set. The
problem is that only the records with the selSalesmenID(0) and
selCustomerID(0) are showing up.

 

[Douglas J. Steele]

\First of all, it's not clear exactly what you're showing below. If it's the
end-result of building the query, it definitely won't work, because Access
won't know what txtFromDate or txtToDate, for example, is when it's running
the query.

You have to put the values of the variables into the SQL string, not their
names.

You also need to ensure that values are enclosed in the appropriate
delimiters: text values need to be in quotes, and dates need to be in # (and
need to be in mm/dd/yyyy, regardless of what your Regional Settings are)

Assuming saleDate is a date field, and SalesmenID and CustomerID are
numeric, try:

strWhere = "WHERE (saleDate > #" & txtFromDate & "#" & _
" AND salesDate < #" & txtToDate) & "# " & _
" AND (SalesmenID = " & selSalesmenID(0) & _
" OR SalesmenID = selSalesmenID(1) & ") " & _
" AND (CustomerID = " & selCustomerID(0) & _
" OR CustomerID = " & selCustomerID(1) & ")"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am building a web application that uses an access database. I am using

multiple select boxes to allow the user to select multiple salesmen,
customers, etc. and I am generating the SQL statement on the next page. In
addition to this, the user is required to put a date range. Therefore, my
SQL WHERE statement looks something like this

WHERE (saleDate > txtFromDate AND salesDate < txtToDate) AND (SalesmenID =

selSalesmenID(0) OR SalesmenID = selSalesmenID(1)) AND (CustomerID =
selCustomerID(0) OR selCustomerID(1))

I want the user to be able to select multiple customers and salesmen and

them any combination of those selections to show up in the result set. The
problem is that only the records with the selSalesmenID(0) and
selCustomerID(0) are showing up.

 

[Guest]

Douglas,

Thank you for your response. The date was not the problem. I was pulling the values from the form on the previous page. The problem was that either in the database or in the value= section of the select control there was a space being added in front of the Replication ID. I used the trim function when I was entering the value in the SQL. This seemed to fix the problem. I also changed the query to utilize BETWEEN for the dates and IN for the IDs.

Thanks again,
Jason

 

[Graham R Seach]

Jason,

Firstly, I would format the SQL statements within your code to make them a
bit more readable. Like so:
strSQL = "SELECT Principal, " & _
"Customer, " & _
"Distributor, " & _
"Salesman, " & _
"InvoiceDate, " & _
"ExtendedPrice, " & _
"[Comm%], " & _
"[Comm $] " & _
"FROM CommissionResults " & _
"WHERE (InvoiceDate BETWEEN #" & request.Form("txtFromDate") &
_
"# AND #" & request.Form("txtToDate") &
"#)"

numSalesSQL = "SELECT SUM(ExtendedPrice) As TotalExtendedPrice " & _
"FROM CommissionResults " & _
"WHERE (InvoiceDate BETWEEN #" &
request.Form("txtFromDate") & _
"# AND #" & request.Form("txtToDate")
& "#)"

numComSQL = "SELECT SUM([Comm $]) As TotalComm " & _
"FROM CommissionResults " & _
"WHERE (InvoiceDate BETWEEN #" & request.Form("txtFromDate")
& _
"# AND #" & request.Form("txtToDate") &
"#)"

You'll notice I removed the table qualifier from every column name. If the
table is the same, there's no need to fully qualify the column names. It
just makes it easier to read.

Secondly, although you can get away without it, whenever you use aggregation
in SQL, you should give it an alias. For example, the following two
statements:
numSalesSQL = "SELECT SUM(ExtendedPrice) "
numComSQL = "SELECT SUM([Comm $]) "
....should be:
numSalesSQL = "SELECT SUM(ExtendedPrice) As TotalExtendedPrice "
numComSQL = "SELECT SUM([Comm $]) As TotalComm "

Thirdly, and I'm betting this is your problem, if SalesmanID is text, then
your assignment is OK, but if it's numeric, the assignment should be as
follows:
strSQL = strSQL & "SalesmanID = " & arrSalesmen(I)
numSalesSQL = numSalesSQL & "SalesmanID = " & arrSalesmen(I)
numComSQL = numComSQL & "SalesmanID = " & arrSalesmen(I)

....the same can be said about PrincipalID, CustomerID, and DistributorID.

I'm not familiar with ASP, so your code looks good to a blind man. The SQL
looks OK now, so if I were you, I'd check the contents of strSQL,
numSalesSQL and numComSQL just before you poke them into a recordset. Print
them out (maybe Debug.Print) and check them for problems.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

Graham,

First, thank you for your response, however I did not totally understand

your response. I am building the query in code. I am including it in my
reply. My problem is that I am only getting part of the results I desire.
WHERE ( conditionA AND conditionB) AND (conditionC OR conditionD OR
conditionE) AND (conditionF OR conditionG OR conditionH). What I am looking
for is the recordset that meet Conditions A and B and any of C, D, OR E that
meet condition F, G, or H. So, it would return A, B, C, F or A, B, D, F or
A, B, E, H, G etc. Basically any combination of A, B, and all records that
meet condition C that have condition F, G, or H as well as all records that
meet A, B and all records that meet condition D that have condition F,G, H.
and so on down the line.

I hope this clarifies my question and thank you very much for your help.

I know this may not be the cleanest code, but everything is working fine except for the sql.

set connlib = server.createobject("adodb.connection")
with connlib
.connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath("Commissions.mdb")
.open
end with

Dim strSQL
Dim numSalesSQL
Dim numComSQL
Dim arrSalesmen
Dim arrDistributors
Dim arrPrincipals
Dim arrCustomers
Dim curSales
Dim curCommissions




arrDistributors = split(request.Form("selDistributors"), ",")

strSQL = "SELECT CommissionResults.Principal, CommissionResults.Customer,

CommissionResults.Distributor, CommissionResults.Salesman,
CommissionResults.InvoiceDate, CommissionResults.ExtendedPrice,
CommissionResults.[Comm%], CommissionResults.[Comm $] FROM CommissionResults
WHERE (InvoiceDate > #" & request.Form("txtFromDate") & "# AND InvoiceDate <
#" & request.Form("txtToDate") & "#)"

numSalesSQL = "SELECT SUM(CommissionResults.ExtendedPrice) FROM

CommissionResults WHERE (InvoiceDate > #" & request.Form("txtFromDate") & "#
AND InvoiceDate < #" & request.Form("txtToDate") & "#)"

numComSQL = "SELECT SUM(CommissionResults.[Comm $]) FROM

CommissionResults WHERE (InvoiceDate > #" & request.Form("txtFromDate") & "#
AND InvoiceDate < #" & request.Form("txtToDate") & "#)"