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

Weekleyba

Registered User.
Local time
Today, 06:05
Joined
Oct 10, 2013
Messages
586
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
Staff member
Local time
Today, 04:05
Joined
Oct 29, 2018
Messages
21,357
Hi. Try using the Nz() function. For example:
Code:
Nz([FieldName],0)
Hope that helps...
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 04:05
Joined
Aug 30, 2003
Messages
36,118
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

Registered User.
Local time
Today, 06:05
Joined
Oct 10, 2013
Messages
586
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

Registered User.
Local time
Today, 06:05
Joined
Oct 10, 2013
Messages
586
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, 04:05
Joined
Aug 30, 2003
Messages
36,118
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

Registered User.
Local time
Today, 06:05
Joined
Oct 10, 2013
Messages
586
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

  • DFM Funding Database TEST1.zip
    82.6 KB · Views: 101

theDBguy

I’m here to help
Staff member
Local time
Today, 04:05
Joined
Oct 29, 2018
Messages
21,357
Hi. Take a look at the attached modified copy of your db. Is this what you wanted?

Edit: Deleted attachment
 
Last edited:

Weekleyba

Registered User.
Local time
Today, 06:05
Joined
Oct 10, 2013
Messages
586
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
Staff member
Local time
Today, 04:05
Joined
Oct 29, 2018
Messages
21,357
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

Registered User.
Local time
Today, 06:05
Joined
Oct 10, 2013
Messages
586
That looks awesome!
But the database you loaded is not mine? It's a Loan database.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:05
Joined
Oct 29, 2018
Messages
21,357
That looks awesome!
But the database you loaded is not mine? It's a Loan database.
Oops, sorry. My mistake. Hope this is the right one now.
 

Attachments

  • DFM Funding Database TEST1.zip
    82.1 KB · Views: 100

Weekleyba

Registered User.
Local time
Today, 06:05
Joined
Oct 10, 2013
Messages
586
Excellent! Now why did I think to put the Nz function there???
Thank you.
 

Users who are viewing this thread

Top Bottom