Update query to clean "N/A"s in exporteddata

[Frank]

Hi folks,
I'm exporting a data file from access 2000 with a some "N/A"s in empty
fields. The import works fine but I need to clean the data. Problem is the
N/A's are all over the and if you speciify multiple fields in the UPDATE
query it will only update those records who have all fields as N/A. This prob
means i'd need to declare a var and index through the field names in the
query. Is there anyway around this? Maybe using a simple (non-query) function
I'm not aware of to scroll through the records and change the values. Thanks

CurrentDb.Execute "UPDATE [Asset and SLN Data] SET [Orig Notional] = ''
WHERE [Orig Notional] = 'N/A'", dbFailOnError
DoCmd.TransferText acExportDelim, "AssetSLN", "Asset and SLN Data",
exportPathName, False

 

[John Spencer]

UPDATE [Asset and SLN Data]
SET [Orig Notional] = IIF([Orig Notional] = 'N/A', '', [Orig Notional])
, NextField = IIF([NextField] = 'N/A', '', [NextField])
,...
WHERE [Orig Notional] = 'N/A'
Or NextField = 'N/A'
Or ...

 

[strive4peace]

Hi Frank,

you need to cycle through fields and substitute the
fieldnames in your SQL

you might possibly be able to use search and replace, but I
can't give you a VBA example of that

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[Frank]

Thanks to both of you for your replies.
John, this works great (I just tried a couple fields in the SQL editor and
it does the job just fine). Only problem is that I have 4 tables with about
20 fields each that will have to be coded in VBA.

What would you guys recommend to be able to cycle through the fields in the
query? I've been lookinf for an object method that I can index and will
return the tablenames and fieldnames

 

[strive4peace]

Hi Frank,

here is some code to cycle through all text fields in all tables

'~~~~~~~~~~
Sub ChangeNA()
'NEEDS REFERENCE
'Microsoft DAO Object Library

Dim db As DAO.Database, tdf As DAO.TableDef
Dim s As String, fld As DAO.Field

Set db = CurrentDb

For Each tdf In db.TableDefs
If Left(tdf.Name, 4) <> "Msys" Then
For Each fld In tdf.Fields
If fld.Type = 10 Then
s = "UPDATE [" & tdf.Name _
& "] SET [" & fld.Name & "] = null " _
& " WHERE [" & fld.Name & "] = 'N/A';"
CurrentDb.Execute s
Debug.Print tdf.Name, fld.Name
End If
Next fld
End If
Next tdf

Proc_Exit:
On Error Resume Next
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing

End Sub
'~~~~~~~~~~~~~~~~~~~

I set it to NULL instead of an empty string...


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[Frank]

Crystal,
It works beautifully -- I literally plopped it in and it worked (made me do
a double take) -- I have a lot to learn about DAO objects. Thanks a million.
Quick question: If Left(tdf.Name, 4) <> "Msys" Then -- on this line I
imagine you're asking to overlook system generated tables -- is it
recommended to always overlook these files.
and If fld.Type = 10 Then -- I saw there were all types of constants you
can set the field type to; 10 wasn't defined, I imagine it's for text?
Thanks again!
Frank

 

[strive4peace]

you're welcome, Frank

10 is for text -- here is a little function to convert the
numbers to their text equivalents -- I mostly use it to look
up the numbers It is not complete, but this is all the
data types that I ever use

'~~~~~~~~~~~~~~~~~~~~~~~~`
Function GetDataType(pDatType) As String
Select Case pDatType
Case 1: GetDataType = "Boolean"
Case 2: GetDataType = "Byte"
Case 3: GetDataType = "Integer"
Case 4: GetDataType = "Long"
Case 5: GetDataType = "Currency"
Case 6: GetDataType = "Single"
Case 7: GetDataType = "Double"
Case 8: GetDataType = "Date"
Case 10: GetDataType = "Text"
Case 12: GetDataType = "Memo"
case else: GetDataType = format(nz(pDatType),"0")
End Select
End Function
'~~~~~~~~~~~~~~~~~~~~~~~~`

imagine you're asking to overlook system generated tables

-- is it recommended to always overlook these files. "

YES! You should not change the system tables


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[strive4peace]

btw, after you run the routine, you can open the debug
window and see which tables/fields it ran SQL on

CTRL-G --> Goto debuG window


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[strive4peace]

Hi Frank,

here are some valuable tips on learning more using Help

***
to find out more about references...
***

press F2 in a code window to View the Object Browser

change the library to "VBA" (for instance, instead of <all
libraries>) and look at the classes (categories) of
functions -- click on a class and then click on a function
in the right pane. To get the help for that function, press F1

The VBA library is the most basic library and a great place
to start exploring.

to lookup properties and methods for different objects like
forms, tabledefs, etc, change the library to Access

change to the DAO library for DAO recordsets

explore the different libraries you have to pick from and
see what is available in each

when you are in the Object Browser window, the library that
each function/class is a member of is shown in the lower
left corner of the window

the Object Browser is a GREAT way to learn

when you have an object selected, press F1 to get help.

on DAO: the Microsoft 2.5/3.51 Compatibility Library has
more built-in help than other DAO libraries I have used --
it is what was standard with Access 97. IF you have it,
this is a great library to look up help with in the Object
Browser as the later DAO libraries don't always have help on
everything.

from Tools, References...
you can select a library and then use the Object Browser to
see what it has in it...

***

another great way to learn is to press F1 on keywords in a
module sheet

ie: OpenForm, OpenReport, CASE, any built-in function such
as DateDiff, Format, etc
-- that gets you immediately to the help for that topic
without having to navigate
-- just use the Windows taskbar to switch between the module
and help windows

***
You can also press F1 in any property on a property sheet
I highly recommend you learn more about the events, what
triggers them, and the order they happen.
***

You can also press SHIFT-F1 while in the design view of a
form or report (for instance)
this turns your mouse into a pointer with a question mark --
click on any tool or menu item to get information instead of
selecting while in that mode
***

keep in mind that any menu option with [...] after it will
have a dialog box with a CANCEL button
-- so explore!

***
and then, despite how simple it seems -- start reading help
from the beginning of the table of contents -- it really is
quite interesting.

Learn about collections, objects, properties, methods,
events ...

Object-oriented programming is a whole different way of
thinking than sequential programming

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ GRAPH help

from the design view of any module, link to the Graph Library

from the menu --> Tools, References
check Microsoft Graph #.# Object Library

from the menu --> View, Object Browser
OR
F2

On the left, you will probably see a Project window and a
Properties window below it

On the right, you will see the main Object Browser window

change <All Libraries> in the Project/Library combobox in
the upper left of the Object Browser window
to
Graph

explore each object in Classes
as you select a Class on the left, its members will appear
in the pane on the right

when you see something you want help on, press the F1 key
and switch to the Help window


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[Frank]

Crystal,
I wrote you along reply but it wen to bit heabven. Thanks for all the info.
You had a debug.print statement and I saw all the fields come up. It saved me
a lot of work.
The Graph objeect library is interetsing and seems to be for exchel charts
and graphs. My projects going to be mainly reports -- 30 of them that need to
be printed "en masse" and I'mm hoping that I only need to creat 1 or 2
templates and pass all the information to the unbounded text boxes in the
reports. We'll see how far I get. At this point I'm still having problems
figuring out how to pass a query string to the reports in an easy manner and
seem to be stuck on:


Dim objConn As ADODB.Connection
Dim objRS As ADODB.Recordset
Dim strSQL As String

Set objConn = New ADODB.Connection
objConn.CursorLocation = adUseClient
objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=Z:\alm\ALM\TWS\TWSCOREFILES\Aegis Financing LLC\TestFolder-DO NOT
USE\Test2\consolidated_pipe_feedbu.mdb; Persist Security Info=False;"
objConn.Open

'* get the data
strSQL = "SELECT TOP 5 [Orig Notional] " _
& "FROM [Asset and SLN Data] " _
& "WHERE [Trade TypeID] = 'TRV' " _
& "ORDER BY [Orig Notional] Desc " _ ;"

Set objRS = New ADODB.Recordset
objRS.Open strSQL, objConn
' once i have the sql string, not sure how to pass it directly to reports.

Thanks again for all you help and have a great evening.

 

[strive4peace]

Hi Frank,

you're welcome

the debug window, also called the immediate window, is
another good resource. When you are executing code, you can
query the value of any variable, field, control, ...

? pSQL
and then press ENTER

You can also use the debug window to get help on a topic --
type or paste a keyword into the window and press F1

I can't help with that method, but here is another way ...

'~~~~~~~~~~~~~~~~~~~~~~~~~~
Sub SetRecordSource(_
ByVal pReportName As String, _
ByVal pSQL As String)

' written by Crystal
' strive4peace2006 at yahoo.com

' PARAMETERS:
' pReportName is the name of your report
' pSQL is an SQL string or tablename or queryname

' USEAGE:
' SetRecordSource "MyReportname","QueryName"
' SetRecordSource "MyAppointments", _
"SELECT * FROM Addresses WHERE City='Denver';"

On Error GoTo err_proc
Dim rpt As Report

'you can remove these lines once everything works ok
debug.print pReportName & " --> "
debug.print pSQL


'this turns off the screen updating
DoCmd.Echo False

DoCmd.OpenReport pReportName, acViewDesign
Set rpt = Reports(pReportName)
rpt.RecordSource = pSQL
DoCmd.Save acReport, pReportName
DoCmd.Close acReport, pReportName
Set rpt = Nothing
DoCmd.Echo True

'if you want to open report now, remove comment
'DoCmd.OpenReport pReportName, acViewPreview

'if you want to use the WHERE parameter
'to further filter the report
'DoCmd.OpenReport pReportName, _
acPreview, , "condition"

Exit Sub

err_proc:

MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " SetRecordSource"

DoCmd.Echo True
'press F8 to step thru lines and fix problem
Stop
Resume

End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

Crystal,
I wrote you along reply but it wen to bit heabven. Thanks for all the info.
You had a debug.print statement and I saw all the fields come up. It saved me
a lot of work.
The Graph objeect library is interetsing and seems to be for exchel charts
and graphs. My projects going to be mainly reports -- 30 of them that need to
be printed "en masse" and I'mm hoping that I only need to creat 1 or 2
templates and pass all the information to the unbounded text boxes in the
reports. We'll see how far I get. At this point I'm still having problems
figuring out how to pass a query string to the reports in an easy manner and
seem to be stuck on:


Dim objConn As ADODB.Connection
Dim objRS As ADODB.Recordset
Dim strSQL As String

Set objConn = New ADODB.Connection
objConn.CursorLocation = adUseClient
objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=Z:\alm\ALM\TWS\TWSCOREFILES\Aegis Financing LLC\TestFolder-DO NOT
USE\Test2\consolidated_pipe_feedbu.mdb; Persist Security Info=False;"
objConn.Open

'* get the data
strSQL = "SELECT TOP 5 [Orig Notional] " _
& "FROM [Asset and SLN Data] " _
& "WHERE [Trade TypeID] = 'TRV' " _
& "ORDER BY [Orig Notional] Desc " _ ;"

Set objRS = New ADODB.Recordset
objRS.Open strSQL, objConn
' once i have the sql string, not sure how to pass it directly to reports.

Thanks again for all you help and have a great evening.

 

[Frank]

Crystal,
Thanks again, this is useful and I got it to run when I manually created a
report with pReportName first, otherwise it gave me an error that the
pReportName didn't exist.
I found the CreateReport method which looks like it will create a report
based on a report template that you give it, but I don't know how you can
reference the fields from a query.
Thanks for your code. I will prob create another post (and I'll be sure and
credit you for your code!)

 

[strive4peace]

you're welcome, Frank

"...I manually created a report with pReportName first ..."

you didn't NAME your report that, did you? btw, I use "p" in
code to indicate it is a passed parameter


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*