Convert True/False to 1/0? (Access 2007)

[Jadesky]

I apologize in advance for my lack of skills, but hope you can help me out of a tight spot at work.

1. Got a number of forms with True/False checkboxes on, when I run the query in order to be able to import the data into Excel, I see TRUE/FALSE values in there. Is there a way to be able to convert them to +1/0 before going into Excel?

2. Unrelated question but if you have any guidance I'd really appreciate it. I have a form/table that asks the user to select a currency that their transaction is in. I have another table of currencies and their respective exchange rates, I would really like to be able to substitute the currency code (e.g. GBP, EUR, USD) the user has chosen, with the exchange rate (e.g. 1.31) in the query that exports to Excel - possible?

Really, really appreciate any solutions you can give

 

[namliam]

I dont have A2007, so these answers based of 2002 and earlier versions
1. False is already 0 and True is actually -1
Doing Abs(YourField) will change it to 1 and 0 for true and false
Otherwize if you like use an IFF:
IIF(Yourfield, 1,0)

2. Yes possible assuming the exchange rate is somewhere to be found in access.
Simply use a query to join your currency to the exchange rate and your good to go.

 

[Guus2005]

First i'd like to welcome you to this forum!

1. Using the IIF statement you can convert TRUE to 1 and <> TRUE to 0. Type F1 for a detailed explanation

2. If you let users choose their currency from a combobox, you can let it return the exchange rate based on the combobox properties.

Properties Combobox:
Row Source Type: Table/Query
Row Source: select Rate, Curr from ExchangeRates
Column Count: 2
Column Widths: 0;5
Bound Column: 1

The rowsource contains the query to your exchange rates table. As you can see, it contains two fields of which the first one is hidden: width=0.

If you refer to the combobox like me.cmbCurrency it returns the Exchange Rate of the chosen currency

HTH

 

[Jadesky]

Thank you so much for your help!! Got the 1/0 the way I need it now, just struggling (due to my complete lack of experience!) with the second point.

Have created combobox as Guus2005 outlined, but I don't know how to refer to the value I need from that box in the query where it should display the exchange rate and not the currency name...

 

[Jadesky]

Oh I finally figured it out - thank you again!