Searching calculated fields

[staffrmj]

I have a relatively simple database that records disciplinary details
of our football (soccer) club. When players pick up red / yellow cards
in a match they can be banned for a period of time. The StartofBan
field is a calculated field - 1 day added to the DateofMatch field.
The EndofBan field is calculated by adding the LengthofBan field to
the StartofBan field. I have used a query to calculate this data. THis
all work well when entering data into the appropriate form.

I need to run a query to find all ineligible players for forthcoming
matches i.e. players for whom EndofBan> a particular date. However
when I try this it doesn't work.

Is it possible to search calculated fields such as this using a query?
I'm afraid I'm not great with SQL. Any help greatly appreciated.

 

[Al Campagna]

staffrmj,
Assuming that LengthOfBan is always a numeric "days" value ( 3 = 3 days)
Also, I'm assuming that you are not saving the EndOfBan date... since
that can always be recalculated "on the fly" in any subsequent form, report,
or query.

matches i.e. players for whom EndofBan> a particular date.

Actually that's not true...
If StartBan date is less than GameDate, AND BanEnd is greater than
GameDate, then this player is not elligible.

Just do the calculations within the query.
Using the query design grid... ('ll abbreviate your names)

StartBan LenBan BanEnd : DateAdd("d",LenBan,StartBan)
1/1/09 5 1/6/09
<[GameDate] and >[GameDate]

You would supply the GameDate using a parameter in the query, or using a
value from a form, or an InputBox, etc... etc...
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."