select top not working

[complexx]

I feel like I'm misunderstanding something basic here.

I have a report that lists systems for my desired customer in the detail. I leave some blank space next to the system name so that my techs who are out in the field have room to write in data by hand for their service.

next to the blank space I'm trying to pull up the data for each system from our last service visit for reference.

the detail is formatted like the following

| SYSTEM_NAME | _(blank space) _____________| __(last months data) __|

last months data is a subreport that is linked to the main report via the SYSTEM_NAME

My issue is that in the third (last months data - subreport) region, its listing the entire system history, rather than only the TOP 1 most recent record.

I've tried to modify the query design that the subreport uses to only select the TOP 1 and it simply returns nothing to the report (the area is left blank) in this case.

SQL

SELECT [Service Records].RecordID, [Service Records].Date, [Service Records].SYSTEMID, [Service Records].TDS, [Service Records].P_ALK, [Service Records].Sulfite, [Service Records].Nitrite, [Service Records].Organophosphate, [Service Records].Chemical_Addition, [Service Records].Freeze_Protection, [Service Records].BIO_1_ADD, [Service Records].BIO_2_ADD, [Service Records].BIO_3_ADD, [Service Records].pH, [Service Records].[Cl-], [Service Records].TH, [Service Records].WTR_MTR
FROM [Service Records]
ORDER BY [Service Records].Date DESC;

if I change it to:

SELECT TOP 1 [Service Records].RecordID...........etc

I get nothing displayed on my form.

 

[llkhoutx]

SELECT TOP 1 [Service Records].RecordID...........etc

should work.

I suspect that's somethings wrong in your SQL string. Maybe a bad field name. Try the string in the QBE window to be sure that it's correct.

 

[complexx]

It has to be something else. The only thing I change is adding TOP 1. It works when its simply SELECT blah blah blah, but when I try to SELECT TOP 1, it returns no results.

 

[shudini]

Can you open the query in the QBE window? If so, just go to the properties of the query and enter 1 into the Top Values field. Then, if it works, you can go to SQL view to see how Access built the query.

 

[complexx]

Unfortunately that didn't seem to work either.

 

[shudini]

Which part - opening in the QBE window, or returning the top 1 record? That would be very strange if you are building the entire string in QBE, and when you don't specify it returns all, but with top 1 it doesn't work.

Would you consider uploading a copy of the dbase (delete any private data?)

 

[llkhoutx]

Actually you should try both.

Set the Top value in the QBE queries property.

 

[complexx]

Problem solved. Thank you Shudini.