Fill listbox if a form date falls between 2 dates in a query

[shenty]

Hi all - i need some help creating a query and think i've got my knickers in a twist.

Basically the attached form has a list box of all animals on a farm, including the Date Of Birth, Date Purchased and Date Moved Off. I only want the list box to be populated if the "Admin Date" falls between the 'Date Purchased' and 'Date Moved Off' OR 'Date Of Birth' and 'Date Moved Off' IF no purchase date applies.

When the admin date changes, obviously i want the listbox requerying.

This is my SQL of the query behind the listbox:-

SELECT AnimalRegister.AR_ID, AnimalRegister.TAG, AnimalRegister.Brand, AnimalRegister.Sex, AnimalRegister.Breed, AnimalRegister.DOB, AnimalRegister.[On Farm], Left([TAG],8) AS Expr1, Right([TAG],5) AS Expr2, AnimalRegister.[Purchase Date] AS Purchased, AnimalRegister.[Movement Date] AS [Moved Off]
FROM AnimalRegister
WHERE (((AnimalRegister.TAG)<>"Not tagged"))
ORDER BY AnimalRegister.Sex, Left([TAG],8), Right([TAG],5);

Any help or pointers would be greatful as i have splinters from scratching my head on this one !

I can cut the DB down & upload it if this helps.

Cheers

 

[khawar]

Use following sql

SELECT AnimalRegister.AR_ID, 
AnimalRegister.TAG, 
AnimalRegister.BRAND, 
AnimalRegister.SEX, 
AnimalRegister.BREED, 
AnimalRegister.DOB, 
AnimalRegister.[ON FARM], 
Left([TAG],8) AS Expr1, 
Right([TAG],5) AS Expr2, 
AnimalRegister.[PURCHASE DATE] AS Purchased, 
AnimalRegister.[MOVEMENT DATE] AS [Moved Off]
FROM AnimalRegister
WHERE (((AnimalRegister.TAG)<>"Not tagged") 
AND 
((AnimalRegister.[MOVEMENT DATE]) Is Not Null) 
AND 
(([B][COLOR="Red"][FORMS]![BATCH ADMIN FORM]![ADMIN DATE][/COLOR][/B]) 
Between (IIf(IsNull([purchase date]),[dob],[purchase date])) 
And 
[movement date])) 
OR 
(((AnimalRegister.TAG)<>"Not tagged") 
AND 
((AnimalRegister.[MOVEMENT DATE]) Is Null))
ORDER BY 
AnimalRegister.SEX, 
Left([TAG],8), 
Right([TAG],5);

replace form and field name with your form and field name

 

[shenty]

Thanks Khawar i think you've just got me back on track. A little fine tuning to the query and i believe its sorted.

I've changed the SQL now to:-

SELECT AnimalRegister.AR_ID, AnimalRegister.TAG, AnimalRegister.Brand, AnimalRegister.Sex, AnimalRegister.Breed, AnimalRegister.DOB, AnimalRegister.[Purchase Date] AS Purchased, AnimalRegister.[Movement Date] AS [Moved Off]
FROM AnimalRegister
WHERE (((AnimalRegister.TAG)<>"Not tagged") AND ((AnimalRegister.[Movement Date]) Is Not Null) AND (([forms]![AdminBatchForm]![txtDate]) Between (IIf(IsNull([Purchase Date]),[DOB],[Movement Date])) And [Movement Date])) OR (((AnimalRegister.TAG)<>"Not tagged") AND ((AnimalRegister.[Movement Date]) Is Null) AND (([forms]![AdminBatchForm]![txtDate])>[DOB]))
ORDER BY AnimalRegister.Sex, Left([TAG],8), Right([TAG],5);

Many thanks for that, a little testing now just to make sure but things are looking a bit rosier than earlier today.