find value from a query

[maceslin]

I have a dynamic qryParametersLast in which I have a field
cboClassification that I must now search through to determine a value
to place in the report header. In descending order the values for
cboClassification are secret, confidential, FOUO and unclass. If
secret is there then the header is secret, if no secret but
confidential then the header is confidential, etc...

I have absolutley no idea as to how to go about doing this. Can
someone point me down the right path?

Thanks
Dave

 

[Duane Hookom]

I thought I read a similar question recently where it was suggested you have
a table that describes the order of the classifications. Do you have one?

 

[KARL DEWEY]

First question - is your machine and network authorized to process
classified? Have your security manager check it out.

Create a translation table listing all classifications that could be in the
field and number representing classification order as Code from highest to
lowest.

Join the translation table to your cboClassification field and add Code in
your query.

In the Header insert a text box and use this as the source ---
=IIf(DMax("Code","YourQuery")=1,"Top_Class",IIf(DMax("Code"," YourQuery
")=2,"MidClass","LowClass"))

Change 'Top_Class' to your maximum classification and the other in order.
Add to the nested IIFs if you have more levels.

Make sure to test with all variations before using with real data.

 

[maceslin]

I thought I read a similar question recently where it was suggested you have
a table that describes the order of the classifications. Do you have one?
--
Duane Hookom
Microsoft Access MVP







- Show quoted text -

I had a post similar but it was a different table. I could order
tblClassification in any order needed in same method (adding another
column to table).

I do all my developmental work on an unclassifiefd network so I can
work issue at hom ein spare time (really the only time I ahve as this
is a collateral duty) and then transfer it tot he correct network when
completed. Not sure I understand KArl suggestion- what goes in
"code" spot?

 

[maceslin]

I had a post similar but it was a different table.  I could order
tblClassification in any order needed in same method (adding another
column to table).

I do all my developmental work on an unclassifiefd network so I can
work issue at hom ein spare time (really the only time I ahve as this
is a collateral duty) and then transfer it tot he correct network when
completed.  Not sure I understand KArl suggestion-  what goes in
"code" spot?- Hide quoted text -

- Show quoted text -

I have tried the following and am getting a parameter box with the
same message in it. If I put an "=" in front of the expression I get
an error "missing operand"

IIF(DMax(cboCommentClass, qryParametersLast)=1,"SECRET NOFORN")

Any idea what is wrong?

 

[KARL DEWEY]

Post your complete SQL statement.

 

[maceslin]

Post your complete SQL statement.
--
KARL DEWEY
Build a little - Test a little








- Show quoted text -

Here is the complete query string. It is a dynamic query so the
"Where" claus can be very complex

SELECT tblComments.DOTLMPF_ChoiceFK, tblComments.solution,
tblSolutionLocation.Solution_Location, tblBasicData.[Lesson IDPK],
tblPersonnel.[Last Name], tblCore.CoreCapability,
tblNumbered.Numbered_Fleet, tblDOTMLPF.DOTMLPF_Choices,
tblStatusChoices.Status, tblComments.Date_Entered,
tblBasicData.HyperlinkToLesson, tblComments.Review_Date,
tblBasicData.DateObserved, tblBasicData.Title,
tblComments.ClassificationFK
FROM tblStatusChoices INNER JOIN (tblPersonnel INNER JOIN (tblNumbered
INNER JOIN (tblDOTMLPF INNER JOIN (tblCore INNER JOIN (tblBasicData
INNER JOIN (tblSolutionLocation RIGHT JOIN tblComments ON
tblSolutionLocation.SollocfieldsPK=tblComments.Solution_LocationFK) ON
tblBasicData.[Lesson IDPK]=tblComments.Lesson_IDFK) ON tblCore.[Core
Capability IDPK]=tblBasicData.CoreCapabilityFK) ON tblDOTMLPF.[DOTMLPF
ID PK]=tblComments.DOTLMPF_ChoiceFK) ON
tblNumbered.NumberFleetPK=tblBasicData.NumberedFleetFK) ON
tblPersonnel.personnelPK=tblComments.Responsible_IndividualFK) ON
tblStatusChoices.StatusChoiceIDPK=tblComments.statusFK
WHERE DOTMLPF_Choices="Doctrine";



Thanks for looking

Dave

 

[KARL DEWEY]

It seems like you did not post the latest SQL as it does not Join the
translation table to your cboClassification field ----

Edit it like this --
SELECT IIF(DMax("cboCommentClass", "qryParametersLast")=1,"SECRET NOFORN")
AS [Max Classification], tblComments.DOTLMPF_ChoiceFK,
tblComments.solution,........

Use [Max Classification] in the header.
--
KARL DEWEY
Build a little - Test a little

Post your complete SQL statement.
--
KARL DEWEY
Build a little - Test a little
















- Show quoted text -

Here is the complete query string. It is a dynamic query so the
"Where" claus can be very complex

SELECT tblComments.DOTLMPF_ChoiceFK, tblComments.solution,
tblSolutionLocation.Solution_Location, tblBasicData.[Lesson IDPK],
tblPersonnel.[Last Name], tblCore.CoreCapability,
tblNumbered.Numbered_Fleet, tblDOTMLPF.DOTMLPF_Choices,
tblStatusChoices.Status, tblComments.Date_Entered,
tblBasicData.HyperlinkToLesson, tblComments.Review_Date,
tblBasicData.DateObserved, tblBasicData.Title,
tblComments.ClassificationFK
FROM tblStatusChoices INNER JOIN (tblPersonnel INNER JOIN (tblNumbered
INNER JOIN (tblDOTMLPF INNER JOIN (tblCore INNER JOIN (tblBasicData
INNER JOIN (tblSolutionLocation RIGHT JOIN tblComments ON
tblSolutionLocation.SollocfieldsPK=tblComments.Solution_LocationFK) ON
tblBasicData.[Lesson IDPK]=tblComments.Lesson_IDFK) ON tblCore.[Core
Capability IDPK]=tblBasicData.CoreCapabilityFK) ON tblDOTMLPF.[DOTMLPF
ID PK]=tblComments.DOTLMPF_ChoiceFK) ON
tblNumbered.NumberFleetPK=tblBasicData.NumberedFleetFK) ON
tblPersonnel.personnelPK=tblComments.Responsible_IndividualFK) ON
tblStatusChoices.StatusChoiceIDPK=tblComments.statusFK
WHERE DOTMLPF_Choices="Doctrine";



Thanks for looking

Dave

 

[maceslin]

It seems like you did not post the latest SQL as it does not Join the
translation table to your cboClassification field ----

Edit it like this --
SELECT IIF(DMax("cboCommentClass", "qryParametersLast")=1,"SECRET NOFORN")
AS [Max Classification], tblComments.DOTLMPF_ChoiceFK,
tblComments.solution,........

Use [Max Classification] in the header.
--
KARL DEWEY
Build a little - Test a little

Here is the complete query string.  It is a dynamic query so the
"Where" claus can be very complex

SELECT tblComments.DOTLMPF_ChoiceFK, tblComments.solution,
tblSolutionLocation.Solution_Location, tblBasicData.[Lesson IDPK],
tblPersonnel.[Last Name], tblCore.CoreCapability,
tblNumbered.Numbered_Fleet, tblDOTMLPF.DOTMLPF_Choices,
tblStatusChoices.Status, tblComments.Date_Entered,
tblBasicData.HyperlinkToLesson, tblComments.Review_Date,
tblBasicData.DateObserved, tblBasicData.Title,
tblComments.ClassificationFK
FROM tblStatusChoices INNER JOIN (tblPersonnel INNER JOIN (tblNumbered
INNER JOIN (tblDOTMLPF INNER JOIN (tblCore INNER JOIN (tblBasicData
INNER JOIN (tblSolutionLocation RIGHT JOIN tblComments ON
tblSolutionLocation.SollocfieldsPK=tblComments.Solution_LocationFK) ON
tblBasicData.[Lesson IDPK]=tblComments.Lesson_IDFK) ON tblCore.[Core
Capability IDPK]=tblBasicData.CoreCapabilityFK) ON tblDOTMLPF.[DOTMLPF
ID PK]=tblComments.DOTLMPF_ChoiceFK) ON
tblNumbered.NumberFleetPK=tblBasicData.NumberedFleetFK) ON
tblPersonnel.personnelPK=tblComments.Responsible_IndividualFK) ON
tblStatusChoices.StatusChoiceIDPK=tblComments.statusFK
WHERE DOTMLPF_Choices="Doctrine";

Thanks for looking

Dave- Hide quoted text -

- Show quoted text -

Karl
Not sure I understand your last suggestion. Where does the IIF code
go- in the control source for text box in the header? And what is
reference to {Max Classification]? This code also seems awfully long
if I need to do it for each of my 5 possibilities

Went back to SQL and yes- the code did not save, I think I have it now
on the SQL for qryParametersLast

SELECT tblComments.DOTLMPF_ChoiceFK, tblComments.solution,
tblSolutionLocation.Solution_Location, tblBasicData.[Lesson IDPK],
tblPersonnel.[Last Name], tblCore.CoreCapability,
tblNumbered.Numbered_Fleet, tblDOTMLPF.DOTMLPF_Choices,
tblStatusChoices.Status, tblComments.Date_Entered,
tblBasicData.HyperlinkToLesson, tblComments.Review_Date,
tblBasicData.DateObserved, tblBasicData.Title, tblClass.Classification
FROM tblClass INNER JOIN (tblStatusChoices INNER JOIN (tblPersonnel
INNER JOIN (tblNumbered INNER JOIN (tblDOTMLPF INNER JOIN (tblCore
INNER JOIN (tblBasicData INNER JOIN (tblSolutionLocation RIGHT JOIN
tblComments ON
tblSolutionLocation.SollocfieldsPK=tblComments.Solution_LocationFK) ON
tblBasicData.[Lesson IDPK]=tblComments.Lesson_IDFK) ON tblCore.[Core
Capability IDPK]=tblBasicData.CoreCapabilityFK) ON tblDOTMLPF.[DOTMLPF
ID PK]=tblComments.DOTLMPF_ChoiceFK) ON
tblNumbered.NumberFleetPK=tblBasicData.NumberedFleetFK) ON
tblPersonnel.personnelPK=tblComments.Responsible_IndividualFK) ON
tblStatusChoices.StatusChoiceIDPK=tblComments.statusFK) ON tblClass.
[ClassID PK]=tblComments.ClassificationFK


I am then using the code
=IIF(Dmax(cboCommentClass, qryParametersLast)=1, SECRET NOFORN)
in the text box and am getting invalid syntax missing operator or
operand

Following your build a little, test a little- I like to test a lot
and as I am jus tlearning I really neeed to do a lot of testing so I
understand what is going on

Thanks for continued support
Dave