I have a search that returns the results I want EXCEPT if there are no job records with MfgStartDate matching Today's Date (result from the GETDATE)
How can I update the attached search to find what it currently finds (because it works well as long as there are records that have today as a start date) and if RecordCount = 0, use the next available date in the table that is greater than today's date?
For example, if its Sunday Oct 23rd, the date it uses to search for the jobs should be Monday Oct 24th IF there are no job records that have Oct 23rd as its Start Date.
//find first job for today and set JobSchedule = 0 AS Total
with dmMfgQuantities.qrySearch do begin
sql.Add('SELECT TOP(1)JobID, JobSchedule FROM MfgSchedulingView WHERE (Mfg_Start = CONVERT(datetime, CONVERT(char(12), GETDATE()))) ORDER BY Mfg_Start, Orders');
sJobID := FieldValues['JobID'];