If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Query null value problem
Hi All,
I have created an application that among other tasks will convert currencies into a base currency. The user selects a base currency then enters amounts in different currencies into a form. QUERY 1 gets its data from the underlying table and shows only records with the selected base currency. … WHERE (((qry_BdgOthers.CurrencyID)=[BaseCurrency])); QUERY 2 does the opposite. It gets its data from the table and returns records that are not base currencies. … WHERE (((qry_BdgOthers.CurrencyID)[BaseCurrency])); After converting the non base currency into base currency another query adds the Totals together. The trouble is if either of the 2 queries is null I cannot do that. How to solve this problem? At which point to solve it? In the first 2 queries or when I add them together? The field names in the queries come from the table. I don’t know where in the queries I could use Nz(), or how to replace the null value with a zero value. Anybody has idea? Thank you -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201005/1 |
#2
|
|||
|
|||
Query null value problem
What about going in to the query criteria and putting is not null? Or better
yet, go into the form itself and don't allow the user to enter a null value? -- Milton Purdy ACCESS State of Arkansas "ffulmer via AccessMonster.com" wrote: Hi All, I have created an application that among other tasks will convert currencies into a base currency. The user selects a base currency then enters amounts in different currencies into a form. QUERY 1 gets its data from the underlying table and shows only records with the selected base currency. … WHERE (((qry_BdgOthers.CurrencyID)=[BaseCurrency])); QUERY 2 does the opposite. It gets its data from the table and returns records that are not base currencies. … WHERE (((qry_BdgOthers.CurrencyID)[BaseCurrency])); After converting the non base currency into base currency another query adds the Totals together. The trouble is if either of the 2 queries is null I cannot do that. How to solve this problem? At which point to solve it? In the first 2 queries or when I add them together? The field names in the queries come from the table. I don’t know where in the queries I could use Nz(), or how to replace the null value with a zero value. Anybody has idea? Thank you -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201005/1 . |
#3
|
|||
|
|||
Query null value problem
golfinray wrote:
What about going in to the query criteria and putting is not null? Or better yet, go into the form itself and don't allow the user to enter a null value? Thank you for your reply If the user enters only non base currencies this query will be null … WHERE (((qry_BdgOthers.CurrencyID)=[BaseCurrency])); If the user enters only base currency this query will be null … WHERE (((qry_BdgOthers.CurrencyID)[BaseCurrency])); One of the queries can be null and I cannot make a total from a query that returns null. I have no idea how to convert the query that returns null to zero. If technically this is not possible then I need to redesign my queries. I just don't know if there is a way to force a query that returns null to return zero. Thanks ffulmer -- Message posted via http://www.accessmonster.com |
Thread Tools | |
Display Modes | |
|
|