[AccessD] set control source for text box in VBA using Nz

Just break the problem up? You have two data elements, so you need two Nz calls...

 Format(Nz(rstData.Fields("Date"),""), "dddd") & " " & Nz(rstData.Fields("ProductName"), 0))

Note that the Nz is applied to the two fields before doing anything else, and that the Nz wrapping the date field is set to return an empty string and not zero. The reason is that if you format 0 as a date you will get 12/31/1899, which was a Saturday so  Format(0, "dddd") will result in "Saturday", probably not what you want.

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Carolyn Johnson
Sent: Wednesday, November 18, 2009 1:56 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] set control source for text box in VBA using Nz

Thanks.  I didn't know that the format funtion would return a string, but I should have at least caught the space I put there.

Unfortunately, I still don't see how to replace the null values with 0 when the control source is set in code.


Carolyn Johnson