where condition doesnt call 0

[TipsyWolf]

hey guys !
i have a query where i have value 0 in RiskCode filed

and i have a textbox in form and in dbl click even i have

="RiskID In(SELECT RiskID FROM Qddall WHERE riskcode=0)"

and when i run dbl click event - it shows me empty list.

i even tried to call to table directly

[RiskCode]=0

but still empty list, while i have 2 records where riskcode = 0
i even tried

[riskcode]=-1

but no luck. any advice ? i guess maybe its deferent type of 0. like text or numbers..

 

[Gasman]

Firstly I expect you would need a space between In and ( ?
What do you get if you run the SELECT in the query sql window ?
Break issues down into their parts and test each individually.

Try the query in the sql window.

Most people do not use macroes, way to limiting.
I would find the riskid with DlookUp() and then use that in the WHERE of the open form, if I did not have it to hand elsewhere.

 

[GPGeorge]

hey guys !
i have a query where i have value 0 in RiskCode filed
View attachment 114368

and i have a textbox in form and in dbl click even i have
View attachment 114369

="RiskID In(SELECT RiskID FROM Qddall WHERE riskcode=0)"

and when i run dbl click event - it shows me empty list.

i even tried to call to table directly

[RiskCode]=0

but still empty list, while i have 2 records where riskcode = 0
i even tried

[riskcode]=-1

but no luck. any advice ? i guess maybe its deferent type of 0. like text or numbers..

Can you show us the datatype of the riskcode field in the underlying table?

 

[TipsyWolf]

Can you show us the datatype of the riskcode field in the underlying table?

sure, its

 

[TipsyWolf]

Firstly I expect you would need a space between In and ( ?

didn't any impact on my another textbox where i have this event with "where condition"

What do you get if you run the SELECT in the query sql window ?

u mean if i run this even i mean if i dbl click it ? it should open for me a list of records which fit to my condition

 

[theDBguy]

Is the Where Condition in a macro supposed to be in quotes? Do your other macros that are working have quotes in the Where Condition?

Sent from phone...

 

[The_Doc_Man]

A good strategy is "divide and conquer." It worked for Julius Caesar 2000 years ago, still works today.

Like Gasman first suggested, pick that apart. For instance, create this query:

SELECT RiskID FROM Qddall WHERE riskcode=0

Now run that to verify that it actually returns a list of Risk IDs.

But I noted something else that makes me wonder a bit... I see "MinofDueDate" which raises the question of whether your FROM targets an aggregate query that uses a MIN function. It would be good to see the SQL for that query so we could look at possible pitfalls.

EDIT by TDM: TheDBguy asks a valid question too. Those quotes could well be in the way.

 

[TipsyWolf]

Do your other macros that are working have quotes in the Where Condition?

yeap.

="RiskID In(SELECT RiskID FROM QddallA WHERE Site='site1' and status='completed')"

works just fine

Is the Where Condition in a macro supposed to be in quotes?

idk i guess im using your code btw lol

 

[GPGeorge]

Thanks, I wanted to rule out that it could be a text field with digits in it.
The_Doc_Man has already asked my followups, so I'll wait for your reply to that.

 

[TipsyWolf]

It would be good to see the SQL for that query

SELECT Tmain.RiskID, Tmain.isActive, Tmain.RiskCode, Min(Tactions.DueDate) AS MinOfDueDate, Tmain.location
FROM Tmain INNER JOIN Tactions ON Tmain.RiskID = Tactions.RiskID
GROUP BY Tmain.RiskID, Tmain.isActive, Tmain.RiskCode, Tmain.location;

 

[TipsyWolf]

Now run that to verify that it actually returns a list of Risk IDs.

 

[GPGeorge]

View attachment 114373

I think this is complicated quite a bit by trying to use macros. Unfortunately, for this purpose, most developers don't actually use macros a lot and, therefore, have less experience trouble-shooting them.

 

[The_Doc_Man]

View attachment 114373

No. Define that SELECT query as a standalone, named query. Then open it, not with a macro but from the list of named queries.

Your "Where Condition" in this example is clearly a full query whereas it should ONLY be a WHERE clause without the word "WHERE" in it. So of course it won't work.

 

[TipsyWolf]

No. Define that SELECT query as a standalone, named query. Then open it, not with a macro but from the list of named queries.

Your "Where Condition" in this example is clearly a full query whereas it should ONLY be a WHERE clause without the word "WHERE" in it. So of course it won't work.

it shows me 1 record which is true. i have only 1 record where riskcode=0

my sql for just new created query is

SELECT RiskID FROM Qddall WHERE riskcode=0

 

[TipsyWolf]

oh ,, i get it .. now i just need to make a dbl click event runs this new query. damn. its so cool to learn new things )
thank u very much

it will show riskID. is there a way to open a masterlist (form) which will show all risks with riskcode=0 ?

upd: or open a master list with riskID listed on a query ?

 

[Gasman]

Your "Where Condition" in this example is clearly a full query whereas it should ONLY be a WHERE clause without the word "WHERE" in it. So of course it won't work.

Yet supposedly works?

didn't any impact on my another textbox where i have this event with "where condition

 

[mike60smart]

oh ,, i get it .. now i just need to make a dbl click event runs this new query. damn. its so cool to learn new things )
thank u very much

it will show riskID. is there a way to open a masterlist (form) which will show all risks with riskcode=0 ?

upd: or open a master list with riskID listed on a query ?

It would be easier if you uploaded a copy of the Db

 

[Josef P.]

="RiskID In(SELECT RiskID FROM QddallA WHERE Site='site1' and status='completed')"

works just fine

Are these perhaps random results?
I assume that with ="id in (...)" all data records are displayed.
The correct expression would be id in (...).
Interestingly, in (select .. from ... where ...) does not work in my test in the macro, with DoCmd.OpenForm in VBA was filtered correctly.

Note: I tested: id in (2;4;5) and "id in (2;4;5)".
The first variant worked, the second variant showed all data records.