A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Query null value problem



 
 
Thread Tools Display Modes
  #1  
Old May 13th, 2010, 06:57 PM posted to microsoft.public.access.queries
ffulmer via AccessMonster.com
external usenet poster
 
Posts: 13
Default 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  
Old May 13th, 2010, 07:38 PM posted to microsoft.public.access.queries
Golfinray
external usenet poster
 
Posts: 1,597
Default 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  
Old May 14th, 2010, 06:54 AM posted to microsoft.public.access.queries
ffulmer via AccessMonster.com
external usenet poster
 
Posts: 13
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:29 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.