used sql stored in field

[HENDRY_del_FUEGO via AccessMonster.com]

I am struggling with the following problem....

I Have saved following string in a field :" "EUALL" + [SKU]![loc] "

I want to use this field in my query design, for example in an if-statement.
How can I get Access to understand that this is SQL-code instead of a string?

I tried to use Eval() but this doesn't seem to work

thx
Hendry

 

[Jeff Boyce]

If you "quote" it, it's a string.

Perhaps you could leave off the quotes around the entire expression and just
keep the quotes around "EUALL".

Also, you might want to try something like (untested):
YourNewField: "EUALL" + ...
in your query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[HENDRY_del_FUEGO via AccessMonster.com]

the quotes around "EUALL" + []![loc] was just to indicate that access sees
this what is stored in the field as a string

maybe I explained my problem wrong, here is an new attempt;

table 1:
LocType AllocationCalendar
eg PLT "EUALL" + [Loc]
CDC (blank field)


table 2:
Loc LocType AllocationCalendar
eg JXY(Taiwan) PLT EUALLJXY
PDF (KOREA) CDC (blank field)

In my query i link table 1 with 2 . In this query I test wheter for every PLT
[table 1]![allocationCalendar] = [table 2]![Allocationcalendar]. If this is
not the case I know my data is inconsistent. (the same counts for locType =
CDC)

I hope I now described the problem in a better way

If you "quote" it, it's a string.

Perhaps you could leave off the quotes around the entire expression and just
keep the quotes around "EUALL".

Also, you might want to try something like (untested):
YourNewField: "EUALL" + ...
in your query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am struggling with the following problem....

[quoted text clipped - 9 lines]

thx
Hendry

 

[Jeff Boyce]

Sorry, I understand less than I did before!

Perhaps you could use a pair of queries to generate the codes, then use a
third query to combine them.

You've described a "how" ... as in how you are trying to do something.

If you'll describe a bit more of the "what" ... what you want to do, not how
.... the newsgroup readers may be able to offer alternate approaches.

Regards

Jeff Boyce
Microsoft Office/Access MVP

the quotes around "EUALL" + []![loc] was just to indicate that access sees
this what is stored in the field as a string

maybe I explained my problem wrong, here is an new attempt;

table 1:
LocType AllocationCalendar
eg PLT "EUALL" + [Loc]
CDC (blank field)


table 2:
Loc LocType AllocationCalendar
eg JXY(Taiwan) PLT EUALLJXY
PDF (KOREA) CDC (blank field)

In my query i link table 1 with 2 . In this query I test wheter for every
PLT
[table 1]![allocationCalendar] = [table 2]![Allocationcalendar]. If this
is
not the case I know my data is inconsistent. (the same counts for locType
=
CDC)

I hope I now described the problem in a better way

If you "quote" it, it's a string.

Perhaps you could leave off the quotes around the entire expression and
just
keep the quotes around "EUALL".

Also, you might want to try something like (untested):
YourNewField: "EUALL" + ...
in your query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am struggling with the following problem....

[quoted text clipped - 9 lines]

thx
Hendry

 

[HENDRY_del_FUEGO via AccessMonster.com]

Okay... I see, I will try to describe what the purpose is of this excercice.

I am trying to write a tool to identify the inconsistencies in some ERP-
software. In an attempt to make the tool easy to maintain and grasp by others
I want to create a rule-based tool. So I don't want to put the identification
in the query itself but in different table which will represent the rules.

In table 1 I define whether a SKU (stock-keeping Unit , supply chain object)
is from a Plant, central Warehouse or a local Warehouse.. I do this by
looking at a combination of 4 fields: Location, source code, distribution
source code and replenType. Each combination I validate manually and check
whether it is a Plant, central wh or a local one. (these four fields are
present in table SKUGENERAL)

Some the fields in the ERP-software are dependent on LocType, others are just
default.

Therefore I created 2 tables: table 2 where I define the locType -dependent
fields and table 3 for the locType independent fields.

Table 2

LocType Priority Allocation Calendat ..... ....... (table
header)
Plant 1 .....
(record 1)
Centralwh 2 ......
(record 2)
local wh 3 ...
(record 3)

As you see I only need three records. However, now comes the problem, for
example field 'Allocation Calendar is also dependent on Location.

This field needs to be filled in in the following way: "EUALL" + Loc of the
SKU
I, however, don't want to add Location in this table because then it would
become too large due to the many possibilites (100 locations with three
possible types....).

Therefore I was thinking by just putting as value "EUALL" + [SKUGENERAL]![Loc]
.. Loc is stored in this the SKUGENERAL table. In this way it is clear how
this field needs to be populated for a plant for everyone.

Next I want to use that field in combination with SKUGENERAL table and table
1 to define for every plant its Allocation callendar based on the rule in
table2.

Question: How can I Use the code stored as a value (string) in the table 2 to
do this? So without retyping the formula in the query what would make
maintenance impossible...

I hope this makes any sense....

Sorry, I understand less than I did before!

Perhaps you could use a pair of queries to generate the codes, then use a
third query to combine them.

You've described a "how" ... as in how you are trying to do something.

If you'll describe a bit more of the "what" ... what you want to do, not how
... the newsgroup readers may be able to offer alternate approaches.

Regards

Jeff Boyce
Microsoft Office/Access MVP

the quotes around "EUALL" + []![loc] was just to indicate that access sees
this what is stored in the field as a string

[quoted text clipped - 41 lines]

 

[HENDRY_del_FUEGO via AccessMonster.com]

here is table 2 again because it has changed in my original post


LocType Priority Allocation Calendat ..... (tableheader)
Plant 1 ..... (record 1)

Centralwh 2 ...... (record 2)
local wh 3 ... (record 3)

Okay... I see, I will try to describe what the purpose is of this excercice.

I am trying to write a tool to identify the inconsistencies in some ERP-
software. In an attempt to make the tool easy to maintain and grasp by others
I want to create a rule-based tool. So I don't want to put the identification
in the query itself but in different table which will represent the rules.

In table 1 I define whether a SKU (stock-keeping Unit , supply chain object)
is from a Plant, central Warehouse or a local Warehouse.. I do this by
looking at a combination of 4 fields: Location, source code, distribution
source code and replenType. Each combination I validate manually and check
whether it is a Plant, central wh or a local one. (these four fields are
present in table SKUGENERAL)

Some the fields in the ERP-software are dependent on LocType, others are just
default.

Therefore I created 2 tables: table 2 where I define the locType -dependent
fields and table 3 for the locType independent fields.

Table 2

As you see I only need three records. However, now comes the problem, for
example field 'Allocation Calendar is also dependent on Location.

This field needs to be filled in in the following way: "EUALL" + Loc of the
SKU
I, however, don't want to add Location in this table because then it would
become too large due to the many possibilites (100 locations with three
possible types....).

Therefore I was thinking by just putting as value "EUALL" + [SKUGENERAL]![Loc]
. Loc is stored in this the SKUGENERAL table. In this way it is clear how
this field needs to be populated for a plant for everyone.

Next I want to use that field in combination with SKUGENERAL table and table
1 to define for every plant its Allocation callendar based on the rule in
table2.

Question: How can I Use the code stored as a value (string) in the table 2 to
do this? So without retyping the formula in the query what would make
maintenance impossible...

I hope this makes any sense....

Sorry, I understand less than I did before!

[quoted text clipped - 16 lines]

 

[Jeff Boyce]

If I'm understanding, you want to USE the combination for other processing,
comparison, etc.

You are not required to STORE the combination in order to be able to use it.

Store your base data in your tables. Use queries to join/concatenate
information such as (I think) you are describing.

And you can use queries based on other queries if you need to compare.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Okay... I see, I will try to describe what the purpose is of this
excercice.

I am trying to write a tool to identify the inconsistencies in some ERP-
software. In an attempt to make the tool easy to maintain and grasp by
others
I want to create a rule-based tool. So I don't want to put the
identification
in the query itself but in different table which will represent the
rules.

In table 1 I define whether a SKU (stock-keeping Unit , supply chain
object)
is from a Plant, central Warehouse or a local Warehouse.. I do this by
looking at a combination of 4 fields: Location, source code, distribution
source code and replenType. Each combination I validate manually and check
whether it is a Plant, central wh or a local one. (these four fields are
present in table SKUGENERAL)

Some the fields in the ERP-software are dependent on LocType, others are
just
default.

Therefore I created 2 tables: table 2 where I define the
locType -dependent
fields and table 3 for the locType independent fields.

Table 2

LocType Priority Allocation Calendat ..... ....... (table
header)
Plant 1 .....
(record 1)
Centralwh 2 ......
(record 2)
local wh 3 ...
(record 3)

As you see I only need three records. However, now comes the problem, for
example field 'Allocation Calendar is also dependent on Location.

This field needs to be filled in in the following way: "EUALL" + Loc of
the
SKU
I, however, don't want to add Location in this table because then it would
become too large due to the many possibilites (100 locations with three
possible types....).

Therefore I was thinking by just putting as value "EUALL" +
[SKUGENERAL]![Loc]
Loc is stored in this the SKUGENERAL table. In this way it is clear how
this field needs to be populated for a plant for everyone.

Next I want to use that field in combination with SKUGENERAL table and
table
1 to define for every plant its Allocation callendar based on the rule in
table2.

Question: How can I Use the code stored as a value (string) in the table 2
to
do this? So without retyping the formula in the query what would make
maintenance impossible...

I hope this makes any sense....

Sorry, I understand less than I did before!

Perhaps you could use a pair of queries to generate the codes, then use a
third query to combine them.

You've described a "how" ... as in how you are trying to do something.

If you'll describe a bit more of the "what" ... what you want to do, not
how
... the newsgroup readers may be able to offer alternate approaches.

Regards

Jeff Boyce
Microsoft Office/Access MVP

the quotes around "EUALL" + []![loc] was just to indicate that access
sees
this what is stored in the field as a string

[quoted text clipped - 41 lines]

thx
Hendry

 

[HENDRY_del_FUEGO via AccessMonster.com]

I know I am not required to store the combination but I want to do this....

If I use queries a part of the logic is 'hidden' in a query. My goal is to
put everything in a table.

I want to store the combination because when the combination needs to change
I just need to change a table instead of a query. Because this table
represent the general rules this is in my opinion more logic than changing a
query. Especially when somebody else doesn' know all the queries.

Can I execute my idea? And how?

greatly appreciate your input

If I'm understanding, you want to USE the combination for other processing,
comparison, etc.

You are not required to STORE the combination in order to be able to use it.

Store your base data in your tables. Use queries to join/concatenate
information such as (I think) you are describing.

And you can use queries based on other queries if you need to compare.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Okay... I see, I will try to describe what the purpose is of this
excercice.

[quoted text clipped - 83 lines]