Need to sort 'inspection date" in ascending order using the code below

[monkeysee]

=IIf([Priority]="1",DateAdd("m",4,[LastOfInspectionDate]),IIf([Priority]="2",DateAdd("yyyy",1,[LastOfInspectionDate]),DateAdd("yyyy",2,[LastOfInspectionDate])))

 

[dhookom]

Can you be a bit more descriptive in your post? I expect you can add the expression to your record source so you can do the sorting of your form or report or query or recordset.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[monkeysee]

Sorry, guess I was a little vague.
I have a query:

SELECT QryInspLastDate.FacilityID, QryInspLastDate.MaxOfInspectionDate, Food.BusinessName, Food.AddressLocationID, Address.CityName, Food.Priority, Food.NameID, Food.CertifiedManager, License.LicenseNo, QryInspLastDate.TypeID, Food.Status
FROM ((Food INNER JOIN Address ON Food.AddressLocationID = Address.AddressLocationID) INNER JOIN License ON Food.FacilityID = License.FacilityID) LEFT JOIN QryInspLastDate ON Food.FacilityID = QryInspLastDate.FacilityID
WHERE (((QryInspLastDate.TypeID)=2 Or (QryInspLastDate.TypeID) Is Null) AND ((Food.Status)="active")) OR (((QryInspLastDate.TypeID)=23))
ORDER BY Food.BusinessName;

The field I wish to sort ascending is a calculated field in a text box on the report design: =IIf([Priority]="1",DateAdd("m",4,[LastOfInspectionDate]),IIf([Priority]="2",DateAdd("yyyy",1,[LastOfInspectionDate]),DateAdd("yyyy",2,[LastOfInspectionDate])))

I tried to use the group function in the report, but since it is not an actual field in the query, it is not appearing as a choice.
I also tried to right click in the field and choose sort, but the option to sort is not available.

thanks

 

[dhookom]

Did you try to add the expression to your query? I think it would be something like:

SELECT QryInspLastDate.FacilityID, QryInspLastDate.MaxOfInspectionDate, Food.BusinessName, Food.AddressLocationID, Address.CityName, 
Food.Priority, Food.NameID, Food.CertifiedManager, License.LicenseNo, QryInspLastDate.TypeID, Food.Status,
IIf([Priority]="1",DateAdd("m",4,[LastOfInspectionDate]),IIf([Priority]="2",DateAdd("yyyy",1,[LastOfInspectionDate]),DateAdd("yyyy",2,[LastOfInspectionDate]))) AS NextInspection
FROM ((Food INNER JOIN Address ON Food.AddressLocationID = Address.AddressLocationID) INNER JOIN License ON Food.FacilityID = License.FacilityID) 
LEFT JOIN QryInspLastDate ON Food.FacilityID = QryInspLastDate.FacilityID
WHERE (((QryInspLastDate.TypeID)=2 Or (QryInspLastDate.TypeID) Is Null) AND ((Food.Status)="active")) OR (((QryInspLastDate.TypeID)=23))
ORDER BY Food.BusinessName;

You can then sort your report on the column NextInspection.

Do you have a priority table with the number of months between inspections? If not, you should consider it since your requirements may change and I would hate to manage SQL statements and control sources.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016