Default Values for textbox field in a Report (1 Viewer)

Weekleyba

Silver Supporter
Local time
Today, 08:53
Joined
Oct 10, 2013
Messages
287
I import a table from Excel in the DB.
I have some reports generated by queries from that one table.
I'd like the values to show $0.00 in a report fed by the queries, when there is a null. ie. no record for a query.

How can I do this?
 

theDBguy

I’m here to help
Local time
Today, 06:53
Joined
Oct 29, 2018
Messages
8,540
Hi. Try using the Nz() function. For example:
Code:
Nz([FieldName],0)
Hope that helps...
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 06:53
Joined
Aug 30, 2003
Messages
33,760
no record for a query
FYI, there's a difference between a Null value in a record and there not being a record. Nz() will work on a Null value in a record, it won't help if there's no record. That would probably require editing the joins in the query.
 

Weekleyba

Silver Supporter
Local time
Today, 08:53
Joined
Oct 10, 2013
Messages
287
But where does the Nz function go?
The report is fed by a query.
I’ve tried creating expressions in the query and using the Nz but still nothing. ??
 

Weekleyba

Silver Supporter
Local time
Today, 08:53
Joined
Oct 10, 2013
Messages
287
Ah! I see Pbaldy.
Hmm.I’ll take a look at that.
Not sure if I can figure that out or not.
But thanks.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 06:53
Joined
Aug 30, 2003
Messages
33,760
Not sure if I can figure that out or not.
If that's what's happening, you would typically edit the join between the relevant tables. The correct option may be obvious once you see them. If you have trouble, you can attach the db here or at least post the SQL of the query and describe what's missing.
 

Weekleyba

Silver Supporter
Local time
Today, 08:53
Joined
Oct 10, 2013
Messages
287
Now that I'm back in the office and looking at the db, even though it's a query, it's only fed by one table. So no joins to work with.
How can I make that work?

Note: Only the first selection in the combo for the Main_F, has data. "Belcourt Service Unit" Select that one and it shows the problem.
The query the feeds the Current Year Quarters is QTR_CurrentFY_Q.
The table has no record for current year quarters funds for the Belcourt Service Unit, so that's why the text box shows nothing.
I would like it to show $0.00.

See attached db.
 

Attachments

theDBguy

I’m here to help
Local time
Today, 06:53
Joined
Oct 29, 2018
Messages
8,540
Hi. Take a look at the attached modified copy of your db. Is this what you wanted?

Edit: Deleted attachment
 
Last edited:

Weekleyba

Silver Supporter
Local time
Today, 08:53
Joined
Oct 10, 2013
Messages
287
Sorry DBguy, but I'm not seeing how this helps me.
Can you point out what you are trying to show me?
Also, I'm wondering if trying to create this on a Report vs. a Form is an issue.
 

theDBguy

I’m here to help
Local time
Today, 06:53
Joined
Oct 29, 2018
Messages
8,540
Sorry DBguy, but I'm not seeing how this helps me.
Can you point out what you are trying to show me?
Also, I'm wondering if trying to create this on a Report vs. a Form is an issue.
Sorry for the confusion. When I first opened your report, this is what I got.
form1.PNG

After modifying your report, this is what I get now.
form2.PNG

Did I misunderstand your question/requirement?
 

Weekleyba

Silver Supporter
Local time
Today, 08:53
Joined
Oct 10, 2013
Messages
287
That looks awesome!
But the database you loaded is not mine? It's a Loan database.
 

Weekleyba

Silver Supporter
Local time
Today, 08:53
Joined
Oct 10, 2013
Messages
287
Excellent! Now why did I think to put the Nz function there???
Thank you.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom