Select Top 1

[sendtobrad]

I am trying to return every field for the highest (most recent record) in my table. I used SELECT TOP 1 in front the query and it is returning the highest record with no null values. I almost always have null values. How do I get around this?

In a related question, how do I get a query which I want to use as the source for a report to only show fields with values?

 

[Alc]

I am trying to return every field for the highest (most recent record) in my table. I used SELECT TOP 1 in front the query and it is returning the highest record with no null values. I almost always have null values. How do I get around this?

Do the records have an ID number? If so, you could select the record having the highest ID.

In a related question, how do I get a query which I want to use as the source for a report to only show fields with values?

Use the query builder to add 'Is Not Null' as a criteria in each field.

 

[sendtobrad]

Yes I was trying to select the record with the highest ID by using SELECT TOP 1. This is not returning any records with null values. Will select MAX work with Nulls?

 

[rainman89]

Do you want null values returned?

 

[sendtobrad]

No, I have a results area on a form that feeds an underlying table and am trying to create a query that only returns the fields (or at least only show the fields) which have values on a report for the most current record. I have been using SELECT TOP 1 on the ID field which gives me the most recent record but only when I put a value in every field. When I have any nulls it returns the last record with no nulls. I need to return the most current record everytime and want to display only fields which are not null.

 

[ByteMyzer]

Can you post a copy of the query's SQL statement, and highlight the field with the Null values that is apparently causing the problem?

 

[sendtobrad]

SELECT TOP 1 tblMissionRequest.Misson_ID, tblMissionRequest.Loc1, tblMissionRequest.HRS1, tblMissionRequest.Aps1, tblMissionRequest.TBs1, tblMissionRequest.GAs1, tblMissionRequest.VEH1, tblMissionRequest.Veh_type1, tblMissionRequest.drugs_szed1, tblMissionRequest.Unit_drugs1, tblMissionRequest.Desc_drugs1, tblMissionRequest.Loc2, tblMissionRequest.HRS2, tblMissionRequest.Aps2, tblMissionRequest.TBs2, tblMissionRequest.GAs2, tblMissionRequest.VEH2, tblMissionRequest.veh_type2, tblMissionRequest.Drugs_szed2, tblMissionRequest.Unit_drugs2, tblMissionRequest.Desc_drugs2, tblMissionRequest.Loc3, tblMissionRequest.hrs3, tblMissionRequest.Aps3, tblMissionRequest.Tbs3, tblMissionRequest.GAs3, tblMissionRequest.Veh3, tblMissionRequest.Veh_type3, tblMissionRequest.Drugs_szed3, tblMissionRequest.unit_drugs3, tblMissionRequest.Desc_drugs3, tblMissionRequest.Loc4, tblMissionRequest.hrs4, tblMissionRequest.APs4, tblMissionRequest.TBs4, tblMissionRequest.GAs4, tblMissionRequest.Veh4, tblMissionRequest.veh_type4, tblMissionRequest.drugs_szed4
FROM tblMissionRequest

Almost any of the fields past (anyname)1 will be blank reqularly.