February 29, 2008 at 12:49 pm
I'm working for a charity and tring to make an easy database to monitor what we do it would be easier to use if i could convert a true or yes to a number 1 this would mean using a check box I done this years ago with access 2007 and had an expression to use in query to change value but have lost it can anyone help
February 29, 2008 at 1:06 pm
The only thing I remember regarding booleans in Access is that -1 is a true value and zero is false. Based on that, wouldn't that mean you just need the ABS value?
February 29, 2008 at 1:15 pm
Thanks Lynn yes your right if you try to count it comes back as a negative number I'm not what you would call database literate but some years ago i got an expression for a access 97 database i made to monitor the work we where doing with a charity for the homeless. we have to monitor quite a lot like ethnic origin areas people come from etc and its easy to use if done with a chack box as anyone can enter the info the problem comes if its a negative value then it difficult to take reports from it without messing about a lot if as before i could change the value to 1 a positive number then this can be set in queries and set up to forms which means anyone can gain easy access to the figures when needed, Frank
April 24, 2008 at 6:06 pm
You could change the data type in the table and limit to 1 or 0, but then you couldn't use the check box.
However, you can build baseline queries for reporting from any table with the t/f field(s) and use colname:IIF(fieldname<>0,1,0) in the top cell of the QBE grid for all pertinent t/f fields. Then, build all reports based on such baseline queries rather than the tables directly.
The advantage of this syntax is that this test works for sql server tables (in case you ever upsize your back end) which use 1/0 for t/f rather than the -1/0 codes of Access. An Access MVP informed me that the "<>" operator is much faster than the abs() function.
July 22, 2008 at 11:10 am
Or you could use a SQL table (we'll call it 'Decision') with 2 values and 2 fields. 0 | False, 1 | True and use tinyint for the data type. All of your Access check boxes now become drop-downs with only 2 choices. You can even default them to 0 or 1. Link that to your Access form, and your users can just hit 'y' or 'n' for each field. That's how I get around the Yes/No field in Access. Plus you can create a view that actually says 'Yes' or 'No' without seeing a 1 or 0.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply