VBA Syntex Question

[thebiggermac via AccessMonster.com]

My company inherited a database that has numerous blank data fields. In order
to make the project work correctly the blank field(s) was populated with the
word "None". This had the desired effect and the project screamed along
nicely.

Now the powers that be want the word "None" replaced with a blank. The
problem is that removing the word causes the project to fail. By failure I
mean when running the query(s) it skips data (the blank fields) which in turn
produces erroneous reports (a lot of data is missing). In Access we are
getting around the issue with conditional text formatting (Access 2003, we
literally white out the text). BUT the data is exported to Word. When that
happens the word "None" appears in the Word doc.

The missing data (what we populated with the word "None") is generally
located in one specific field (about 98% of the time), which is why it seems
to me that the solution is to replace the word "None" with a null value, such
as a blank space. I know this can be done via VBA. Most likely the code would
be in a module and available as Public to the entire database to affect the
odd fields where data is also missing. I'm almost positive a Private_sub
won't work (but I could be wrong). It may be that I have to create both. I’m
not sure.

We first tried to correct the issue using queries but with no luck. Hence I
believe that our only option is VBA code. The trouble is I don't have enough
knowledge of VBA to write the code to replace "None" with a null value (blank
space). Any help and/or suggestions would be most appreciative. Other
suggestions would also be appreciated.

 

[Guest]

With out being able to see the data and the structure of the database or the
queries that you refer to, it is really difficult to determine what the
problems are.

I sounds to me as if there are problems with the criteria in you queries
that provide the data to the report.

You did not say, but are you exporting the Report to Word or is there
another query that is used to do the export?

One thing that I would point out: You state, "it seems to me that the
solution is to replace the word "None" with a null value, such as a blank
space". Please be aware that there is a huge difference between a blank
character in a string (" "), an empty string value ("") and Null. Also be
aware that Access (sql) really knows the difference so you need to pay
peticular attention to how you refer to these values. This could be having
effect on your problems.

Although I seriously doubt that the fix for your issue is really centered
around the use of the word "None" in the field, ff replacing the "None" with
a blank space is really what you want to do, then VBA is not required to do
that.

You can simply use an update query and have it update the field with "None"
for all records to " ".

 

[thebiggermac via AccessMonster.com]

Thanks for the input. Sorry I have to be so vague but the data in question is
DOD sensitive so providing a complete history and database structure is not
an option. I will try the update query and see how that works.

With out being able to see the data and the structure of the database or the
queries that you refer to, it is really difficult to determine what the
problems are.

I sounds to me as if there are problems with the criteria in you queries
that provide the data to the report.

You did not say, but are you exporting the Report to Word or is there
another query that is used to do the export?

One thing that I would point out: You state, "it seems to me that the
solution is to replace the word "None" with a null value, such as a blank
space". Please be aware that there is a huge difference between a blank
character in a string (" "), an empty string value ("") and Null. Also be
aware that Access (sql) really knows the difference so you need to pay
peticular attention to how you refer to these values. This could be having
effect on your problems.

Although I seriously doubt that the fix for your issue is really centered
around the use of the word "None" in the field, ff replacing the "None" with
a blank space is really what you want to do, then VBA is not required to do
that.

You can simply use an update query and have it update the field with "None"
for all records to " ".

My company inherited a database that has numerous blank data fields. In order
to make the project work correctly the blank field(s) was populated with the

[quoted text clipped - 23 lines]

space). Any help and/or suggestions would be most appreciative. Other
suggestions would also be appreciated.

 

[Guest]

I understand.

One thing that I did think of is that if all you need to do is to get rid of
the "None" in the report and/or in the export to Word, you could create a new
field in your query the uses an IIF statement that says that if the value of
that field is "None" just make the value "" else use the value.

--
HTH

Mr B
draccess at askdoctoraccess.com

Thanks for the input. Sorry I have to be so vague but the data in question is
DOD sensitive so providing a complete history and database structure is not
an option. I will try the update query and see how that works.

With out being able to see the data and the structure of the database or the
queries that you refer to, it is really difficult to determine what the
problems are.

I sounds to me as if there are problems with the criteria in you queries
that provide the data to the report.

You did not say, but are you exporting the Report to Word or is there
another query that is used to do the export?

One thing that I would point out: You state, "it seems to me that the
solution is to replace the word "None" with a null value, such as a blank
space". Please be aware that there is a huge difference between a blank
character in a string (" "), an empty string value ("") and Null. Also be
aware that Access (sql) really knows the difference so you need to pay
peticular attention to how you refer to these values. This could be having
effect on your problems.

Although I seriously doubt that the fix for your issue is really centered
around the use of the word "None" in the field, ff replacing the "None" with
a blank space is really what you want to do, then VBA is not required to do
that.

You can simply use an update query and have it update the field with "None"
for all records to " ".

My company inherited a database that has numerous blank data fields. In order
to make the project work correctly the blank field(s) was populated with the

[quoted text clipped - 23 lines]

space). Any help and/or suggestions would be most appreciative. Other
suggestions would also be appreciated.

 

[thebiggermac via AccessMonster.com]

Thanks. I tried the update query but I'm having trouble finding/displaying
the records that are blank. In the database I am working with all the "None"
words have been discarded and I am left with only blank fields (where "None"
use to be, which I will now call FIELD_2). Now I'm trying to query those
records that are blank in FIELD_2. Data does exist in FIELD_1 so I want to
display FIELD_1 data along with a blank (empty, null, whatever, no data is
displayed) in FIELD_2. I'm having trouble finding the correct syntex to do
the job. So, to use and IFF statement, what would be the correct syntex?

I do appreciate your help.

I understand.

One thing that I did think of is that if all you need to do is to get rid of
the "None" in the report and/or in the export to Word, you could create a new
field in your query the uses an IIF statement that says that if the value of
that field is "None" just make the value "" else use the value.

Thanks for the input. Sorry I have to be so vague but the data in question is
DOD sensitive so providing a complete history and database structure is not

[quoted text clipped - 31 lines]

 

[Guest]

To display the records where Field_2 is "Blank" (this may actually be Null)
depending on you stated your update query.

Try using "Is Null" without the quotes in the criteria for Field_2 and see
if that will work. If not, then try ="" or = " "

I would thing that one of the criteria above would return the records where
the None was located.

--
HTH

Mr B
draccess at askdoctoraccess.com

Thanks. I tried the update query but I'm having trouble finding/displaying
the records that are blank. In the database I am working with all the "None"
words have been discarded and I am left with only blank fields (where "None"
use to be, which I will now call FIELD_2). Now I'm trying to query those
records that are blank in FIELD_2. Data does exist in FIELD_1 so I want to
display FIELD_1 data along with a blank (empty, null, whatever, no data is
displayed) in FIELD_2. I'm having trouble finding the correct syntex to do
the job. So, to use and IFF statement, what would be the correct syntex?

I do appreciate your help.

I understand.

One thing that I did think of is that if all you need to do is to get rid of
the "None" in the report and/or in the export to Word, you could create a new
field in your query the uses an IIF statement that says that if the value of
that field is "None" just make the value "" else use the value.

Thanks for the input. Sorry I have to be so vague but the data in question is
DOD sensitive so providing a complete history and database structure is not

[quoted text clipped - 31 lines]

space). Any help and/or suggestions would be most appreciative. Other
suggestions would also be appreciated.

 

[Keith Wilby]

Sorry I have to be so vague but the data in question is
DOD sensitive so providing a complete history and database structure is
not
an option.

That being the case then you shouldn't be using Access at all, it just isn't
secure.

Keith.
www.keithwilby.com

 

[thebiggermac via AccessMonster.com]

Sensitive, not critical or secret. Besides that it is really, really, boring
data. But it has to be tracked.

 

[thebiggermac via AccessMonster.com]

I tried using IsNull, IsEmpty, "", " ", and everything else I could think of.
I did successfully manage to really mess up the field with gibberish. So
maybe I made some headway. Good thing I'm working on a copy and not the
original. Still I'm having trouble locating and selecting only those fields
that are blank. I can say that field (FIELD_2) is set to 255 characters, but
the data is actually only 3 or 4 characters. Whoever set this project up
originally did not put any thought into it. Also the field is numbers only,
no text. It is also set to Double. Not sure that makes a difference though.

To display the records where Field_2 is "Blank" (this may actually be Null)
depending on you stated your update query.

Try using "Is Null" without the quotes in the criteria for Field_2 and see
if that will work. If not, then try ="" or = " "

I would thing that one of the criteria above would return the records where
the None was located.

Thanks. I tried the update query but I'm having trouble finding/displaying
the records that are blank. In the database I am working with all the "None"

[quoted text clipped - 19 lines]