...The Ol' NULL Statement...

  • Thread starter Thread starter Popparod
  • Start date Start date
P

Popparod

....LoL....if I could get just ONE to work.....!

.....here's my dilema.....

....I need an Is Null statment to that say's "No Data" if theres of course
"no data"...Null/Empty

.....This works except I need "No Data"...not a "0"
......Street: Nz([StreetAddress], 0)

SELECT DISTINCTROW [AFBA Members].Street, Nz([Street],0) AS StreetAddress
FROM [AFBA Members];

.......I'm sure glad you'all have a sense of humor when it comes to simple
questions....!
 
......Thanks.....I guess I forgot the " " around No Data........

.....No for my next question....where I run into a problem ALL the time.....

..... How do I base the text box on the Query instead of the table now....

.....I try to use the Expression Builder but as of yet...I've never gotten
the hang of it.....

....if I copy/paste the SQL from the query...I get a #Name?.....

.....Do I have to remove the text box and replace it with one based on the NZ
query.....?

....if I could get the hang of the builder....my strife would end....
 
.......I know it's not any of these......StreetNzequals0 is my Query.....


=[StreetNzequals0]![Street]=[StreetNzequals0]![StreetAddress]

Street= [StreetNzequals0]![Street]. [StreetNzequals0]![StreetAddress]

=Street:[StreetNzequals0]![Street].[StreetNzequals0]![StreetAddress]

=[Street]=[StreetNzequals0]![Street].[StreetNzequals0]![StreetAddress]
 
Popparod,

I can't figure out what you are trying to do.

If you mean you have a Query named StreetNzequals0 and in that query you
have a calculated field that looks like...
Street: Nz([StreetAddress],"No data")
.... and if you mean you have a form based on this query, then you can
place a textbox on the form with its Control Source set to Street and as
long as the textbox is not named StreetAddress you should be ok.

If you mean something else, please try to give some more details of how
you want things to hang together.
 
........It's based on the Members Table......

......FirstName..LastName.....Street...City.....etc.....

....at times there will be blanks in either Street......City.......

.....Rather than have a Blank text box.....I figured I'd give it a "No
Data"......

.....When I made the Query you gave me help with.....it ran perfect......(out
of the form)

.....On it's own it filled all the Street (Blanks)...with "No Data"....

......But how do I use the query in the "MembersForm" when the "Street"
txtBox still bound to
"Street" in the Members Table....
 
.....What I meant to say....is.......

.....My Members Form is Based on my Members Table......




.......By the way.....

......just for the hell of it....I did make just a Street form just to test
the NzQuery....
......and yes..I can bind a txt box to it.....and it works good....

......but not when my Members Form is based on the Members Table......

.....if I could just figure out the Expression Builder thing...
 
Popparod,
.....But how do I use the query in the "MembersForm" when the "Street"
txtBox still bound to
"Street" in the Members Table....
Click to expand...

You can't. If you have a textbox bound to the Street field in the
table, and there is no entry in the Street field, then there will be
nothing shown in the textbox on the form. End of story. If you want
the "No data" to show in the textbox, then it has to be either an
unbound textbox with the Nz expression entered into its Control Source,
or else bound to a calculated field in the query that the form is based
on, in which case it should not be called Street. Or whatever it is...
I am now confused about where SteetAddress comes from.
 
Popparod,
....if I could just figure out the Expression Builder thing...
Click to expand...

No, I don't think that's it. The Expression Builder won't be able to
help you do something which can't be done.
 
.......I appreciate the help anyway......

....Hey if anything.....I got the Query to work....LoL.....

......even if I can't use it for anything....good practice....
 
Popparod,

When you say "Query" I assume you are referring to the calculated
expression we have been discussing. Well, I don't think it is quite
correct either to say you can't use it. You can use it, to achieve the
results you want too. Just not in the way you thought you were going to
use it. To base a form on a query with a calculated field, or to use a
calculated control on a form, is standard practice.
 
......I appreciate the help anyway......

...Hey if anything.....I got the Query to work....LoL.....

.....even if I can't use it for anything....good practice....
Click to expand...
Popparod, what I think you're missing in Steve's explanation is that
you cannot base a TEXTBOX on a query - but you *can* base a FORM on a
query.

Use the Query with the calculated field as the recordsource *OF YOUR
FORM* rather than using the table directly. You won't be able to
update the calculated field (but you wouldn't want to anyway); all the
other fields will be updateable.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Back
Top