Stuck on a query expression

  • Thread starter Thread starter MeSteve
  • Start date Start date
M

MeSteve

I am trying to do an Iif expression based on the value in my table. If the
ID = 6 then return the date in the record having the ID 6, else return the
date in the record having an ID of 7. All query records are returning the
date where ID = 6 is first found instead of the date associated with that
record. I don't realy know what I am doing when it comes to an Iif
expression within a query.

myECD:
IIf([milestoneID]=6<>"",DLookUp("[MileDate]","[tblProjectMilestones]",[milestoneID]=7),DLookUp("[miledate]","[tblProjectMilestones]",[milestoneID]=6))
 
MeSteve said:
I am trying to do an Iif expression based on the value in my table. If the
ID = 6 then return the date in the record having the ID 6, else return the
date in the record having an ID of 7. All query records are returning the
date where ID = 6 is first found instead of the date associated with that
record. I don't realy know what I am doing when it comes to an Iif
expression within a query.

myECD:
IIf([milestoneID]=6<>"",DLookUp("[MileDate]","[tblProjectMilestones]",[milestoneID]=7),DLookUp("[miledate]","[tblProjectMilestones]",[milestoneID]=6))
Click to expand...

The condition [milestoneID]=6<>"" is mostlikely nonsense.

Try removing the <>"" or explaining what purpose you
thought it would serve.
 
Try something like:
MyECD: DLookUp("MileDate","tblProjectMilestones","MilestoneID =" &
[MileStoneID])
 
Perhaps what you want is

IIf([milestoneID]=6
,DLookUp("[MileDate]","[tblProjectMilestones]","[milestoneID]=6")
,DLookUp("[miledate]","[tblProjectMilestones]","[milestoneID]=7"))

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
I have no idea what MilestoneID =" & [MileStoneID] would do for me, but it
does not return the desired results.

Duane Hookom said:
Try something like:
MyECD: DLookUp("MileDate","tblProjectMilestones","MilestoneID =" &
[MileStoneID])

--
Duane Hookom
Microsoft Access MVP


MeSteve said:
I am trying to do an Iif expression based on the value in my table. If the
ID = 6 then return the date in the record having the ID 6, else return the
date in the record having an ID of 7. All query records are returning the
date where ID = 6 is first found instead of the date associated with that
record. I don't realy know what I am doing when it comes to an Iif
expression within a query.

myECD:
IIf([milestoneID]=6<>"",DLookUp("[MileDate]","[tblProjectMilestones]",[milestoneID]=7),DLookUp("[miledate]","[tblProjectMilestones]",[milestoneID]=6))
Click to expand...
Click to expand...
 
You are correct about that condition being erroneous and I had removed that
shortly after posting my question.

Marshall Barton said:
MeSteve said:
I am trying to do an Iif expression based on the value in my table. If the
ID = 6 then return the date in the record having the ID 6, else return the
date in the record having an ID of 7. All query records are returning the
date where ID = 6 is first found instead of the date associated with that
record. I don't realy know what I am doing when it comes to an Iif
expression within a query.

myECD:
IIf([milestoneID]=6<>"",DLookUp("[MileDate]","[tblProjectMilestones]",[milestoneID]=7),DLookUp("[miledate]","[tblProjectMilestones]",[milestoneID]=6))
Click to expand...

The condition [milestoneID]=6<>"" is mostlikely nonsense.

Try removing the <>"" or explaining what purpose you
thought it would serve.
Click to expand...
 
This is what I ended up with after removing the erroneous condition:

milestoneID]=6<>"",

and it doesn't produce the desired results.

John Spencer said:
Perhaps what you want is

IIf([milestoneID]=6
,DLookUp("[MileDate]","[tblProjectMilestones]","[milestoneID]=6")
,DLookUp("[miledate]","[tblProjectMilestones]","[milestoneID]=7"))

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
I am trying to do an Iif expression based on the value in my table. If the
ID = 6 then return the date in the record having the ID 6, else return the
date in the record having an ID of 7. All query records are returning the
date where ID = 6 is first found instead of the date associated with that
record. I don't realy know what I am doing when it comes to an Iif
expression within a query.

myECD:
IIf([milestoneID]=6<>"",DLookUp("[MileDate]","[tblProjectMilestones]",[milestoneID]=7),DLookUp("[miledate]","[tblProjectMilestones]",[milestoneID]=6))
Click to expand...
Click to expand...
 
I am trying to do an Iif expression based on the value in my table. If the
ID = 6 then return the date in the record having the ID 6, else return the
date in the record having an ID of 7. All query records are returning the
date where ID = 6 is first found instead of the date associated with that
record. I don't realy know what I am doing when it comes to an Iif
expression within a query.

myECD:
IIf([milestoneID]=6<>"",DLookUp("[MileDate]","[tblProjectMilestones]",[milestoneID]=7),DLookUp("[miledate]","[tblProjectMilestones]",[milestoneID]=6))
Click to expand...

You're making it a lot harder than it needs to be. Your <> expression is
simply out of place and unnecessary!

Try a criterion of

=DLookUp("[MileDate]", "[tblProjectMilestones]", "[MilestoneID] = " &
IIF([MilestoneID]=6, 6, 7))
 
This is what I ended up with after removing the erroneous condition:

milestoneID]=6<>"",

and it doesn't produce the desired results.
Click to expand...

"Doctor, I don't feel good. What should I take?"

Please indicate what results you want, what you are getting, and perhaps a bit
more information about the structure and content of your tables. What's the
significance of the 6 and 7? What do you want to see if the MilestoneID is 31
or 1 or null?
 
OK, here goes...

I am trying to end up with:

ProjectID | ProjectName | Remarks | ECD

Where ECD is actual completion if available, followed by est if available,
lastly a string of "TBD" if all else fails. What I am getting is a record
for ACD and a different record for ECD.

Due to how I am using this, I need one record per project that has a ACD,
ECD or "TBD".

Hope this makes a little more sense.

John W. Vinson said:
This is what I ended up with after removing the erroneous condition:

milestoneID]=6<>"",

and it doesn't produce the desired results.
Click to expand...

"Doctor, I don't feel good. What should I take?"

Please indicate what results you want, what you are getting, and perhaps a bit
more information about the structure and content of your tables. What's the
significance of the 6 and 7? What do you want to see if the MilestoneID is 31
or 1 or null?
Click to expand...
 
Duane, check out my thread at 9/15/2008 11:13 AM PST for a little better
description af what I am trying to do. Thanks.

Duane Hookom said:
Try something like:
MyECD: DLookUp("MileDate","tblProjectMilestones","ID =" & [MileStoneID])

--
Duane Hookom
Microsoft Access MVP


MeSteve said:
I have no idea what MilestoneID =" & [MileStoneID] would do for me, but it
does not return the desired results.

Duane Hookom said:
Try something like:
MyECD: DLookUp("MileDate","tblProjectMilestones","MilestoneID =" &
[MileStoneID])

--
Duane Hookom
Microsoft Access MVP


:

I am trying to do an Iif expression based on the value in my table. If the
ID = 6 then return the date in the record having the ID 6, else return the
date in the record having an ID of 7. All query records are returning the
date where ID = 6 is first found instead of the date associated with that
record. I don't realy know what I am doing when it comes to an Iif
expression within a query.

myECD:
IIf([milestoneID]=6<>"",DLookUp("[MileDate]","[tblProjectMilestones]",[milestoneID]=7),DLookUp("[miledate]","[tblProjectMilestones]",[milestoneID]=6))
Click to expand...
Click to expand...
Click to expand...
 
OK, here goes...

I am trying to end up with:

ProjectID | ProjectName | Remarks | ECD

Where ECD is actual completion if available, followed by est if available,
lastly a string of "TBD" if all else fails. What I am getting is a record
for ACD and a different record for ECD.

Due to how I am using this, I need one record per project that has a ACD,
ECD or "TBD".

Hope this makes a little more sense.
Click to expand...

I'm sorry Steve, but it would make sense if I knew what a "completion" was,
could see the SQL of the query you're using, and knew something about the
structure, datatypes and content of your table; and what a "completion" is.
You can see your application; we cannot!

More details please?
 
Sorry,

ProjectID = autonumber
ProjectName = text
Remarks = text
ECD = date
ACD = date

The completion I referred to is the date that the project is completed. I
don't think the SQL statement will help as its not doing anything close to
what I need. Currently, ProjectID, ProjectName and Remarks are in
tblProjects. ECD and ACD are in a linking table (tblProjectMilestones)
between tblProjects and tblMilestones.

Currently, I can return a single result for each project when using just the
fields on tblProjects. When I try to add the ECD and or ACD, I get one of 2
things, either I have multiple results for some records (the ones that have
both an ECD and ACD in the linking table) or the list is reduced to only the
ones that have an ECD or ACD. I need a single result for each projectID that
returns the ACD if available, an ECD if available and there is no ACD, or
"TBD" if both ar false.

I hope this is enough detail for you. Not really knowing queries its hard
for me to know what you need to see to help out. Thanks.
 
Back
Top