find value from a query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

maceslin@gmail.com - 10 Mar 2008 02:49 GMT

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

Reply to this Message

Duane Hookom - 10 Mar 2008 14:44 GMT

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?

Signature

Duane Hookom
Microsoft Access MVP

> I have a dynamic qryParametersLast in which I have a field
> cboClassification that I must now search through to determine a value
[quoted text clipped - 8 lines]
> Thanks
> Dave

Reply to this Message

KARL DEWEY - 10 Mar 2008 19:11 GMT

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.

Signature

KARL DEWEY
Build a little - Test a little

> 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?
[quoted text clipped - 11 lines]
> > Thanks
> > Dave

Reply to this Message

maceslin@gmail.com - 10 Mar 2008 23:03 GMT

On Mar 10, 9:44 am, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
wrote:

> 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?
[quoted text clipped - 17 lines]
>
> - 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?

Reply to this Message

maceslin@gmail.com - 11 Mar 2008 03:35 GMT

On Mar 10, 6:03 pm, maces...@gmail.com wrote:

> On Mar 10, 9:44 am, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
> wrote:
[quoted text clipped - 32 lines]
>
> - 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?

Reply to this Message

KARL DEWEY - 11 Mar 2008 17:19 GMT

Post your complete SQL statement.

Signature

KARL DEWEY
Build a little - Test a little

> On Mar 10, 6:03 pm, maces...@gmail.com wrote:
> > On Mar 10, 9:44 am, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
[quoted text clipped - 41 lines]
>
> Any idea what is wrong?

Reply to this Message

maceslin@gmail.com - 11 Mar 2008 21:31 GMT

On Mar 11, 12:19 pm, KARL DEWEY <KARLDE...@discussions.microsoft.com>
wrote:

> Post your complete SQL statement.
> --
[quoted text clipped - 49 lines]
>
> - 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

Reply to this Message

KARL DEWEY - 12 Mar 2008 00:12 GMT

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.

Signature

KARL DEWEY
Build a little - Test a little

> On Mar 11, 12:19 pm, KARL DEWEY <KARLDE...@discussions.microsoft.com>
> wrote:
[quoted text clipped - 78 lines]
>
> Dave

Reply to this Message

maceslin@gmail.com - 12 Mar 2008 01:22 GMT

On Mar 11, 7:12 pm, KARL DEWEY <KARLDE...@discussions.microsoft.com>
wrote:

> It seems like you did not post the latest SQL as it does not Join the
> translation table to your cboClassification field ----
[quoted text clipped - 94 lines]
>
> - 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

Reply to this Message