-
WSzbrett
AskWoody LoungerThat is exactly what I was trying to get to but I had the syntax wrong. Works great. Thanks!
-
WSzbrett
AskWoody LoungerThat made sense and I no longer lose the “+” signs when I export to Excel. Thanks!
-
WSzbrett
AskWoody LoungerI tried to specify the column as text in Excel, but it doesn’t make a difference. The custom format is a good idea, except these are credit rankings and if I do that, it places a “+” in front of all of the positive numbers where I only need it on certain ones. i.e. 3 and 3+ would both be valid entries in the column.
-
WSzbrett
AskWoody LoungerThat sounds promising but I’m fairly new at Access so I not exactly sure exactly where to do this in the query or report
Thanks
-
WSzbrett
AskWoody LoungerI tried that and it works, but I lose some data calculations that I’ve incorporated into the report that isn’t in the query.
-
WSzbrett
AskWoody LoungerI tried that but it didn’t work. Somehow I have to have Access create the column as a text field in Excel before it exports the file, if you know what I mean…
-
WSzbrett
AskWoody LoungerI only figured it after thinking really hard about what was going on and a little trial and error. However, now when I export the report into Excel, it drops the column sums that have the Nz function in them but picks up the rest. Anyone know how to fix that? You close one door and another one opens…
-
WSzbrett
AskWoody LoungerTo answer my own question in case a hapless Access newbie like myself has the same problem. If you use Nz in a report text box, the control data and name must be different or you will get an error…
-
WSzbrett
AskWoody LoungerI tried that and I got a #Error in the report column… other variations yield all zeros, wiping out any values. Hence my frustration.
-
WSzbrett
AskWoody LoungerHere is the Query. Can anyone tell me how to insert the Nz function and make it work. I’m about ready to give up…
TRANSFORM Sum([Cost, NBV, RV].SumOfLRESID) AS [The Value]
SELECT [Cost, NBV, RV].[Dealer Name], Sum([Cost, NBV, RV].SumOfAEQPCOS) AS SumOfSumOfAEQPCOS, Sum([Cost, NBV, RV].MaxOfLNETINVS) AS SumOfMaxOfLNETINVS, Sum([Cost, NBV, RV].SumOfLRESID) AS SumOfSumOfLRESID
FROM [Cost, NBV, RV]
GROUP BY [Cost, NBV, RV].[Dealer Name]
PIVOT Format([LTERMDA],”yyyy”); -
WSzbrett
AskWoody LoungerI stumbled onto the Nz function after posting and was playing with it. I guess the best solution is to use it at the report level but I’m having trouble making it work. The contol source in the text box for the column is 2003. I either get all zeros or error. Could you give me some examples of what is the correct format for the Nz function in this case?
Thanks so much for your response.
-
WSzbrett
AskWoody LoungerI didn’t really check the Excel dates, good catch.
I resolved my problem the easy way. I went to the person who generates the file I’m importing and asked if the date format could be changed to mm/dd/yyyyy. No problem. Wish I had thought of that before I spent a bunch of time trying to make it work.
Thanks for the reply
-
WSzbrett
AskWoody LoungerEeek. I’ll try to figure it out (just starting with Access). I wonder why Excel handles it and Access doesn’t. Does Access 2000 have the same problem?
Thanks for your reply.
-
WSzbrett
AskWoody LoungerThanks Andrew. FYI – I also found out that EDATE will do the same thing.
Regards,
Brett
![]() |
Patch reliability is unclear. Unless you have an immediate, pressing need to install a specific patch, don't do it. |
SIGN IN | Not a member? | REGISTER | PLUS MEMBERSHIP |

Plus Membership
Donations from Plus members keep this site going. You can identify the people who support AskWoody by the Plus badge on their avatars.
AskWoody Plus members not only get access to all of the contents of this site -- including Susan Bradley's frequently updated Patch Watch listing -- they also receive weekly AskWoody Plus Newsletters (formerly Windows Secrets Newsletter) and AskWoody Plus Alerts, emails when there are important breaking developments.
Get Plus!
Welcome to our unique respite from the madness.
It's easy to post questions about Windows 11, Windows 10, Win8.1, Win7, Surface, Office, or browse through our Forums. Post anonymously or register for greater privileges. Keep it civil, please: Decorous Lounge rules strictly enforced. Questions? Contact Customer Support.
Search Newsletters
Search Forums
View the Forum
Search for Topics
Recent Topics
-
Where’s the cache today?
by
Up2you2
1 hour, 16 minutes ago -
Ascension says recent data breach affects over 430,000 patients
by
Nibbled To Death By Ducks
2 hours, 56 minutes ago -
Nintendo Switch 2 has a remote killing switch
by
Alex5723
3 hours, 17 minutes ago -
Blocking Search (on task bar) from going to web
by
HenryW
7 hours, 57 minutes ago -
Windows 10: Microsoft 365 Apps will be supported up to Oct. 10 2028
by
Alex5723
13 hours, 4 minutes ago -
Add or Remove “Ask Copilot” Context Menu in Windows 11 and 10
by
Alex5723
13 hours, 11 minutes ago -
regarding april update and may update
by
heybengbeng
14 hours, 40 minutes ago -
MS Passkey
by
pmruzicka
2 hours, 28 minutes ago -
Can’t make Opera my default browser
by
bmeacham
22 hours, 20 minutes ago -
*Some settings are managed by your organization
by
rlowe44
9 hours, 4 minutes ago -
Formatting of “Forward”ed e-mails
by
Scott Mills
21 hours, 15 minutes ago -
SmartSwitch PC Updates will only be supported through the MS Store Going Forward
by
PL1
1 day, 16 hours ago -
CISA warns of hackers targeting critical oil infrastructure
by
Nibbled To Death By Ducks
2 days, 1 hour ago -
AI slop
by
Susan Bradley
16 hours, 2 minutes ago -
Chrome : Using AI with Enhanced Protection mode
by
Alex5723
2 days, 3 hours ago -
Two blank icons
by
CR2
14 hours, 58 minutes ago -
Documents, Pictures, Desktop on OneDrive in Windows 11
by
ThePhoenix
2 days, 12 hours ago -
End of 10
by
Alex5723
2 days, 14 hours ago -
Single account cannot access printer’s automatic duplex functionality
by
Bruce
1 day, 12 hours ago -
test post
by
gtd12345
2 days, 20 hours ago -
Privacy and the Real ID
by
Susan Bradley
2 days, 10 hours ago -
MS-DEFCON 2: Deferring that upgrade
by
Susan Bradley
13 hours, 17 minutes ago -
Cant log on to oldergeeks.Com
by
WSJonharnew
3 days, 1 hour ago -
Upgrading from Win 10
by
WSjcgc50
1 day, 12 hours ago -
USB webcam / microphone missing after KB5050009 update
by
WSlloydkuhnle
1 day, 16 hours ago -
TeleMessage, a modified Signal clone used by US government has been hacked
by
Alex5723
3 days, 16 hours ago -
The story of Windows Longhorn
by
Cybertooth
3 days, 4 hours ago -
Red x next to folder on OneDrive iPadOS
by
dmt_3904
3 days, 18 hours ago -
Are manuals extinct?
by
Susan Bradley
19 hours, 9 minutes ago -
Canonical ditching Sudo for Rust Sudo -rs starting with Ubuntu
by
Alex5723
4 days, 3 hours ago
Recent blog posts
Key Links
Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.
Mastodon profile for DefConPatch
Mastodon profile for AskWoody
Home • About • FAQ • Posts & Privacy • Forums • My Account
Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts
Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.