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?
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.
0
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

0
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'
0
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.

hnasrCommented:
Could be a problem in syntax:
DMin ("field", "table", "afield = " & Me.aBox & " bfield =" & Me.bBox)
0
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
0
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
0
hnasrCommented:
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

0
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
0
hnasrCommented:
To get a value to use in code, try

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

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.

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.