Not pulling Past Data

[Czeszyn]

Not understand this problem. I am putting a query that pulls from data from the same table. I have a date range so that I can choose a month period. Now, when I put in say the current month, everything works find. It gives me all the items that we had worked on for that month.

Now here is where the problem is, when I go back say to check an earlier month, say Jan, it does not show all the items we worked on. I know that the items are there because when I do my other queries and reports they show up their.

Not sure why it is not pulling the data in past months? :banghead:

I hope that this is a easy fix.
Thank you in advance.
Tony

 

[pbaldy]

What exactly does the data in the date field look like (and what is the data type)? What is the criteria you're using?

 

[Czeszyn]

It is just the Date, ex start date: 1/1/2015, and a end date: 1/31/2015. I am asking it bring those jobs that we worked on during those date. This works for my other queries that I use for reports and those show up for those dates. But for this query is does not was to work right.

Thank you for helping
Tony

 

[spikepl]

You have not answered Paul's questions

 

[Czeszyn]

Sorry, I am new to this. The date field is set for Date, and it works for the current month bring in everything we worked on. But when I go to past months, it leaves a lot of jobs out, when I know that the data is there. I have other reports that I use the data to bring in the data I need and it works fine for those reports past and present. I hope that this is what you are asking for.

Tony

 

[Minty]

Can you post up the SQL of the query that doesn't work.

 

[Czeszyn]

Yes.
SELECT tblScrapLog.ScDate, tblScrapLog.Department, tblScrapLog.Shift, tblPartInfo.PartNumber, tblPartInfo.[Film Type], tblPartInfo.[Matl/Part], tblScrapLog.StUpMa, tblScrapLog.BadForm, [tblScrapLog]![THWeb]+[tblScrapLog]![Webs] AS Webs, [tblScrapLog]![THDirt]+[tblScrapLog]![Dirt] AS Dirt, [tblScrapLog]![THFoil]+[tblScrapLog]![Foil] AS Foil, [tblScrapLog]![THScratch]+[tblScrapLog]![Scratch] AS Scratch, tblScrapLog.CavSep, tblScrapLog.FinTrim, tblScrapLog.Crack, tblScrapLog.Crease, tblScrapLog.Pits, tblScrapLog.[Ben/Smash/Pull], [tblScrapLog]![THColdForm]+[tblScrapLog]![ColdForm] AS [Cold Form], [tblScrapLog]![THDeMolding]+[tblScrapLog]![DeMolding] AS DeMolding, tblScrapLog.THSheetSep, tblScrapLog.THAlignment, tblScrapLog.Melt, [tblScrapLog]![THOther]+[tblScrapLog]![Other] AS Other, tblScrapLog.THQAHeatTape, tblScrapLog.DestructiveTest, tblScrapLog.THTapeSplice, [tblScrapLog]![THFoil]+[tblScrapLog]![Foil] AS [Total Supplier Defect], [tblScrapLog]![THTotalScrap]+[tblScrapLog]![TotScrwBlank] AS [Total Scrap Pieces], (([Total Scrap Pieces]*[Matl/Part])+([StUpMa]/12)) AS [Scrap Length Total LF]
FROM tblPartInfo INNER JOIN tblScrapLog ON (tblPartInfo.PartID = tblScrapLog.PartNumber) AND (tblPartInfo.PartID = tblScrapLog.[Film Supplier])
WHERE (((tblScrapLog.ScDate) Between [Forms]![frmOtherReports]![Start Date] And [Forms]![frmOtherReports]![End Date]))
ORDER BY tblScrapLog.ScDate;

Is there a way to send you the database to see if I did the whole thing correct.

 

[Minty]

So what happens if you hard code the dates into your query? Does it still return the same data or do the missing expected results appear?

If the data is still missing then your data simply isn't matching the criteria.

Also the join statement looks strange to me;

(tblPartInfo.PartID = tblScrapLog.PartNumber) AND (tblPartInfo.PartID = tblScrapLog.[Film Supplier])

Not knowing your data structure, but I'm not sure how tblPartInfo.PartID would be equal to two fields at the same time ?

 

[pbaldy]

You can post the db here:

http://www.access-programmers.co.uk/forums/showthread.php?t=140587

 

[Czeszyn]

I think you are right. I had made it where I just put in the date, and it still only brought some parts in, but the rest of them. I also tried a test, by just doing a small query where I just have the date and part number, and the same thing. So this seems it got something to do with not looking at only some of the parts on the given day requested. How would I fix this???

 

[Czeszyn]

I did another test, where I went to the main page, and did a search between dates, and that did show everything.

 

[Czeszyn]

I had to ask the owner of the company about sending the db, they prefer not too because of the data that is in it. Darn, it would sure help if I could.

 

[Czeszyn]

I think you were also right about this:
(tblPartInfo.PartID = tblScrapLog.PartNumber) AND (tblPartInfo.PartID = tblScrapLog.[Film Supplier])

When I too the Film Supplier out, everything showed up. So I guess the next question would be how can I get the Film Supplier into my query without it doing what it just did.

 

[Minty]

Are you trying to only show a particular (tblScrapLog.[Film Supplier]) ?

If so then the SQL would be

(tblPartInfo.PartID = tblScrapLog.PartNumber) AND (tblScrapLog.[Film Supplier] = 'Enter_Film_Supplier')

You could put another control on your form to select the Film Supplier , maybe a combo box?

 

[Czeszyn]

Each part has their Film Supplier listed, so for each part that is printed out, it would also show their Film Supplier. I think there is a conflict, because the Film Supplier is in the Parts Table, and they are both required to pull the part and film. I think that is why it is acting the way it is. Any way to fix this.

 

[Minty]

Just add the field to the SELECT statement - tblPartInfo.[Film Supplier]
As they are joined it should just work?

You can strip out sensitive info from your DB like customer addresses and names and post it up it would be a lot easier.

 

[Czeszyn]

Thank you Minty, Everything is working great now. Thank you again for the help.