*REPOST* Query Pulling dates from one month prior to requested mon

  • Thread starter Thread starter Thorson
  • Start date Start date
T

Thorson

I posted this a few days ago, but no one has replied yet. If someone has
any suggestions that would be great. I think the problem is with my coding
in the criteria section for calfbirthdate and entrydate.

Here is my previous post, thanks!
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])));
 
One of my issues with your question is that you don't even take the time to
rename/alias your columns form Expr1003 and Expr1006 so how hard did you work
on resolving this yourself.

Try take a look at the organized and simplified where clause to see if you
can find your issue.
WHERE
(
(
EntryDate<=DateSerial(Year([Expr1003]),Month([Expr1003])+1,5)
)
AND
(
calfbirthdate<=DateSerial(Year([txtDate]),Month([txtDate]),0)
)
)
OR
(
(
EntryDate>DateSerial(Year([Expr1003]),Month([Expr1003])+1,5)
And
EntryDate<=CVDate([txtDate])
)
)
;
 
Part of the problem is that I and my co-worker are both very new to access
and know very little, epically when it comes to writing code/criteria.

As far as the Expr1003 and Expr1006. I had this in same thing in several
places throughout the database, when asking a question in the past I was
informed that this was bad naming practice and I should take the time to give
these columns correct names. I thought I went through and did that
everywhere possible. I missed these, and also I am not sure how to name them
since they are in a Union Query and it automatically gives them the expr.
names, I am not familiar with Union Queries and do not know how to give them
a name like in a select query.

Expr1003 is the acquisition date of the animals and Expr1006 is the owner of
the animals at the time of acquisition.

As far as the code/criteria, I had help writing it in the past and am not
sure what would cause it to be one month prior. In the past I wrote an
equation to make criteria pull up records 1 month prior but I don't remember
what the small change was. I figured it was just a small glitch in my
criteria that was causing the problem this time and that someone could easily
say what it was. My co-worker and I did spend a couple of hours trying to
figure out the problem. We compared it to other coding/criteria we have in
the database that pulls up 1 month prior from the date entered on the form,
however it looks identical, which makes me think that that is the problem,
but neither him or I know what to change.

We will continue to work on it, thanks for any help you can offer.
 
You might try find the calculated value based on your form control by opening
the debug window (press Ctrl+G) and entering the following all on one line:
?
DateSerial(Year([Forms]![frmCurrentInventoryDateQuery]![txtDate]),Month([Forms]![frmCurrentInventoryDateQuery]![txtDate]),0)

The form must be open with a date entered into txtDate.
 
Back
Top