How do I access the value of a toggle button in a query statement?

[GoodyGoody]

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]

Hi Stephen. Have you tried it without the CInt()?

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]

Please post a list of fields and datatypes in your RaceName and RaceEvent tables.

 

[vba_php]

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]

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]

Hi. Just to check, what do you get with this SQL?


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

 

[Cronk]

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]

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]

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!