Trying to compare dates to parameter date not working

[jnrussell]

I have a table of aquarium tank contents. When an animal is put in the tank the date and genus species is entered. When animals are removed the date and genus species is entered as another record. I'm trying to create a query that shows what animals are in the tank on a specific date. I've created a parameter for the query that prompts for the date. However, I've tried all sorts of criteria for the date field using that parameter and either end up with all the records in the table or only the ones that match the parameter date. What syntax should I use for the criteria to make this work?

[pbaldy]

I might have designed it differently, but I assume there's something that differentiates a "going in" record from a "coming out" record? Presuming so, you basically have an inventory situation, so you'd add up the transactions (going in's minus coming out's) as of the desired date. If you're still stuck and can provide a sample db, it may be easier to help.

[jnrussell]

The only thing that designates "going in" and coming out" is a positive or negative number of animals. It is mostly used to track transfers between tanks, but we also want to be able to see what is in a tank on a given date. Since I'm still in the designing process of the database, I'd be interested to hear how you would have designed this table differently.

[pbaldy]

I said might because it depends on the data. If you were referring to specific animals, I might have used a single record with fields for both in and out dates. Fish number xyz when in on 1/17/2014 and came out on 1/31/2014 type of thing. If you're putting in 50 of species xyz on one date and might take out 10 on another date, 10 more on some other date, I'd leave it like you have it.

[jnrussell]

Yes, we might be taking out different numbers than we put in, like if one dies. I did get the report I need. I created two queries for the report. The first finds all records less than or equal to the date I'm interested in. The second query is based on the first and sums the number of animals. My mistake was trying to use two date when I only needed to have it look at dates less than or equal to the one I want. And I also needed the second query to add the number of animals in the records that the first query found.

Thanks for your help. It got me to thinking in a different direction, which was exactly what I needed.

[pbaldy]

I would have thought it could be done with one query, but glad you have a working solution.