getsavedquery

[HENDRY_del_FUEGO via AccessMonster.com]

Hi,

I have a table 'OverviewInconsistency' with the following columns PrimaryKey
(number), Code (text), Description (text) and SQLtext (text).

eg. 1 // 1__A0 // find the inactive items //
SELECT iif([Basic data checked + default data]![ActiveSwitch]= "YES", "ok",
"non") FROM [Basic data checked + default data];

I use the following function

Public Function GetSavedQuerySQL(Code As String) As String

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb()
Set rst = db.OpenRecordset("OverviewInconsistency")

rst.Index = "PrimaryKey"
rst.Seek "=", Code

If Not rst.NoMatch Then
GetSavedQuerySQL = rst!SQLText
End If

rst.Close
Set rst = Nothing
Set db = Nothing
End Function

I used this function as GetSavedQuerySQL(1__A0)

i get the following error "runtime error 3421 data type conversion error"

if I debug it marks rst.Seek "=", Code

I cant find where I am screwing things up or maybe is my function wrong?

 

[Klatuu]

To retrieve the SQL of a stored query, you have to use the QueryDef object:

strSQL = CurrentDb.QueryDefs("QueryName").SQL

 

[HENDRY_del_FUEGO via AccessMonster.com]

Is this what you mean?

Public Function GetSavedQuerySQL(Code As String) As String

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb()
Set rst = db.OpenRecordset("OverviewInconsistency")

GetSavedQuerySQL = CurrentDb.QueryDefs("Code").SQL

rst.Close
Set rst = Nothing
Set db = Nothing
End Function

because this does not work

thanks in advance

To retrieve the SQL of a stored query, you have to use the QueryDef object:

strSQL = CurrentDb.QueryDefs("QueryName").SQL

Hi,

[quoted text clipped - 34 lines]

I cant find where I am screwing things up or maybe is my function wrong?

 

[Klatuu]

You don't need the recordset.
--
Dave Hargis, Microsoft Access MVP

Is this what you mean?

Public Function GetSavedQuerySQL(Code As String) As String

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb()
Set rst = db.OpenRecordset("OverviewInconsistency")

GetSavedQuerySQL = CurrentDb.QueryDefs("Code").SQL

rst.Close
Set rst = Nothing
Set db = Nothing
End Function

because this does not work

thanks in advance

To retrieve the SQL of a stored query, you have to use the QueryDef object:

strSQL = CurrentDb.QueryDefs("QueryName").SQL

Hi,

[quoted text clipped - 34 lines]

I cant find where I am screwing things up or maybe is my function wrong?

 

[HENDRY_del_FUEGO via AccessMonster.com]

Thanks for your prompt responses but I want to use the queries I retrieve
from that particular table to on a second table.

Not on the table where the data is stored

I thought I needed the rst?

(sorry for the slow understanding but VBA is new since this afternoon for me)

You don't need the recordset.

Is this what you mean?

[quoted text clipped - 25 lines]

 

[Klatuu]

I don't understand what it is you are trying to do. Can you please explain
in detail.
--
Dave Hargis, Microsoft Access MVP

Thanks for your prompt responses but I want to use the queries I retrieve
from that particular table to on a second table.

Not on the table where the data is stored

I thought I needed the rst?

(sorry for the slow understanding but VBA is new since this afternoon for me)

You don't need the recordset.

Is this what you mean?

[quoted text clipped - 25 lines]

I cant find where I am screwing things up or maybe is my function wrong?

 

[HENDRY_del_FUEGO via AccessMonster.com]

well I have two tables: the first table contains a lot of data. I have
identified a lot of inconsistencies in this data and I would like to clean
this up.

I created a second table in which a define the inconsistencies:
I created a Code for each inconsistency, gave it a description, and in the
last column I put the SQl-code.

eg *Code = 1__AX
*description= active items without safety stock
*SQLtext= iif([table1]!activityswitch = "ON" and [table1]!safetystock =
o, "YESactivewithoutss", "no problem")

(the YESactivewithoutss indicates that there is a problem, in a later stadium
I will filter on this.)

So I want to consolidate all my inconsistencies in one table and by means of
a function I want tot run these queries on the data.

eg.instead of calling the function getsavedsql() I would call it
CheckInconsistency() and within the brackets I would enter the Code for the
inconsistency i want to check

like this CheckInconsistency (1__AX)

that is the function I intented to write....

Is this clear or am I still to unstructured?

thanks for your time

I don't understand what it is you are trying to do. Can you please explain
in detail.

Thanks for your prompt responses but I want to use the queries I retrieve
from that particular table to on a second table.

[quoted text clipped - 11 lines]

 

[Klatuu]

Okay, I understand what you are trying to do, but it isn't that easy. The
SQL for a query contains the entire SQL string which includes all fields. To
do this effetively, you will need some pretty sophisticated string
manipulation to pull out the piece for just one field.
--
Dave Hargis, Microsoft Access MVP

well I have two tables: the first table contains a lot of data. I have
identified a lot of inconsistencies in this data and I would like to clean
this up.

I created a second table in which a define the inconsistencies:
I created a Code for each inconsistency, gave it a description, and in the
last column I put the SQl-code.

eg *Code = 1__AX
*description= active items without safety stock
*SQLtext= iif([table1]!activityswitch = "ON" and [table1]!safetystock =
o, "YESactivewithoutss", "no problem")

(the YESactivewithoutss indicates that there is a problem, in a later stadium
I will filter on this.)

So I want to consolidate all my inconsistencies in one table and by means of
a function I want tot run these queries on the data.

eg.instead of calling the function getsavedsql() I would call it
CheckInconsistency() and within the brackets I would enter the Code for the
inconsistency i want to check

like this CheckInconsistency (1__AX)

that is the function I intented to write....

Is this clear or am I still to unstructured?

thanks for your time

I don't understand what it is you are trying to do. Can you please explain
in detail.

Thanks for your prompt responses but I want to use the queries I retrieve
from that particular table to on a second table.

[quoted text clipped - 11 lines]

I cant find where I am screwing things up or maybe is my function wrong?

 

[HENDRY_del_FUEGO via AccessMonster.com]

why the string manipulation?

CheckInconsistency(1__AX) this function should become iif([table1]!
activityswitch = "ON" and [table1]!safetystock =
o, "YESactivewithoutss", "no problem")

CheckInconsistency(1__BX) this function should become iif([table1]!prodcal<>
[table1]!overviewprodcal, "YESprodcal", "no problem")

and so on

I thougt I just needed to describe all the correct SQL-statments in a table
and a function that get this SQL-string and made that Access saw that this is
an SQL statement.

I am getting discouraged for the moment in the beginning I thougt I could
solve this with Eval(), then I thougt my function would to the trick but now..
..

Can you give me any direction?

Okay, I understand what you are trying to do, but it isn't that easy. The
SQL for a query contains the entire SQL string which includes all fields. To
do this effetively, you will need some pretty sophisticated string
manipulation to pull out the piece for just one field.

well I have two tables: the first table contains a lot of data. I have
identified a lot of inconsistencies in this data and I would like to clean

[quoted text clipped - 34 lines]

 

[Klatuu]

Well, I did not notice your statement about putting the SQL values in a
table. You could use a DLookup to get SQL from the table. You would need to
have a field with the name of the field so you could look it up.
--
Dave Hargis, Microsoft Access MVP

why the string manipulation?

CheckInconsistency(1__AX) this function should become iif([table1]!
activityswitch = "ON" and [table1]!safetystock =
o, "YESactivewithoutss", "no problem")

CheckInconsistency(1__BX) this function should become iif([table1]!prodcal<>
[table1]!overviewprodcal, "YESprodcal", "no problem")

and so on

I thougt I just needed to describe all the correct SQL-statments in a table
and a function that get this SQL-string and made that Access saw that this is
an SQL statement.

I am getting discouraged for the moment in the beginning I thougt I could
solve this with Eval(), then I thougt my function would to the trick but now..
..

Can you give me any direction?

Okay, I understand what you are trying to do, but it isn't that easy. The
SQL for a query contains the entire SQL string which includes all fields. To
do this effetively, you will need some pretty sophisticated string
manipulation to pull out the piece for just one field.

well I have two tables: the first table contains a lot of data. I have
identified a lot of inconsistencies in this data and I would like to clean

[quoted text clipped - 34 lines]

I cant find where I am screwing things up or maybe is my function wrong?

 

[HENDRY_del_FUEGO via AccessMonster.com]

mmh I got my solution to work (thans fo rthe help) but now I need to make it
dynamic and rulebased which is not the case in my solution

I think that the complex string manipulation you were talking about is what I
need to do: Can you give me a push in the right direction to cope with this...

Well, I did not notice your statement about putting the SQL values in a
table. You could use a DLookup to get SQL from the table. You would need to
have a field with the name of the field so you could look it up.

why the string manipulation?

[quoted text clipped - 26 lines]

 

[Klatuu]

CAn you please post the code that is working so I can make some suggestions,
please?
--
Dave Hargis, Microsoft Access MVP

mmh I got my solution to work (thans fo rthe help) but now I need to make it
dynamic and rulebased which is not the case in my solution

I think that the complex string manipulation you were talking about is what I
need to do: Can you give me a push in the right direction to cope with this...

Well, I did not notice your statement about putting the SQL values in a
table. You could use a DLookup to get SQL from the table. You would need to
have a field with the name of the field so you could look it up.

why the string manipulation?

[quoted text clipped - 26 lines]

I cant find where I am screwing things up or maybe is my function wrong?

 

[HENDRY_del_FUEGO via AccessMonster.com]

I tried but it appears that my formula is too long and that I am missing ' )'


I created a table where I cutted my tests in different columns, in such a way
that every row represented a test.

eg
External SKU MFG_policy
MinSS .......
Inactive SKU without SS Y MTS
55 ......

then I tried with some string manipulation to create the WHERE clause of my
SQL-code
I pasted my attempt underneath

In this string you will notive [tblBasis&Default] this is the table where all
the data is collected and on which the test should be run.

string: "Iif(" & Iif(IsNull([External SKUSw]),"","[tblBasis&Default].
[External SKUSw]=" & [External SKUSw] & Iif(IsNull([UDC_MFG_POLICY_CD]),"","
AND [tblBasis&Default].[UDC_MFG_POLICY_CD]=" & [UDC_MFG_POLICY_CD] & Iif
(IsNull([Overview Mfg-site / CDC / LDC]),""," AND [tblBasis&Default].
[Overview Mfg-site / CDC / LDC]=" & [Overview Mfg-site / CDC / LDC] & Iif
(isNull([UDC_INVEN_ABC_CD]),""," AND [tblBasis&Default].[UDC_INVEN_ABC_CD]="
& [UDC_INVEN_ABC_CD] & Iif(IsNull([DFULoc]),""," AND [tblBasis&Default].
[DFULoc]=" & [DFULoc] & Iif(isNull([MinSS]),""," AND [tblBasis&Default].
[MinSS]=" & [MinSS] & Iif(IsNull([Source]),""," AND [tblBasis&Default].
[Source]=" & [Source] & Iif(IsNull([Dest]),""," AND [tblBasis&Default].[Dest]
=" & [Dest] & Iif(IsNull([FcstAdjRule]),""," AND [tblBasis&Default].
[FcstAdjRule]=" & [FcstAdjRule] & Iif(IsNull([CustOrderDur]),""," AND
[tblBasis&Default].[CustOrderDur]=" & [CustOrderDur] & Iif(IsNull(
[UDC_MAX_CO_DUR]),""," AND [tblBasis&Default].[UDC_MAX_CO_DUR]=" &
[UDC_MAX_CO_DUR] & Iif(IsNull([Loc]),""," AND [tblBasis&Default].[Loc]=" &
[Loc] & Iif(IsNull([UDC_DISC_DATE]),""," AND [tblBasis&Default].
[UDC_DISC_DATE]=" & [UDC_DISC_DATE] & Iif(IsNull([Discontinue_Rreason]),"","
AND [tblBasis&Default].[Discontinue_Rreason]=" & [Discontinue Rreason] & Iif
(IsNull[SDC_OBSOLETE_SKU]),""," AND [tblBasis&Default].[SDC_OBSOLETE_SKU]=" &
[SDC_OBSOLETE_SKU] & Iif(Isnull([SDC_NEW_SKU]),""," AND [tblBasis&Default].
[SDC_NEW_SKU]=" & [SDC_NEW_SKU])))))))))))))))) & '",YES" & [Description] & ",
NO" & [Description])'

 

[HENDRY_del_FUEGO via AccessMonster.com]

my example has been shuffeld a littel bit

External SKU MFG_policy

MinSS .......
Inactive SKU without SS Y MTS >55 ......

I tried but it appears that my formula is too long and that I am missing ' )'

I created a table where I cutted my tests in different columns, in such a way
that every row represented a test.

eg
External SKU MFG_policy
MinSS .......
Inactive SKU without SS Y MTS
55 ......

then I tried with some string manipulation to create the WHERE clause of my
SQL-code
I pasted my attempt underneath

In this string you will notive [tblBasis&Default] this is the table where all
the data is collected and on which the test should be run.

string: "Iif(" & Iif(IsNull([External SKUSw]),"","[tblBasis&Default].
[External SKUSw]=" & [External SKUSw] & Iif(IsNull([UDC_MFG_POLICY_CD]),"","
AND [tblBasis&Default].[UDC_MFG_POLICY_CD]=" & [UDC_MFG_POLICY_CD] & Iif
(IsNull([Overview Mfg-site / CDC / LDC]),""," AND [tblBasis&Default].
[Overview Mfg-site / CDC / LDC]=" & [Overview Mfg-site / CDC / LDC] & Iif
(isNull([UDC_INVEN_ABC_CD]),""," AND [tblBasis&Default].[UDC_INVEN_ABC_CD]="
& [UDC_INVEN_ABC_CD] & Iif(IsNull([DFULoc]),""," AND [tblBasis&Default].
[DFULoc]=" & [DFULoc] & Iif(isNull([MinSS]),""," AND [tblBasis&Default].
[MinSS]=" & [MinSS] & Iif(IsNull([Source]),""," AND [tblBasis&Default].
[Source]=" & [Source] & Iif(IsNull([Dest]),""," AND [tblBasis&Default].[Dest]
=" & [Dest] & Iif(IsNull([FcstAdjRule]),""," AND [tblBasis&Default].
[FcstAdjRule]=" & [FcstAdjRule] & Iif(IsNull([CustOrderDur]),""," AND
[tblBasis&Default].[CustOrderDur]=" & [CustOrderDur] & Iif(IsNull(
[UDC_MAX_CO_DUR]),""," AND [tblBasis&Default].[UDC_MAX_CO_DUR]=" &
[UDC_MAX_CO_DUR] & Iif(IsNull([Loc]),""," AND [tblBasis&Default].[Loc]=" &
[Loc] & Iif(IsNull([UDC_DISC_DATE]),""," AND [tblBasis&Default].
[UDC_DISC_DATE]=" & [UDC_DISC_DATE] & Iif(IsNull([Discontinue_Rreason]),"","
AND [tblBasis&Default].[Discontinue_Rreason]=" & [Discontinue Rreason] & Iif
(IsNull[SDC_OBSOLETE_SKU]),""," AND [tblBasis&Default].[SDC_OBSOLETE_SKU]=" &
[SDC_OBSOLETE_SKU] & Iif(Isnull([SDC_NEW_SKU]),""," AND [tblBasis&Default].
[SDC_NEW_SKU]=" & [SDC_NEW_SKU])))))))))))))))) & '",YES" & [Description] & ",
NO" & [Description])'

 

[Klatuu]

Sorry, Hendry, but I can't tell from reading the code how to correct it.
There are multiple problems with it and I can't tell what it is trying to do.