Query runs fine, but not in report...

[Starchildren3317]

First - looks like I have found my new home. This site is exactly what I was looking for. Have all ready found some great tips here so thanks!

OK, now on to my question...

I have a subreport that I am using to grab the date that an order set was entered into the system. The issue is that the same order set can have multiple entered days until all of the samples belonging to the order set have been entered.

However, I am only interested in the very first date entered.

Here is what I have:

SELECT DISTINCT TOP 1 DatePart("M",[EnteredDate]) & "/" & DatePart("D",[EnteredDate]) & "/" & DatePart("YYYY",[EnteredDate]) AS EnterDate, Results.OrderID
FROM Results

Now when I run this query it works fine, I only get a single date - the first date entered.

However, when I run the report that this subreport belongs it gives me every date entered.

I have the links properly set for the subreport - linked by OrderID

Also, I should mention that the EnteredDate field is actually a date / time field. This is why I have concatenated the date because I only want the m/d/y. When there are mulitple Times entered but in the same day, I will only get one entered date. However, if there are multiple days entered that is when the report (not the query) gives the multiple entered dates.

Any tips would be much appreciated! Thanks!

 

[vbaInet]

You mentioned you want to get the very first date entered. How are you going to identify the first date entered? Is the first date entered always going to be the minimum date in the list?

 

[Starchildren3317]

Yes, the first date entered would actually be the first result in the query. So knowing that, I figured using TOP(1) would get it for me. But for some reason it is still grabbing multiple results.

 

[vbaInet]

No, my point is how are you guaranteed that the first record in the query is the first date ENTERED. Is there some sort of Autonumber field that generates a sequential number of dates entered? Or if we were to get the minimum date, would that suffice as the first date ENTERED?

 

[Brianwarnock]

To handle just the date use Format.

Brian