How do I access the value of a toggle button in a query statement? (1 Viewer)

GoodyGoody

Registered User.
Local time
Today, 22:21
Joined
Aug 31, 2019
Messages
120
Hi,

I'm running Access 2013 on Windows 10. I have a form with a drop down which selects races that have not been 'finalised' (i.e. the results verified as correct, it doesn't really matter though. The point is that this is a an access database Boolean Yes/No type field with values as I understand of 0-false and -1-true).

The current SQL runs fine and selects only the non-finalised races:
SELECT RN.ID, RN.RaceName, RN.RaceDistance
FROM RaceName AS RN
WHERE (((RN.ID) In (SELECT RE.RaceName FROM RaceEvent AS RE WHERE RE.RaceName = RN.ID and RE.RaceEventClosed=false)))
ORDER BY RN.RaceName;

I now want to add a toggle button on the form so that users can choose which type of race to report on so I've added a toggle button to the form and amended the SQL as follows using the name of the new field on the form. I've used the CInt() function as I believe that the actual form field is a variant type. However, when I run the SQL below it tells me the expression is too complex to evaluate (there are no typos and if I use '0' or '-1' instead it works fine:

SELECT RN.ID, RN.RaceName, RN.RaceDistance
FROM RaceName AS RN
WHERE (((RN.ID) In (SELECT RE.RaceName FROM RaceEvent AS RE WHERE RE.RaceName = RN.ID and RE.RaceEventClosed=CInt([Forms]![frmSelectRace]![togHistReports]))))
ORDER BY RN.RaceName;

Your thoughts please oh access Gods! Many thanks as ever. :)
Stephen
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:21
Joined
Oct 29, 2018
Messages
21,358
Hi Stephen. Have you tried it without the CInt()?
Code:
SELECT RN.ID, RN.RaceName, RN.RaceDistance
FROM RaceName AS RN
WHERE (((RN.ID) In (SELECT RE.RaceName FROM RaceEvent AS RE 
WHERE  RE.RaceName = RN.ID and  RE.RaceEventClosed=[Forms]![frmSelectRace]![togHistReports])))
ORDER BY RN.RaceName;
Just a thought...
 

jdraw

Super Moderator
Staff member
Local time
Today, 18:21
Joined
Jan 23, 2006
Messages
15,364
Please post a list of fields and datatypes in your RaceName and RaceEvent tables.
 
Last edited:

vba_php

Forum Troll
Local time
Today, 17:21
Joined
Oct 6, 2019
Messages
2,884
Stephen,

This is just an addition to what you've already read. I notice a few things regarding the error access is giving you:
SELECT RN.ID, RN.RaceName, RN.RaceDistance
FROM RaceName AS RN
are you sure tables can take on aliases? (e.g. - using ''AS''). I've never seen that done.
WHERE (((RN.ID) In (SELECT RE.RaceName FROM RaceEvent AS RE )))
you're asking for 'ID' in a subquery, but your select statement in the subquery has no 'ID' field in it. are you sure you can do this? I've never seen that done either.
WHERE RE.RaceName = RN.ID and RE.RaceEventClosed=CInt([Forms]![frmSelectRace]![togHistReports]))))

ORDER BY RN.RaceName;
you've got a WHERE clause *inside* a subquery, which is initiated by a WHERE clause itself. I would think something that like would seriously be too complex for access to figure out. I might be wrong, but that's what stood out in this man's mind.
 

GoodyGoody

Registered User.
Local time
Today, 22:21
Joined
Aug 31, 2019
Messages
120
Thanks for the messages guys. There's nothing wrong with the SQL as it works with CInt(0) 'False' or CInt(-1) 'True'. It doesn't work without CInt() and I would expect that as the datatype of a form field is Variant. The datatype of the RaceEventClosed field is Yes/No in the access table i.e. Boolean but I think it is stored as an integer although I may be wrong and that might be the problem. As mentioned the only change I have made is to refer to a form field rather than a constant.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:21
Joined
Oct 29, 2018
Messages
21,358
Hi. Just to check, what do you get with this SQL?


SELECT [Forms]![frmSelectRace]![togHistReports]
 

Cronk

Registered User.
Local time
Tomorrow, 09:21
Joined
Jul 4, 2013
Messages
2,770
Do you really have a table structure where the Primary key of ID in table RaceName, corresponds to the foreign key RaceName in table RaceEvent? If so, funny table structure or rather field naming.
 

GoodyGoody

Registered User.
Local time
Today, 22:21
Joined
Aug 31, 2019
Messages
120
theDBGuy, the select statement allows it but it shows nothing in the column. Perhaps you just can't do it although in VBA [Forms]![frmSelectRace]![togHistReports].value shows the expected value of 0 or -1. Anyways, I've taken a different route but if there is an answer it would be good to know. Thanks
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:21
Joined
Oct 29, 2018
Messages
21,358
theDBGuy, the select statement allows it but it shows nothing in the column. Perhaps you just can't do it although in VBA [Forms]![frmSelectRace]![togHistReports].value shows the expected value of 0 or -1. Anyways, I've taken a different route but if there is an answer it would be good to know. Thanks
Hi. Glad to hear you found another way. To get to the bottom of the original problem, we'll probably need to see a demo version of your db showing the issue. Cheers!
 

Users who are viewing this thread

Top Bottom