Query pulling dates from one month prior to requested month

T

Thorson

A while back I changed my code to pull in all records from the date on a form
up until the 5th of the next month. However if the entry date of the record
and the actual date of the record where in the same month then it would pull
the record for the month of the actual date.

Now we are having some problems with it only pulling records up to 1 month
previous. For example when we enter 8/31/2009 on the form it pulls records
only until 7/31/2009. Is something wrong with my coding or is the issue
something else? Here is my SQL:

SELECT [qryBirthInfoandPurchas/Don].eartag,
[qryBirthInfoandPurchas/Don].calftattoo,
[qryBirthInfoandPurchas/Don].EntryDate,
[qryBirthInfoandPurchas/Don].calfbirthdate,
[qryBirthInfoandPurchas/Don].Expr1003, [qryBirthInfoandPurchas/Don].calfsex,
[qryBirthInfoandPurchas/Don].Expr1006,
[qryBirthInfoandPurchas/Don].BirthUnit,
[qryBirthInfoandPurchas/Don].BirthLocation
FROM tblBirthInformation RIGHT JOIN ([qryBirthInfoandPurchas/Don] RIGHT JOIN
[qryCurrentInventoryBirthInfoandPurchas/Don] ON
[qryBirthInfoandPurchas/Don].eartag =
[qryCurrentInventoryBirthInfoandPurchas/Don].eartag) ON
tblBirthInformation.EarTag =
[qryCurrentInventoryBirthInfoandPurchas/Don].eartag
WHERE
((([qryBirthInfoandPurchas/Don].EntryDate)<=DateSerial(Year([qryBirthInfoandPurchas/Don].[Expr1003]),Month([qryBirthInfoandPurchas/Don].[Expr1003])+1,5))
AND
(([qryBirthInfoandPurchas/Don].calfbirthdate)<=DateSerial(Year(([Forms]![frmCurrentInventoryDateQuery]![txtDate])),Month(([Forms]![frmCurrentInventoryDateQuery]![txtDate])),0)))
OR
((([qryBirthInfoandPurchas/Don].EntryDate)>DateSerial(Year([qryBirthInfoandPurchas/Don].[Expr1003]),Month([qryBirthInfoandPurchas/Don].[Expr1003])+1,5)
And
([qryBirthInfoandPurchas/Don].EntryDate)<=CVDate([Forms]![frmCurrentInventoryDateQuery]![txtDate])));
 

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

Top