ErrorMessage - Field Limitation

[Tom]

In the query design view, I use a field for storing a data
validation messages (IIFs) which has grown very lengthy
now.

At this time, I get a prompt which indicates that the
message is too long; hence, Access won't accept any longer.

This is the message (currently working but it does not
contain all statements which I need):

*************************

ERRORMSG: (
IIf([AllData.MILCIV] = "C" And [AllData.BILLET]<>"SS" And
[AllData.RANK] Like "A*" And [Manpower.RANK] Like "B*", "B-
Level Civ linked to A-Level billet",
IIf([AllData.MILCIV] = "C" And [AllData.BILLET]<>"SS" And
[AllData.RANK] Like "A*" And [Manpower.RANK]
Like "OF*", "Officer linked to A-Level billet",
IIf([AllData.MILCIV] = "C" And [AllData.BILLET]="SS" And
[AllData.RANK] Like "B*" And [Manpower.RANK] Like "A*", "A-
Level Civ linked to B-Level billet",
IIf([AllData.RANK] = [MP_RANK_CONV], "ok",
IIf([MP_RANK_CONV] = "Missing Data", "Analyze Manpower
table",
IIf([MP_RANK_CONV] = "Vacant Position", "To be determined",
IIf([AllData.MILCIV] = "M" And [AllData.BILLET]<>"SS" And
[AllData.RANK] Like "OF*" And [AllData.RANK] <>
[MP_RANK_CONV], "Rank Mismatch: Officer",
IIf([AllData.MILCIV] = "C" And [AllData.BILLET]<>"SS" And
[AllData.RANK] Like "A*" And [AllData.RANK] <>
[MP_RANK_CONV], "Rank Mismatch: A-Level Civ"
)))))))))

*************************

Here are my question now:
- Can I utilize a module to store the error message?
- If yes, can you please provide me some pointers as to
how the function should be written?

For instance, on the example of 2 statements...

1:
IIf([AllData.MILCIV] = "C" And [AllData.BILLET]<>"SS" And
[AllData.RANK] Like "A*" And [AllData.RANK] <>
[MP_RANK_CONV], "Rank Mismatch: A-Level Civ")

and 2:
IIf([AllData.MILCIV] = "M" And [AllData.BILLET]<>"SS" And
[AllData.RANK] Like "OF*" And [AllData.RANK] <>
[MP_RANK_CONV], "Rank Mismatch: Officer")


Thanks! Any help is appreciated!

Tom

 

[Tom]

Thanks, Guido... I appreciate your feedback... I can work
w/ this!

Tom

-----Original Message-----
I beleive a function is the way to go. I wrote a function
called "ErrorMsg()" that takes 4 parameters. You may need
more depending on how many different criteria you evaluate.

Basically the function takes in the parameters and then
you can run If statements on them. If the If statement
returns true you assign the correct wording to the
variable "stMsg" and then send the processing of the
function to "GetOut".

After GetOut the correct stMsg is asigned to ErrorMsg and
is returned.

---------------------------------------------------------
Function ErrorMsg(Year, Month, Testing, Date1)

Dim stMsg As String

If Year = 2001 And Month = 1 And Testing = "100" And Date1
= Null Then
stMsg = "Error Message 1"
GoTo GetOut
End If

If Year = 2002 And Month = 1 And Testing = "1020-55" And
Date1 = Null Then
stMsg = "Error Message 2"
GoTo GetOut
End If

stMsg = "All other errors"

GetOut:

ErrorMsg = stMsg

End Function

----------------------------------------------------

In your query you need to have:

Error: ErrorMsg([Year],[Month],[Testing],[Date])

In the field of the query grid.

I used field names from my own database so obvously
everything will have to be changed around for your
purposes but hopefully this will give you a good start.

-----Original Message-----
In the query design view, I use a field for storing a data
validation messages (IIFs) which has grown very lengthy
now.

At this time, I get a prompt which indicates that the
message is too long; hence, Access won't accept any longer.

This is the message (currently working but it does not
contain all statements which I need):

*************************

ERRORMSG: (
IIf([AllData.MILCIV] = "C" And [AllData.BILLET]<>"SS" And
[AllData.RANK] Like "A*" And [Manpower.RANK] Like "B*", "B-
Level Civ linked to A-Level billet",
IIf([AllData.MILCIV] = "C" And [AllData.BILLET]<>"SS" And
[AllData.RANK] Like "A*" And [Manpower.RANK]
Like "OF*", "Officer linked to A-Level billet",
IIf([AllData.MILCIV] = "C" And [AllData.BILLET]="SS" And
[AllData.RANK] Like "B*" And [Manpower.RANK] Like "A*", "A-
Level Civ linked to B-Level billet",
IIf([AllData.RANK] = [MP_RANK_CONV], "ok",
IIf([MP_RANK_CONV] = "Missing Data", "Analyze Manpower
table",
IIf([MP_RANK_CONV] = "Vacant Position", "To be determined",
IIf([AllData.MILCIV] = "M" And [AllData.BILLET]<>"SS" And
[AllData.RANK] Like "OF*" And [AllData.RANK] <>
[MP_RANK_CONV], "Rank Mismatch: Officer",
IIf([AllData.MILCIV] = "C" And [AllData.BILLET]<>"SS" And
[AllData.RANK] Like "A*" And [AllData.RANK] <>
[MP_RANK_CONV], "Rank Mismatch: A-Level Civ"
)))))))))

*************************

Here are my question now:
- Can I utilize a module to store the error message?
- If yes, can you please provide me some pointers as to
how the function should be written?

For instance, on the example of 2 statements...

1:
IIf([AllData.MILCIV] = "C" And [AllData.BILLET]<>"SS" And
[AllData.RANK] Like "A*" And [AllData.RANK] <>
[MP_RANK_CONV], "Rank Mismatch: A-Level Civ")

and 2:
IIf([AllData.MILCIV] = "M" And [AllData.BILLET]<>"SS" And
[AllData.RANK] Like "OF*" And [AllData.RANK] <>
[MP_RANK_CONV], "Rank Mismatch: Officer")


Thanks! Any help is appreciated!

Tom
.

.

 

[Tom]

John:

Thanks for your reply... no, I did not ignore the
previous suggestions. I just got a lots of things going
on right now w/ the developmen of the db.

I look into your recommendation! Again, thanks for your
feedback.

Tom

-----Original Message-----

In the query design view, I use a field for storing a data
validation messages (IIFs) which has grown very lengthy
now.

You've received at least two suggestions that you make this a
table-driven process. Have you intentionally rejected those solutions?
They will be MUCH MUCH EASIER to maintain than massive IIF's and code.

Here are my question now:
- Can I utilize a module to store the error message?

Certainly. I'd create a VBA function such as

Public Function ValidRank(MilCiv AS String, Billet As String, Rank As
String, MP_Rank_Conv AS String) As String

- If yes, can you please provide me some pointers as to
how the function should be written?

For instance, on the example of 2 statements...

1:
IIf([AllData.MILCIV] = "C" And [AllData.BILLET]<>"SS" And
[AllData.RANK] Like "A*" And [AllData.RANK] <>
[MP_RANK_CONV], "Rank Mismatch: A-Level Civ")

and 2:
IIf([AllData.MILCIV] = "M" And [AllData.BILLET]<>"SS" And
[AllData.RANK] Like "OF*" And [AllData.RANK] <>
[MP_RANK_CONV], "Rank Mismatch: Officer")

You could do this several ways; the closest to your current logic
would use

If MilCiv = "C" And Billet <> "SS" AND Left(Rank, 1) = "A" _
And Rank <> MP_Rank_Conv Then
ValidRank = "Rank Mismatch: A-Level Civ"
Elseif MilCiv = "M" And Billet <> "SS" And Left(Rank, 2) = "OF" _
And Rank <> MP_Rank_Conv Then
ValidRank = "Rank Mismatch: Officer"
Elseif
...

If there are "blocks" of IF's, for instance if all of the results will
be different depending on the comparison of Rank with MP_Rank_Conv,
then you can use that condition in the outermost of a nested set of
IF's.

HOWEVER...!!!!!

THERE IS AN EASIER WAY. There really, really is. You do NOT need any
VBA code at all unless you insist!

If you have a Table containing all of the valid combinations of
MilCiv, Billet, and Rank you can join this table to your table by the
three fields. Any invalid entry will cause an error (which you can
trap, and give the user a meaningful message) when the user attempts
to save the record. You're using a relational database - it will work
very nicely if you use it as a relational database, rather than
reinventing the wheel!



.