If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
used sql stored in field
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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200804/1 |
#2
|
|||
|
|||
used sql stored in field
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" u38916@uwe wrote in message news:8253174bdf241@uwe... 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200804/1 |
#3
|
|||
|
|||
used sql stored in field
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 Jeff Boyce wrote: 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200804/1 |
#4
|
|||
|
|||
used sql stored in field
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 "HENDRY_del_FUEGO via AccessMonster.com" u38916@uwe wrote in message news:825b6f153dd19@uwe... 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 Jeff Boyce wrote: 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200804/1 |
#5
|
|||
|
|||
used sql stored in field
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.... Jeff Boyce wrote: 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200804/1 |
#6
|
|||
|
|||
used sql stored in field
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) HENDRY_del_FUEGO wrote: 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] thx Hendry -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200804/1 |
#7
|
|||
|
|||
used sql stored in field
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 "HENDRY_del_FUEGO via AccessMonster.com" u38916@uwe wrote in message news:8268b272c9fa0@uwe... 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.... Jeff Boyce wrote: 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200804/1 |
#8
|
|||
|
|||
used sql stored in field
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 Jeff Boyce wrote: 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] thx Hendry -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200804/1 |
Thread Tools | |
Display Modes | |
|
|