Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Error displaying query in a MSHFlexgrid

Posted on 2004-11-10
Medium Priority
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
  • 3
  • 2
LVL 12

Expert Comment

by:Bob Lamberson
ID: 12552144
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.

Accepted Solution

Noel_Castillo earned 1000 total points
ID: 12552289
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. :)

Expert Comment

ID: 12552310
NOT available outside MSAccess i mean. I tested it using your VB code and works fine. I think your unsleeping nights are over Stephen. :)
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

LVL 12

Expert Comment

by:Bob Lamberson
ID: 12555571

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 http://www.experts-exchange.com/help.jsp#hs5

Thanks   -Bob

Author Comment

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


Expert Comment

ID: 12562041

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

581 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