Module for Showing Error Message in Query

[Tom]

Currently, in 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

 

[Jen]

Hi Tom,

You could write a function that passes in four arguments
(the fields), something like this

function showErrorMessage(milStatus as variant, rank as _
variant, billet as variant, rankConv as variant) as string

' do your logic here, but instead of using the iif, you
' would use if..then...else construct and refer to the
' fields you're passing in.

end function

In the query you would write your expression like this:
ERRORMSG: showErrorMessage(AllData.MILCIV,
Manpower.RANK, Alldata.BILLET, AllData.MP_RANK_CONV)

Hope this helps,
Jen

 

[Tom]

Jen, thanks for your pointers! I did help!

Tom

-----Original Message-----
Hi Tom,

You could write a function that passes in four arguments
(the fields), something like this

function showErrorMessage(milStatus as variant, rank as _
variant, billet as variant, rankConv as variant) as string

' do your logic here, but instead of using the iif, you
' would use if..then...else construct and refer to the
' fields you're passing in.

end function

In the query you would write your expression like this:
ERRORMSG: showErrorMessage(AllData.MILCIV,
Manpower.RANK, Alldata.BILLET, AllData.MP_RANK_CONV)

Hope this helps,
Jen

-----Original Message-----
Currently, in 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
.

.