Prev: Tricky Accounting query - and not sure how to build it
Next: First function returning min
From: ffulmer via AccessMonster.com on 13 May 2010 13:57
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/Forums.aspx/access-queries/201005/1

From: golfinray on 13 May 2010 14:38
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/Forums.aspx/access-queries/201005/1
>
> .
>
From: ffulmer via AccessMonster.com on 14 May 2010 01:54
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

 | 
Pages: 1
Prev: Tricky Accounting query - and not sure how to build it
Next: First function returning min