text must be seen as code

[HENDRY_del_FUEGO via AccessMonster.com]

Hi all,

I am struggling with the following:

I have made a table where a define Case / Description Case / SQL-code
The sql-code is the correct code (with

!... etc.) which need to be
checked if a record belongs to case 1 , case 2 , etc.

I want the code for these codes all togheter in a table to make it easier for
my successor to maintain it.

Now the problem:
In a query I make the following check:
Case 6: IIf([Overview cases]![SQL-code],"OK","NOK")

This does not work because access sees the test as TEXT, how can I make it
clear to access that this [Overview cases]![SQL-code] is sql code?

thanks in advance

 

[tina]

AFAIK, you can't do it directly. you'd have to write a custom function, and
use it to open a QueryDef using the SQL statement taken from the table.
beyond that, your IIf() function looks incomplete to me. the first argument
should be an expression that evaluates to True or False, usually "x = y". so
unless the query returns a single field in a single record, with a value of
either True or False, i don't see how it's going to work as the argument for
the IIf() function.

hth

 

[HENDRY_del_FUEGO via AccessMonster.com]

Hi tina,

thx for your reply

the 'overview cases' tabel looks like following

case 1 / Manufacturing plant / [BASIS table]![ReplenType] =2 AND [BASIS
table]![Item_UDC_SOURCE_CD] <>"X"

case 2 etc...

so Case 6: IIf([Overview cases]![SQL-code],"OK","NOK")

is in fact the same as iif([BASIS table]![ReplenType] =2 AND [BASIS table]!
[Item_UDC_SOURCE_CD] <>"X", OK, NOK)

I want to avoid very long iff statements and increase facility for
maintaining it, tha tis why I want to group al the cases in one table.

but i don't think I understand your proposed solution yet...

AFAIK, you can't do it directly. you'd have to write a custom function, and
use it to open a QueryDef using the SQL statement taken from the table.
beyond that, your IIf() function looks incomplete to me. the first argument
should be an expression that evaluates to True or False, usually "x = y". so
unless the query returns a single field in a single record, with a value of
either True or False, i don't see how it's going to work as the argument for
the IIf() function.

hth

Hi all,

[quoted text clipped - 15 lines]

thanks in advance

 

[tina]

okay, i get what you're doing now. but you can only refer to fields
ReplenType and Item_UDC_SOURCE_CD if table BASIS table is included in the
query. otherwise, you'd have to use a DLookup in the IIf() function to pull
in that text as the first argument in the IIf() function. and even then, i'm
not sure it will work because the DLookup will return a string, and the
IIf() function may not evaluate it as an expression. you might try enclosing
the DLookup() in an Eval() function.

hth

Hi tina,

thx for your reply

the 'overview cases' tabel looks like following

case 1 / Manufacturing plant / [BASIS table]![ReplenType] =2 AND [BASIS
table]![Item_UDC_SOURCE_CD] <>"X"

case 2 etc...

so Case 6: IIf([Overview cases]![SQL-code],"OK","NOK")

is in fact the same as iif([BASIS table]![ReplenType] =2 AND [BASIS table]!
[Item_UDC_SOURCE_CD] <>"X", OK, NOK)

I want to avoid very long iff statements and increase facility for
maintaining it, tha tis why I want to group al the cases in one table.

but i don't think I understand your proposed solution yet...

AFAIK, you can't do it directly. you'd have to write a custom function, and
use it to open a QueryDef using the SQL statement taken from the table.
beyond that, your IIf() function looks incomplete to me. the first argument
should be an expression that evaluates to True or False, usually "x = y". so
unless the query returns a single field in a single record, with a value of
either True or False, i don't see how it's going to work as the argument for
the IIf() function.

hth

Hi all,

[quoted text clipped - 15 lines]

thanks in advance

 

[HENDRY_del_FUEGO via AccessMonster.com]

Hi Tina,

Sorry formy late reply but I had to had in some deliverables (damned
deadlines)

The table Basis is included in the query, so I didn't applied the method with
the DLookup but jsut used the function Eval.

Expr1: IIf(Eval([Overview cases]![SQL-code])=[test],"true","false") if SQL
code = 2+2 and [test]=4 than this works

however when make SQL-code a real piece of code like [Basic data]!
ReplenType=1 And [Basic data]![KENNY-ITEM_UDC_SOURCE_CD]="X" And ([Basic data]
!Loc<>"WF96" And [Basic data]!Loc<>"VX26" And [Basic data]!Loc<>"WK97")

than this doesn't work....

do you see another solution for this problem

Many thanks already

okay, i get what you're doing now. but you can only refer to fields
ReplenType and Item_UDC_SOURCE_CD if table BASIS table is included in the
query. otherwise, you'd have to use a DLookup in the IIf() function to pull
in that text as the first argument in the IIf() function. and even then, i'm
not sure it will work because the DLookup will return a string, and the
IIf() function may not evaluate it as an expression. you might try enclosing
the DLookup() in an Eval() function.

hth

Hi tina,

[quoted text clipped - 32 lines]

 

[tina]

if the name of the table is BASIS, then why are you referring to the table
as [Basic data]? and if those fields are in table BASIS, and table BASIS is
included in the query, then why refer to a table name at all? did you try

ReplenType=1 And [KENNY-ITEM_UDC_SOURCE_CD]="X" And Loc<>"WF96" And
Loc<>"VX26" And Loc<>"WK97"

if you can't get it to work, you could try writing it in VBA using a
QueryDef, since you can use a text string to set the SQL property. but
again, remember that the fields in the test expression have to be included
in the source table(s)/query(s) of the SQL statement, as we discussed
before.

hth

Hi Tina,

Sorry formy late reply but I had to had in some deliverables (damned
deadlines)

The table Basis is included in the query, so I didn't applied the method with
the DLookup but jsut used the function Eval.

Expr1: IIf(Eval([Overview cases]![SQL-code])=[test],"true","false") if SQL
code = 2+2 and [test]=4 than this works

however when make SQL-code a real piece of code like [Basic data]!
ReplenType=1 And [Basic data]![KENNY-ITEM_UDC_SOURCE_CD]="X" And ([Basic data]
!Loc<>"WF96" And [Basic data]!Loc<>"VX26" And [Basic data]!Loc<>"WK97")

than this doesn't work....

do you see another solution for this problem

Many thanks already

okay, i get what you're doing now. but you can only refer to fields
ReplenType and Item_UDC_SOURCE_CD if table BASIS table is included in the
query. otherwise, you'd have to use a DLookup in the IIf() function to pull
in that text as the first argument in the IIf() function. and even then, i'm
not sure it will work because the DLookup will return a string, and the
IIf() function may not evaluate it as an expression. you might try enclosing
the DLookup() in an Eval() function.

hth

Hi tina,

[quoted text clipped - 32 lines]

thanks in advance

 

[HENDRY_del_FUEGO via AccessMonster.com]

I kept working onthe problem and I changed the name of the table, sorry for
the confusion. Further I always refer to the table becasue I am working with
many tables which all have the same fields otherwise I loose my overview....

I will try to do it in VBA, can you give my somewhat direction or a good
example somewhere on the internet because I didn't hear about VBA before my
quest to this solution....

if the name of the table is BASIS, then why are you referring to the table
as [Basic data]? and if those fields are in table BASIS, and table BASIS is
included in the query, then why refer to a table name at all? did you try

ReplenType=1 And [KENNY-ITEM_UDC_SOURCE_CD]="X" And Loc<>"WF96" And
Loc<>"VX26" And Loc<>"WK97"

if you can't get it to work, you could try writing it in VBA using a
QueryDef, since you can use a text string to set the SQL property. but
again, remember that the fields in the test expression have to be included
in the source table(s)/query(s) of the SQL statement, as we discussed
before.

hth

Hi Tina,

[quoted text clipped - 32 lines]

 

[tina]

well, working with QueryDefs is not how i would introduce somebody to VBA.
if you really want to go that route, suggest you take a look at the links at
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#Books. you'll
want to learn some basic VBA, i think, before jumping in.

hth

I kept working onthe problem and I changed the name of the table, sorry for
the confusion. Further I always refer to the table becasue I am working with
many tables which all have the same fields otherwise I loose my overview....

I will try to do it in VBA, can you give my somewhat direction or a good
example somewhere on the internet because I didn't hear about VBA before my
quest to this solution....

if the name of the table is BASIS, then why are you referring to the table
as [Basic data]? and if those fields are in table BASIS, and table BASIS is
included in the query, then why refer to a table name at all? did you try

ReplenType=1 And [KENNY-ITEM_UDC_SOURCE_CD]="X" And Loc<>"WF96" And
Loc<>"VX26" And Loc<>"WK97"

if you can't get it to work, you could try writing it in VBA using a
QueryDef, since you can use a text string to set the SQL property. but
again, remember that the fields in the test expression have to be included
in the source table(s)/query(s) of the SQL statement, as we discussed
before.

hth

Hi Tina,

[quoted text clipped - 32 lines]

thanks in advance