Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-22-2019, 03:14 PM   #1
Dick7Access
Dick S
 
Dick7Access's Avatar
 
Join Date: Jun 2009
Location: Belleview, FL
Posts: 3,499
Thanks: 608
Thanked 41 Times in 39 Posts
Dick7Access is on a distinguished road
empty field in eepoet

Just came back from VA. Doctor said my report was too confusing. Just made a new one but canít get the empty fields to post a (big X). I tried with a (IF THEN) but itís not working. What am I missing? or should I use something else? See attachment. Thanks
Attached Files
File Type: zip DicksvitalsFromScratch.zip (91.6 KB, 19 views)

Dick7Access is offline   Reply With Quote
Old 10-22-2019, 03:27 PM   #2
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,286
Thanks: 97
Thanked 2,032 Times in 1,979 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: empty field in eepoet

Dick,

Not sure what you are asking. Your macro wants you to go to a new record --hence empty fields.
If you go to previous records(record selector bottom left) the data is displayed.
Perhaps I've missed your key point..
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 10-22-2019, 04:05 PM   #3
Dick7Access
Dick S
 
Dick7Access's Avatar
 
Join Date: Jun 2009
Location: Belleview, FL
Posts: 3,499
Thanks: 608
Thanked 41 Times in 39 Posts
Dick7Access is on a distinguished road
Re: empty field in eepoet

Quote:
Originally Posted by jdraw View Post
Dick,

Not sure what you are asking. Your macro wants you to go to a new record --hence empty fields.
If you go to previous records(record selector bottom left) the data is displayed.
Perhaps I've missed your key point..
My autroexec opens my entry form (frmMain)
My query (qry3Months) will sort the dates my doctor wants
My report (rpt3Months) has the query as its data source.
Report does a good job. If the data source is empty for the (novlin field) the report shows nothing of course.
(see VBA on report) (on load) of report I tried getting report to show a big X when novlin field is empty with a (IF THEN)
Once I get it to do that for (novlin field), I will do the same for all other input fields.

Dick7Access is offline   Reply With Quote
Old 10-22-2019, 05:51 PM   #4
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,479
Thanks: 0
Thanked 578 Times in 574 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: empty field in eepoet

Can't set Text or Value properties of control in report event.

Calculate in query or textbox on report: Nz([novlin], "X")

Why is quantifiable data in text field instead of number type?

Why do the fields not even show color or border when there is no data? Not finding any VBA or conditional formatting.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by June7; 10-22-2019 at 05:57 PM.
June7 is online now   Reply With Quote
The Following User Says Thank You to June7 For This Useful Post:
Dick7Access (10-22-2019)
Old 10-26-2019, 01:50 PM   #5
Dick7Access
Dick S
 
Dick7Access's Avatar
 
Join Date: Jun 2009
Location: Belleview, FL
Posts: 3,499
Thanks: 608
Thanked 41 Times in 39 Posts
Dick7Access is on a distinguished road
Re: empty field in eepoet

Quote:
Originally Posted by June7 View Post
Can't set Text or Value properties of control in report event.

THANKS FOR ANSWERING
Calculate in query or textbox on report: Nz([novlin], "X")
I WAS EXPERIMENTING AND FOERGOT TO TAKE IT OUT.

Why is quantifiable data in text field instead of number type?
BECAUSE I READ SOMEPLACE THAT NUMBER FIELDS CAN'T BE REPLACED WITH TEXT LIKE A TEXT FIELD CAN.
WOULD THERE BE SOME ADVANTAGE IN THIS CASE TO HAVING A NUMBER FIELD? JUST WONDERING!

Why do the fields not even show color or border when there is no data? Not finding any VBA or conditional formatting.
BECAUSE I NEED THIS SOON AND DID NOT WANT TO SPEND TIME LEARNING ALL OVER AGAIN HOW TO DO CONDITIONAL FORMATTING. ITS BEEN AT LEAST 10 YEARS SINCE I HAVE DONE IT. WASN'T EVEN AWARE THAT VBA COULD FILL IN THE FIELDS AND BORDERS COULD BE COLORED WHEN NO DATA PRESENT. THIS IS THE REASON I WAS TRYING TO PUT N/A IN NULL FIELDS SO COLORS WOULD SHOW UP. ONCE I GET MY DOCTOR'S REQUEST SATISFIED I HOPE TO GO BACK AND STUDY ALL OF THIS. I SAY HOPE AS I HAVE A TRIP IN NOVEMBER TO Guadalajara, Mexico TO GET READY FOR AND A MEETING IN LA IN DECEMBER, AND A STOP
OVER WITH DOC_MAN! ONCE AGAIN, THANKS AGAIN FOR THE HELP
Dick7Access is offline   Reply With Quote
Old 10-26-2019, 02:06 PM   #6
Dick7Access
Dick S
 
Dick7Access's Avatar
 
Join Date: Jun 2009
Location: Belleview, FL
Posts: 3,499
Thanks: 608
Thanked 41 Times in 39 Posts
Dick7Access is on a distinguished road
Re: empty field in eepoet

Just found a short term remedy to my problem. I set for each fields default value N/A. Now when I make an entry in just one or two fields the nulls still have data, and the colors still show. However, I still want to go back and study conditional formatting and VBA for situation like this. When I enter a correct data it of course overrides the N/A.
Dick7Access is offline   Reply With Quote
Old 10-26-2019, 04:39 PM   #7
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,479
Thanks: 0
Thanked 578 Times in 574 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: empty field in eepoet

If you wanted to sort or filter or do arithmetic with numeric values will have issues if they are in a text type field. 15 will sort before 2 because alpha sort applies - 1 character at a time.

I still don't know why controls don't have color or border just because field is null. This does not happen in my db without VBA or CF neither of which I saw in your db.

__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is online now   Reply With Quote
The Following User Says Thank You to June7 For This Useful Post:
Dick7Access (10-26-2019)
Old 10-26-2019, 05:52 PM   #8
Dick7Access
Dick S
 
Dick7Access's Avatar
 
Join Date: Jun 2009
Location: Belleview, FL
Posts: 3,499
Thanks: 608
Thanked 41 Times in 39 Posts
Dick7Access is on a distinguished road
Re: empty field in eepoet

Quote:
Originally Posted by June7 View Post
If you wanted to sort or filter or do arithmetic with numeric values will have issues if they are in a text type field. 15 will sort before 2 because alpha sort applies - 1 character at a time.

I still don't know why controls don't have color or border just because field is null. This does not happen in my db without VBA or CF neither of which I saw in your db.
Wow! when that happen I thought it was normal. Anybody else have a clue?
Dick7Access is offline   Reply With Quote
Old 10-26-2019, 08:25 PM   #9
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,479
Thanks: 0
Thanked 578 Times in 574 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: empty field in eepoet

Ooops, have to retract my earlier statement. I was looking at a form, not a report. Yes, controls with Null do not show color or borders. Dang, I've never noticed that before. Maybe because I usually set BorderStyle and BackStyle transparent or field has value.

I would still use number type fields for quantifiable data and deal with nulls in report design. Can still use an expression in query or textbox to provide substitute value.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by June7; 10-26-2019 at 08:52 PM.
June7 is online now   Reply With Quote
Old 10-27-2019, 09:03 AM   #10
Dick7Access
Dick S
 
Dick7Access's Avatar
 
Join Date: Jun 2009
Location: Belleview, FL
Posts: 3,499
Thanks: 608
Thanked 41 Times in 39 Posts
Dick7Access is on a distinguished road
Re: empty field in eepoet

Quote:
Originally Posted by June7 View Post
Ooops, have to retract my earlier statement. I was looking at a form, not a report. Yes, controls with Null do not show color or borders. Dang, I've never noticed that before. Maybe because I usually set BorderStyle and BackStyle transparent or field has value.

I would still use number type fields for quantifiable data and deal with nulls in report design. Can still use an expression in query or textbox to provide substitute value.
29 years ago when I went into computers (forced kicking and screaming by Navy) I heard something said by a young boy that still holds true today (especially Access) and I quote, "My computers will not do what I want it to do. Only what I tell it to do".
Dick7Access is offline   Reply With Quote
Old 10-27-2019, 09:44 AM   #11
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,382
Thanks: 115
Thanked 3,114 Times in 2,831 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: empty field in eepoet

I was surprised by comments in this thread so just tested in a report with null values using a coloured fill and border for each field.
Also tested in a form
As you can see from the attached image, I'm not getting the same results.
Tested using A2010

EDIT:
I've just created a quick badly laid out report in Dick's db based on the same query & added coloured fills to three of the fields.
The background & border are displayed when the values are null.
However, I've no idea what makes that behaviour different in the original report
Attached Images
File Type: png ReportWithNulls.PNG (67.7 KB, 10 views)
Attached Files
File Type: zip DicksvitalsFromScratch_v2.zip (39.4 KB, 3 views)
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)

Last edited by isladogs; 10-27-2019 at 10:14 AM. Reason: Added attachment
isladogs is offline   Reply With Quote
Old 10-27-2019, 11:36 AM   #12
Dick7Access
Dick S
 
Dick7Access's Avatar
 
Join Date: Jun 2009
Location: Belleview, FL
Posts: 3,499
Thanks: 608
Thanked 41 Times in 39 Posts
Dick7Access is on a distinguished road
Smile Re: empty field in eepoet

Quote:
Originally Posted by isladogs View Post
I was surprised by comments in this thread so just tested in a report with null values using a coloured fill and border for each field.
Also tested in a form
As you can see from the attached image, I'm not getting the same results.
Tested using A2010

EDIT:
I've just created a quick badly laid out report in Dick's db based on the same query & added coloured fills to three of the fields.
The background & border are displayed when the values are null.
However, I've no idea what makes that behaviour different in the original report
I see the difference why mine didn't work and yours did. You used colour and I used color. . Maybe I need to buy my next computer in the UK.
Dick7Access is offline   Reply With Quote
Old 10-27-2019, 12:01 PM   #13
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,382
Thanks: 115
Thanked 3,114 Times in 2,831 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: empty field in eepoet

LOL
I suggest you try and make a new report or just adapt the one I made to make it look like what you want.
I'm fairly sure there was some kind of corruption involved ... or an obscure setting on your computer.
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)
isladogs is offline   Reply With Quote
Old 10-27-2019, 12:25 PM   #14
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,479
Thanks: 0
Thanked 578 Times in 574 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: empty field in eepoet

Okay, so something has gone haywire with specific report. I just tested by creating a new report with same data and it works fine. So it's not an app or computer setting.

I copied existing textbox on report and bound to field with some nulls. This textbox loses border and color when Null. I create a new textbox from ribbon or field list and it always has border and color. Also, this new textbox has same height setting as existing but actually renders as a fraction taller. When I push footer section up against textboxes, bottom border is lost on new control, not old ones. Same things happen with Dick's db.

Database started life in Access 2007. No idea if this behavior exhibited in 2007.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by June7; 10-27-2019 at 02:11 PM.
June7 is online now   Reply With Quote
Old 10-27-2019, 12:41 PM   #15
Dick7Access
Dick S
 
Dick7Access's Avatar
 
Join Date: Jun 2009
Location: Belleview, FL
Posts: 3,499
Thanks: 608
Thanked 41 Times in 39 Posts
Dick7Access is on a distinguished road
Re: empty field in eepoet

Quote:
Originally Posted by isladogs View Post
LOL
I suggest you try and make a new report or just adapt the one I made to make it look like what you want.
I'm fairly sure there was some kind of corruption involved ... or an obscure setting on your computer.
No need to. I have a finished version. (see attachment) Originally when I saw that the report was not putting colors in nulls, and even with color, empty fields where miss leading to the eye and made it a bit more difficult to read, (and thinking that was normal) I decided to see if I could get VBA or a query to add N/A to all the nulls. When I wasnít able, probable due to a lack of knowledge, I started looking for a different way. Thatís when I came up with instead of trying to put N/A in nulls after the fact I remembered that I could put N/A in before the fact and put N/A in all the [default values] Now my doctor will not think I forgot a an entry someplace when she sees a N/A. Now how to convince my doctor that it was necessary for me to eat some of the so-called forbidden foods is another problem, but even VBA and queries canít fix that.
Attached Files
File Type: zip DicksvitalsVer1_1.zip (105.8 KB, 9 views)

Dick7Access is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] If the field is empty, use a different field as a data source frankt68 Forms 3 01-05-2018 02:08 AM
If you forget any field is empty , MsgBox alerts the forgotten field Falcon88 Forms 3 10-11-2015 07:34 PM
When is an empty field in a table not really empty? psu_1995 Modules & VBA 3 01-30-2008 03:04 AM
Empty Field - but NOT really halem2 Tables 8 10-18-2007 12:06 PM
Empty Field Lyncroft Forms 4 01-15-2003 12:29 AM




All times are GMT -8. The time now is 02:09 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World