Dmin error

Hi all,

I have a task table and the tasks have a process number and a project number.

What i want to do is when someone completes a task it copies the date completed from that task to the next highest process number start date but only when the project is the same reference.

If Nz(Me.[Date Completed], 0) <> 0 Then
  '/ get the next ProcessNumber
  ProcessNum = DMin("[Process Order]", "[Tasks]", "[Date Completed] Is Null and Forms![Task Details].Project = [Tasks].[Project]")
  MsgBox ProcessNum
  CurrentDb.Execute "UPDATE Tasks SET [Start Date]=#" & Me.[Date Completed] & "# WHERE [Project]=" & Me.Project & " AND [Process Order]=" & ProcessNum
End If

Open in new window


The MsgBox always returns the same processnumber i'm on at the moment so it doesnt seem to be incrementing! Argh!

Any help?

Cheers

Brendan
eyeisystemsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
DMIN should refer the TABLE and not the FORM:

ProcessNum = DMin("[Process Order]", "[Tasks]", "[Date Completed] Is Null and Project =" Me.Project)

This assumes that your Task table contains a Field name "Project", and that the form where this code is run also has a Field/Control named 'Project.
eyeisystemsAuthor Commented:
Hi Scott,

That makes sense.

I'm getting an error on the 'M' of Me.Project stating 'Expected: list separator or ) presuming just syntax?

ProcessNum = DMin("[Process Order]", "[Tasks]", "[Date Completed] Is Null and Project =" Me.Project)

Open in new window

eyeisystemsAuthor Commented:
If i change it to:

ProcessNum = DMin("[Process Order]", "[Tasks]", "[Date Completed] Is Null and Project = Me.Project")

Open in new window


I get The Expression you entered as a query parameter produced this error: 'Me.Project'
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Hamed NasrRetired IT ProfessionalCommented:
Could be a problem in syntax:
DMin ("field", "table", "afield = " & Me.aBox & " bfield =" & Me.bBox)
eyeisystemsAuthor Commented:
Hi hnasr,

That still returns '1'

Task Details is the Form in which the code is run, Tasks is the table.

The Task Details is a form that is based on the tasks table.

Cheers

Brendan
eyeisystemsAuthor Commented:
Just thinking it might be easier to create a select query to return the value and run that?

SELECT Min([Process Order] as PrNum
FROM Tasks
WHERE [Tasks].[Project] = Me.[Project]
AND [Task Complete?] = False;

Open in new window


This is giving me a syntax error though any ideas?

Cheers

Brendan
Hamed NasrRetired IT ProfessionalCommented:
Try a select query with its SQL as:
SELECT Min([Process Order] as PrNum
FROM Tasks
WHERE [Tasks].[Project] = Forms![Task Details].[Project]
AND [Task Complete?] = False;

Open in new window

eyeisystemsAuthor Commented:
Thanks hnsar!

Now the last question, how do i get the result of the select query to be entered into a variable? So that i can use it in further code?

I thought i could just use ProcessNum = DoCmd.OpenQuery(NextDate) but i get an 'Expected Function or variable' error?

Cheers

Brendan
Hamed NasrRetired IT ProfessionalCommented:
To get a value to use in code, try

v = DMin("[Process Order]", "Tasks", "[Project] = " & Forms![Task Details].[Project] & " AND [Task Complete?] =" & False)

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
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
Microsoft Access

From novice to tech pro — start learning today.