RE: VBA Syntex Question

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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 " ".

--
HTH

Mr B
draccess at askdoctoraccess.com


"thebiggermac via AccessMonster.com" wrote:

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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/200703/1


.

Relevant Pages

  • Importing from Excel to Access
    ... a temporary table in the database, ... queries to populate the live tables. ... I need to import data from Excel into an Access ... >Do I need VBA to do this? ...
    (microsoft.public.access.externaldata)
  • Which is better - queries or sub/Function
    ... I need one advice to select in between queries or vba. ... I have to prepare one report on basis of results that I have got.Tables ... Database A It gets the results from Database B and give Report Preview ...
    (microsoft.public.access.modulesdaovba)
  • Re: Run query in another database
    ... I would ask in the VBA forum for details. ... If the remote database is Access, ... Do the queries return data or are they action queries? ...
    (microsoft.public.access.queries)
  • Queries VS VBA Design
    ... My partner and I developed an access database using queries instead of VBA ... database and the shared records also being accessed by VBA written database? ...
    (microsoft.public.access.tablesdbdesign)
  • VBA versus Query
    ... I have many multiple queries that are required to ... Should I have the "queries" execute on the forms/reports themselves using ... Should I rework my database design to improve effeciency ... Right now a particular form may refer to a query that refers to multiple ...
    (comp.databases.ms-access)