Importing Messy Data

G

Guest

I'm importing files into Access from JD Edwards using a Showcase query.
I've had success in getting most of the data cleaned up by using trim in my
VBA code, however, I still have additional criteria to meet.
#1) In addition to importing the records with storenum 700 through 6900, I
only need the records with specific RegDesc(riptions). I tried using <>
Closed/Sold/To Be Opened AND <> " ." spaces with a period.
I also tried to use = South Region, North Region, Main Region ....

What is the syntext required to use multiple criteria in VBA for Access. I
keep getting error messages when I try to add additional criteria to the code
below:


DoCmd.RunSQL "INSERT INTO tblStoreList ( costcenter, storenum, Div,
RegDesc, AreaDesc, DivisionDesc, STATEDESC, storename, divsht )" _
& "SELECT trim(tblStoreList2.costcenter), trim(tblStoreList2.storenum),
trim(tblStoreList2.Div), trim(tblStoreList2.RegDesc),
trim(tblStoreList2.AreaDesc)," _
& "trim(tblStoreList2.DivisionDesc), trim(tblStoreList2.STATEDESC),
trim(tblStoreList2.storename), trim(tblStoreList2.divsht)" _
& " FROM tblStoreList2 WHERE storenum > 700 and storenum < 6901;"
 
G

Guest

I would start by creating a Select query, then pasting that query in the
Append query.

So first create a new query, switch to SQL view and paste in the following:

SELECT trim(tblStoreList2.costcenter), trim(tblStoreList2.storenum),
trim(tblStoreList2.Div), trim(tblStoreList2.RegDesc),
trim(tblStoreList2.AreaDesc), trim(tblStoreList2.DivisionDesc),
trim(tblStoreList2.STATEDESC), trim(tblStoreList2.storename),
trim(tblStoreList2.divsht) FROM tblStoreList2 WHERE [storenum] between 700
and 6900 ORDER BY [storenum];


If this reutrns the expected records, then change the WHERE clause. I'm not
sure what the string is that you don't want in field [RegDesc], or what field
"South Region" is in, but it could look something like this:

WHERE [storenum] between 700 and 6900 AND ([RegDesc] Not Like "Closed*" OR
[RegDesc] Not Like "Sold*")

Again, if this reutrns the expected records, then change the WHERE clause
again.

Repeat until all of the driteria is added to the query.

Once you have the select query working (returning the expected records) with
all of the criteria, then paste the SQL into the Append query.

Ta-Da!! Done :)

If you need more help, post back with more info on field names and data you
want/ don't want to include.

HTH
 
Top