Want only 4 part numbers, and whatever date I am prompt to enter

[Czeszyn]

I am trying to get my query to do the following:

for my part numbers, I want to be able to only get my four part numbers and only see those on a chosen date.

When I enter the four part numbers under the criteria, and I chose a date, then look at the data, I get the part numbers that I want, but I am getting all dates, I only want dates that I am prompt to enter.

Any idea in what I am doing wrong.

Tony
:banghead:

 

[CJ_London]

if you provide some code or other clue to what you are doing, we can probably help.

 

[Czeszyn]

Hi CJ_London

I was building a query, no coding. I am still new to this. I had put each of the part numbers under each other in the Criteria area. And the date asks for a specific dates. Everything works, in giving me the four part numbers that I need, but it gives me every date. I only want the date that I put into the prompts. I hope that this helps??

Thank you for helping.
Tony

 

[CJ_London]

OK - so we need to see the sql code to your query

 

[Czeszyn]

Hi CJ_London
Is this what you need?

SELECT tblScrapLog.ScDate, tblScrapLog.Shift, tblPartInfo.PartNumber, tblScrapLog.TotParts, tblScrapLog.BadForm, tblScrapLog.Dirt, tblScrapLog.FinTrim, tblScrapLog.Foil, tblScrapLog.Scratch, tblScrapLog.Webs, tblScrapLog.Other, tblScrapLog.TotalScrap, tblScrapLog.ScrapPercent, tblScrapLog.Department
FROM tblPartInfo INNER JOIN tblScrapLog ON tblPartInfo.PartID = tblScrapLog.PartNumber
WHERE (((tblScrapLog.ScDate) Between [Forms]![KIA Menu]![Start Date] And [Forms]![KIA Menu]![End Date]) AND ((tblPartInfo.PartNumber)="3119322-KBH -- LH Front - IMS -- Piano Black") AND ((tblScrapLog.Department)="Trimming")) OR (((tblPartInfo.PartNumber)="3119324-KBH -- RH Front Standard - Piano Black")) OR (((tblPartInfo.PartNumber)="3119325-KBH -- LH Rear - Piano Black")) OR (((tblPartInfo.PartNumber)="3119326-KBH -- RH Rear - Piano Black"));


Thank you again

 

[CJ_London]

You have mixed OR's and AND's so the date part of the criteria needs to be on each row of the or part.

At the moment this is saying

this - scDate between start and end AND partnumber=3119322... AND department=trimming
OR partnumber=3119324...
OR partnumber=3119325...
OR partnumber=3119326...

so the scdate and department are only being applied to partnumber 3119322....

Assuming department should be applied to all partnumbers as well as dates, try this

WHERE tblScrapLog.ScDate Between [Forms]![KIA Menu]![Start Date] And [Forms]![KIA Menu]![End Date] AND tblPartInfo.PartNumber in ("3119322-KBH -- LH Front - IMS -- Piano Black","3119324-KBH -- RH Front Standard - Piano Black","3119325-KBH -- LH Rear - Piano Black","3119326-KBH -- RH Rear - Piano Black") AND tblScrapLog.Department="Trimming"

 

[Czeszyn]

CJ_London

Would I delete the other SQL or add that in??? Have not worked with the SQL area before. I really do appreciate your help. Everyone on this forum is so smart.

Tony

 

[CJ_London]

it replaces this part of your query

WHERE (((tblScrapLog.ScDate) Between [Forms]![KIA Menu]![Start Date] And [Forms]![KIA Menu]![End Date]) AND ((tblPartInfo.PartNumber)="3119322-KBH -- LH Front - IMS -- Piano Black") AND ((tblScrapLog.Department)="Trimming")) OR (((tblPartInfo.PartNumber)="3119324-KBH -- RH Front Standard - Piano Black")) OR (((tblPartInfo.PartNumber)="3119325-KBH -- LH Rear - Piano Black")) OR (((tblPartInfo.PartNumber)="3119326-KBH -- RH Rear - Piano Black"));

 

[Czeszyn]

CJ_London

That did not allow anything to come up.

Tony

 

[CJ_London]

That did not allow anything to come up.

You mean you got an error message?

What dates did you use? and what records would you expect to have been returned by the query?

 

[Czeszyn]

CJ_London

Hey, got to work, forgot that I had to refresh. That is really cool. You are very smart. How are you with PopUp Forms? I put a question on the Forum but the guy is not very helpful.

Thanks again for this one.

 

[Czeszyn]

CJ_London

Hey that works great but I have a question. When I bring up the query everything comes in its number order. Looks great. When I turn that into a data, it puts them in a different order, how can I make sure that the report will show the data just like what is in my query?

Thank you again.
Tony

 

[CJ_London]

Be aware there is no 'set order', no 'first' or 'last' with any relevance, the order records are reported depends on how the query is written - to fix it in a particular order, you need to sort it which is easily done from the query grid

 

[Czeszyn]

CJ_London

What do you mean query grid? And how would I fix it?

Tony

 

[CJ_London]

Another name for it is the query designer - one of the options is sort