Qry-Statements

G

Guest

I want my report to show if my product is (specked) or (non-specked)
In the query i am able to get only one of them to show up. I have tried use
OR, and AND. They still only show one. I have created two drop down's on the
form and have tried combinding them and that does not work.
Can anyone help.
 
J

Jeff Boyce

Lisa

We aren't there. We can't see what you are looking at. We have no idea
what data you are storing in your tables, nor how you are querying them.

More info, please!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
G

Guest

Lisa,

Ditto. Show us the SQL. Open the query in design view. Next go to View, SQL
View and copy and past it here. Information on primary keys and relationships
would be a nice touch too.
 
G

Guest

It is long sorry. What I want is I have 2combo boxes on a form that pertain
to Product options. There are 5 choices in each combo box. Both boxes come
from the same table field. I want be able to choose more then one choice ex.
a specked item from box 1and a Non-specked item from box 2. Is that possible
and if so how?



SELECT tblStores.StoreName, tblStores.StoreNo,
tblItemListLedo.LedoItemDescription, tblInvoiceCurrent.[USF Product Number],
tblInvoiceCurrent.[Product Description], tblInvoiceCurrent.Brand,
tblItemListVendor.[Product Options], tblInvoiceCurrent.[Ship Date],
Sum(tblInvoiceCurrent.[Cases Shipped]) AS [SumOfCases Shipped],
tblStores.IncludeInRpts
FROM tblStores INNER JOIN (tblItemListLedo INNER JOIN (tblStoresUSCustNos
INNER JOIN (tblInvoiceCurrent INNER JOIN tblItemListVendor ON
tblInvoiceCurrent.[USF Product Number] = tblItemListVendor.USFSItemNo) ON
tblStoresUSCustNos.USCustNo = tblInvoiceCurrent.[Customer Number]) ON
tblItemListLedo.LIN = tblItemListVendor.VListLedoItemDescription) ON
tblStores.StoreNo = tblStoresUSCustNos.StoreNo
GROUP BY tblStores.StoreName, tblStores.StoreNo,
tblItemListLedo.LedoItemDescription, tblInvoiceCurrent.[USF Product Number],
tblInvoiceCurrent.[Product Description], tblInvoiceCurrent.Brand,
tblItemListVendor.[Product Options], tblInvoiceCurrent.[Ship Date],
tblStores.IncludeInRpts, tblItemListLedo.LIN
HAVING (((tblStores.StoreNo) Like "*" &
[Forms]![frmLedoItemVelocityInput]![cboStoreName] & "*") AND
((tblItemListVendor.[Product Options]) Like "" &
[Forms]![frmLedoItemVelocityInput]![cboProductList] & "*") AND
((tblInvoiceCurrent.[Ship Date]) Between
[Forms]![frmLedoItemVelocityInput]![txtBeginningDate] And
[Forms]![frmLedoItemVelocityInput]![txtEndingDate]) AND
((Sum(tblInvoiceCurrent.[Cases Shipped]))>0) AND
((tblStores.IncludeInRpts)=-1));
 
G

Guest

Allow me to suggest another approach. What happens when you want to select 3
or 4 items? More building of combo boxes and code changes. Since your combo
box only has 5 items, a multi-select list box might be better. Frequent forum
contributor Roger Carlson has a sample database on multi-select list boxes at
the following:

http://www.rogersaccesslibrary.com/download3.asp?SampleName=MultiSelectListboxes.mdb

Watch out for word wrapping above.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Lisa said:
It is long sorry. What I want is I have 2combo boxes on a form that pertain
to Product options. There are 5 choices in each combo box. Both boxes come
from the same table field. I want be able to choose more then one choice ex.
a specked item from box 1and a Non-specked item from box 2. Is that possible
and if so how?

SELECT tblStores.StoreName, tblStores.StoreNo,
tblItemListLedo.LedoItemDescription, tblInvoiceCurrent.[USF Product Number],
tblInvoiceCurrent.[Product Description], tblInvoiceCurrent.Brand,
tblItemListVendor.[Product Options], tblInvoiceCurrent.[Ship Date],
Sum(tblInvoiceCurrent.[Cases Shipped]) AS [SumOfCases Shipped],
tblStores.IncludeInRpts
FROM tblStores INNER JOIN (tblItemListLedo INNER JOIN (tblStoresUSCustNos
INNER JOIN (tblInvoiceCurrent INNER JOIN tblItemListVendor ON
tblInvoiceCurrent.[USF Product Number] = tblItemListVendor.USFSItemNo) ON
tblStoresUSCustNos.USCustNo = tblInvoiceCurrent.[Customer Number]) ON
tblItemListLedo.LIN = tblItemListVendor.VListLedoItemDescription) ON
tblStores.StoreNo = tblStoresUSCustNos.StoreNo
GROUP BY tblStores.StoreName, tblStores.StoreNo,
tblItemListLedo.LedoItemDescription, tblInvoiceCurrent.[USF Product Number],
tblInvoiceCurrent.[Product Description], tblInvoiceCurrent.Brand,
tblItemListVendor.[Product Options], tblInvoiceCurrent.[Ship Date],
tblStores.IncludeInRpts, tblItemListLedo.LIN
HAVING (((tblStores.StoreNo) Like "*" &
[Forms]![frmLedoItemVelocityInput]![cboStoreName] & "*") AND
((tblItemListVendor.[Product Options]) Like "" &
[Forms]![frmLedoItemVelocityInput]![cboProductList] & "*") AND
((tblInvoiceCurrent.[Ship Date]) Between
[Forms]![frmLedoItemVelocityInput]![txtBeginningDate] And
[Forms]![frmLedoItemVelocityInput]![txtEndingDate]) AND
((Sum(tblInvoiceCurrent.[Cases Shipped]))>0) AND
((tblStores.IncludeInRpts)=-1));

--
Lisa S.

Jerry Whittle said:
Lisa,

Ditto. Show us the SQL. Open the query in design view. Next go to View, SQL
View and copy and past it here. Information on primary keys and relationships
would be a nice touch too.
Click to expand...
Click to expand...
 
G

Guest

I don't think that will work the options are not in a seperate table. The
example shows each option with an ID number as the key. My options are a list
option that I put in the table. Are there any other ways I can get this to
work without having to create a new table for my options. Doing that will
change my database to much.
--
Lisa S.


Jerry Whittle said:
Allow me to suggest another approach. What happens when you want to select 3
or 4 items? More building of combo boxes and code changes. Since your combo
box only has 5 items, a multi-select list box might be better. Frequent forum
contributor Roger Carlson has a sample database on multi-select list boxes at
the following:

http://www.rogersaccesslibrary.com/download3.asp?SampleName=MultiSelectListboxes.mdb

Watch out for word wrapping above.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Lisa said:
It is long sorry. What I want is I have 2combo boxes on a form that pertain
to Product options. There are 5 choices in each combo box. Both boxes come
from the same table field. I want be able to choose more then one choice ex.
a specked item from box 1and a Non-specked item from box 2. Is that possible
and if so how?

SELECT tblStores.StoreName, tblStores.StoreNo,
tblItemListLedo.LedoItemDescription, tblInvoiceCurrent.[USF Product Number],
tblInvoiceCurrent.[Product Description], tblInvoiceCurrent.Brand,
tblItemListVendor.[Product Options], tblInvoiceCurrent.[Ship Date],
Sum(tblInvoiceCurrent.[Cases Shipped]) AS [SumOfCases Shipped],
tblStores.IncludeInRpts
FROM tblStores INNER JOIN (tblItemListLedo INNER JOIN (tblStoresUSCustNos
INNER JOIN (tblInvoiceCurrent INNER JOIN tblItemListVendor ON
tblInvoiceCurrent.[USF Product Number] = tblItemListVendor.USFSItemNo) ON
tblStoresUSCustNos.USCustNo = tblInvoiceCurrent.[Customer Number]) ON
tblItemListLedo.LIN = tblItemListVendor.VListLedoItemDescription) ON
tblStores.StoreNo = tblStoresUSCustNos.StoreNo
GROUP BY tblStores.StoreName, tblStores.StoreNo,
tblItemListLedo.LedoItemDescription, tblInvoiceCurrent.[USF Product Number],
tblInvoiceCurrent.[Product Description], tblInvoiceCurrent.Brand,
tblItemListVendor.[Product Options], tblInvoiceCurrent.[Ship Date],
tblStores.IncludeInRpts, tblItemListLedo.LIN
HAVING (((tblStores.StoreNo) Like "*" &
[Forms]![frmLedoItemVelocityInput]![cboStoreName] & "*") AND
((tblItemListVendor.[Product Options]) Like "" &
[Forms]![frmLedoItemVelocityInput]![cboProductList] & "*") AND
((tblInvoiceCurrent.[Ship Date]) Between
[Forms]![frmLedoItemVelocityInput]![txtBeginningDate] And
[Forms]![frmLedoItemVelocityInput]![txtEndingDate]) AND
((Sum(tblInvoiceCurrent.[Cases Shipped]))>0) AND
((tblStores.IncludeInRpts)=-1));

--
Lisa S.

Jerry Whittle said:
Lisa,

Ditto. Show us the SQL. Open the query in design view. Next go to View, SQL
View and copy and past it here. Information on primary keys and relationships
would be a nice touch too.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

Lisa

We aren't there. We can't see what you are looking at. We have no idea
what data you are storing in your tables, nor how you are querying them.

More info, please!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I want my report to show if my product is (specked) or (non-specked)
In the query i am able to get only one of them to show up. I have tried use
OR, and AND. They still only show one. I have created two drop down's on the
form and have tried combinding them and that does not work.
Can anyone help.
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

Problem with Cross qry 1
Too many showing in query 1
Combining CrossTab Qry 1
Parameter qry and forms 4
Epson V750 - dust under glass 15
How to - Qry question 2
Union qry- results show #'s not words 6
query too slow 1

Top