Error displaying query in a MSHFlexgrid

Hi all, I am having a problem showing th results of a query in a flexgrid. The following queires work out the times that someone is not working on a job. Query 6 is the one that shows the output of the people not working. Then the code for displaying the query in a flexgrid has an error, saying that "Underfinedfunction 'DMin' in expression" i have tried declaring the dmin the the top level, but had no success. Any Ideas would be great. Thanks

===== The Queries =====

Select Query (Job0) - This part ensures all operative will have a distinct record for all the dates available in table Job
SELECT DISTINCT Oppname.Operative, Job.Date
FROM Job, Oppname;

Select Query (Job1) - Gets the original transaction from table job. This also includes empty job (unbooked from 8-4:30)
SELECT DISTINCT Job0.Operative, Job0.Date, Job.StartTime, Job.EndTime
FROM Job0 LEFT JOIN Job ON (Job0.Date = Job.Date) AND (Job0.Operative = Job.Operative);

Select Query (Job2) - Creates a formula fields namely IdleStartTime & IdleEndTime
SELECT JOB1.Operative, JOB1.Date, JOB1.StartTime, JOB1.EndTime, IIf(IsNull([StartTime]),"08:00",[JOB1]![EndTime]) AS IdleStartTime, IIf(IsNull([StartTime]),#16:30:00#,IIf(IsNull(DMin("[StartTime]","Job1","[StartTime]> #" & [EndTime] & "# And [Operative] = '" & Trim([Operative]) & "' And [Date] = #" & [Date] & "#")),#16:30:00#,DMin("[StartTime]","Job1","[StartTime]> #" & [EndTime] & "# And [Operative] = '" & Trim([Operative]) & "'  And [Date] = #" & [Date] & "#"))) AS IdleEndTime

Select Query (Job3) - Gets the Minimum StartTime in order to get idletime from 8:00 to StartTime
SELECT JOB1.Operative, JOB1.Date, Min(JOB1.StartTime) AS MinOfStartTime
GROUP BY JOB1.Operative, JOB1.Date;

Select Query (Job4) - Get idletime from 8:00 to StartTime
SELECT Job3.Operative, Job3.Date, "" AS StartTime, "" AS EndTime, #8:00:00# AS IdleStartTime, [Job3]![MinOfStartTime] AS IdleEndTime
WHERE ((([Job3]![MinOfStartTime])>#8:00:00#));

Union Query (Job5) - Combine the results of Job2 & Job4
Select * From Job2 UNION Select * From Job4;

Union Query (Job6) - Sort Accordingly
SELECT Job5.Operative, Job5.Date, Job5.StartTime, Job5.EndTime, Job5.IdleStartTime, Job5.IdleEndTime
ORDER BY Job5.Operative, Job5.Date, Format([StartTime],"hh:nn");

====== Code to display data in flexgrid ========

TrackMouseWheel MSHFlexGrid1.hwnd
Dim SQL As String
Dim AppDate
Dim sConnect As String
Dim icol As Integer
Dim cnJobs As ADODB.Connection  'cnPatient is database name
Dim rsJobs As ADODB.Recordset 'Appointment table
Set cnJobs = New ADODB.Connection
Set rsJobs = New ADODB.Recordset
Dim cnJobs1 As ADODB.Connection  'cnPatient is database name
Dim rsJobs1 As ADODB.Recordset 'Appointment table
Set cnJobs1 = New ADODB.Connection
Set rsJobs1 = New ADODB.Recordset
Dim dmin As String

dateclicked = Format(frmdiary.Calendar1.Value, "Short Date")

sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=Database\ClientDetails.mdb"

cnJobs.Open (sConnect)

SQL = "SELECT * FROM Job6 WHERE Date=#" & dateclicked & "#;"

Set rsJobs.ActiveConnection = cnJobs
rsJobs.Open SQL

With MSHFlexGrid1
    .Rows = 1
    .Cols = rsJobs.Fields.Count

    For icol = 0 To rsJobs.Fields.Count - 1
        .Col = icol
        .Text = rsJobs.Fields(icol).Name

    While Not rsJobs.EOF
        .Rows = .Rows + 1
        .Row = .Rows - 1

        For icol = 0 To rsJobs.Fields.Count - 1
            .Col = icol
            .Text = rsJobs(icol) & ""

End With
MSHFlexGrid1.RowHeight(0) = 400
MSHFlexGrid1.ColWidth(0) = 2000
MSHFlexGrid1.ColWidth(1) = 0
MSHFlexGrid1.ColWidth(2) = 0
MSHFlexGrid1.ColWidth(3) = 0
MSHFlexGrid1.ColWidth(4) = 1300
MSHFlexGrid1.ColWidth(5) = 1400

End Sub
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bob LambersonSoftware EngineerCommented:
You are dimensioning dmin as a string variable

> Dim dmin As String

but you are using it as a function in the following piece of job 2 query

IIf(IsNull(DMin("[StartTime]","Job1","[StartTime]> #" & [EndTime] & "# And [Operative] = '" & Trim([Operative]) & "' And [Date] = #" & [Date] & "#"))

That is causing the error, but I'm not sure what you are intending to do.
finally. i got it.!!! sorry for the delay. actually i almost forgot your querry. im so busy right now. but i got it this time.

replace job2 with

Select Query (Job2) - Creates a formula fields namely IdleStartTime & IdleEndTime
SELECT JOB1.Operative, JOB1.Date, JOB1.StartTime, JOB1.EndTime, IIf(IsNull([StartTime]),"08:00",[JOB1]![EndTime]) AS IdleStartTime, IIf(IsNull((Select Min([StartTime]) From Job1 as J1 Where ([J1].[StartTime] > [Job1].[EndTime] And [Job1].[Operative] = [j1].[Operative]  And [job1].[Date] = [j1].[Date]))),"04:30 PM",(Select Min([StartTime]) From Job1 as J1 Where ([J1].[StartTime] > [Job1].[EndTime] And [Job1].[Operative] = [j1].[Operative]  And [job1].[Date] = [j1].[Date]))) AS IdleEndTime

the reason why the previous query doesnt work is that im using DMin to get the next job in queue. but Dmin is an agregate function in MS access and available outside MSaccess. The code above is a pure SQL command. and it works. sorry again for the delay. :)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NOT available outside MSAccess i mean. I tested it using your VB code and works fine. I think your unsleeping nights are over Stephen. :)
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Bob LambersonSoftware EngineerCommented:

dmin will work outside of access, specifically in VB, if you reference the Access Library. A better description of the solution would be that you had not referenced the Access Library in your references, and that you were declaring DMin as a variable - both things contributed to the problem. You can test this by referencing the Access Library in your vb project and running your original query.

If you want to close this question see

Thanks   -Bob
StephenJnrAuthor Commented:
Thanks Noel, yet again you have helped my alot, much appreciated. Now I am almost finished my program ive been working on.


yes thats true, i'd tried that actually. but you will have different result for that particular querry. DMin must give different results on each record that depends on its current value. But if you call DMin within VB(through referensing Access libarary) it will give a single value for the whole query. It cannot satisfy the requirement of stephen. If you are still interested to know what stephen needs, you can review his questions asked. Im sorry, im wrong stipulating the reason why the previous querry doesnt work. My mistake BobLamberson. Thanks anyway. I stand corrected. :)

Thanks again for the points. I dont mean to forget your querry. Im glad its working this time. :)

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.