Error displaying query in a MSHFlexgrid

Posted on 2004-11-10
Last Modified: 2008-03-17
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
Question by:StephenJnr
    LVL 12

    Expert Comment

    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.
    LVL 3

    Accepted Solution

    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
    FROM JOB1;

    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. :)
    LVL 3

    Expert Comment

    NOT available outside MSAccess i mean. I tested it using your VB code and works fine. I think your unsleeping nights are over Stephen. :)
    LVL 12

    Expert Comment


    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

    Author Comment

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

    LVL 3

    Expert Comment


    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. :)


    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
    This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now