Appending record with VBA when surname is True(sic)

[Peter Jamieson]

My code populates an Access table with data from an Excel spreadsheet.
Recently when reviewing some surnames in the Access table I noticed a record
of "-1" in the Surname field. On checking back I discovered the offending
surname derived from a Mr S True in the Excel sheet.
How can I get Mr True's surname to come across as True and not "-1" ?
I haven't come across a Mr False yet but I guess the same problem would
arise, so too for possibly other surnames which are reserved or special
words in Access. Maybe there is a general way of handling these cases?
Any advice appreciated!
Cheers, PJ

 

[Steve Schapel]

PJ,

This is most peculiar. Could you share with us the method you are using
to get the Excel data into your Access table?

 

[onedaywhen]

...

My code populates an Access table with data from an Excel spreadsheet.
Recently when reviewing some surnames in the Access table I noticed a record
of "-1" in the Surname field. On checking back I discovered the offending
surname derived from a Mr S True in the Excel sheet.

LOL! I had a quick play with this but could not reproduce. Perhaps
it's your code e.g. a variable of type Variant with VBA doing some
implicit coercing?

--

 

[dandgard]

Are you importing the data from an excel spreadsheet. When acces
imports a spreadsheet it reads the first record of the spreadsheet an
assigns data types based on the information it encounters. If there i
a number in the field then it will assign the field as integer or real
what you need to do is make sure that the field in question i
imported as a text type not a boolean. Sometimes this requires that yo
massage the data a little to make sure the data comes across in th
proper fashion. I take it True was in a column by itself.

If you are reading directly from cells in the Excel spreadsheet the
you need to make sure your variables are properly typecast

 

[dandgard]

Are you importing the data from an excel spreadsheet. When acces
imports a spreadsheet it reads the first record of the spreadsheet an
assigns data types based on the information it encounters. If there i
a number in the field then it will assign the field as integer or real
what you need to do is make sure that the field in question i
imported as a text type not a boolean. Sometimes this requires that yo
massage the data a little to make sure the data comes across in th
proper fashion. I take it True was in a column by itself.

If you are reading directly from cells in the Excel spreadsheet the
you need to make sure your variables are properly typecast

 

[onedaywhen]

dandgard wrote ...

When access
imports a spreadsheet it reads the first record of the spreadsheet and
assigns data types based on the information it encounters.

It's not as simple as that! Standard answer number 4:

The relevant registry keys (for Jet 4.0) are in:

Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/

The ImportMixedTypes registry key is always read. You can test this by
changing the key to ImportMixedTypes=OneDayWhen and trying to use the
ISAM: you get the error, 'Invalid setting in Excel key of the Engines
section of the Windows Registry.' The only valid values are:

ImportMixedTypes=Text
ImportMixedTypes=Majority Type

Data type is determined column by column.

ImportMixedTypes='Majority Type' means a certain number of rows (more
on this later) in each column are scanned and the data types are
counted. Both a cell's value and format are used to determine data
type. The majority data type (i.e. the one with the most rows) decides
the overall data type for the entire column. There's a bias in favor
of numeric in the event of a tie. Rows from any minority data types
found that can't be cast as the majority data type will be returned
with a null value.

For ImportMixedTypes=Text, the data type for the whole column will be:

Jet (MS Access UI): 'Text' data type
DDL: VARCHAR(255)
ADO: adWChar ('a null-terminated Unicode character string')

Note that this is distinct from:

Jet (MS Access UI): 'Memo' data type
DDL: N/A
ADO: adLongVarWChar ('a long null-terminated Unicode string value')

ImportMixedTypes=Text will curtail text at 255 characters when 'Memo'
is cast as 'Text'. For a column to be recognized as 'Memo', majority
type must be detected, meaning the majority of rows detected must
contain 256 or more characters.

But how many rows are scanned for each column before is decided that
mixed types exist? There is a second registry Key, TypeGuessRows. This
can be a value from 0-16 (decimal). A value from 1 to 16 inclusive is
the number of rows to scan. A value of zero means all rows will be
scanned.

In summary, use TypeGuessRows to get Jet to detect whether a 'mixed
types' situation exists or use it to 'trick' Jet into detecting a
certaint data type as being the majority type. In the event of a
'mixed types' situation being detected, use ImportMixedTypes to tell
Jet to either use the majority type or coerce all values as 'Text'
(max 255 characters).

--

 

[Peter Jamieson]

G'day Steve, onedaywhen,dandgard,
Many thanks to you all for your input and suggestions!
I have gone back to the db and yes found a Surname "False" has been imported
as 0 ie zero and "True" imports as -1. I have re-run my code and get these
same values imported for the names.

My data arrives over the web, I parse it into columns in a temporary
worksheet then use the following to get it into Access:
(code snippet)
With wshTemp
DBFullName = "C:\Databases\db3.mdb"
Set dbsCurrent = OpenDatabase(DBFullName)
TableName = "Table3"
Set rs = dbsCurrent.OpenRecordset(TableName, dbOpenTable)
mRows = cntData
mCols = 3
On Error Resume Next
For mr = 2 To mRows
With rs
.AddNew
For mc = 1 To mCols
.Fields(Cells(1, mc)) = Cells(mr, mc).Value
Next mc
.Update
End With
Next mr
dbsCurrent.Close
Set dbsCurrent = Nothing
Set rs = Nothing
End With

In Excel the columns are formatted as Text, the fields in Access are defined
as Text.
The True or False names are not at the head of any column of imported data
and are very infrequent, 9 cases in approx 100K records.
My code defines the relevant variables used in the parsing process as
String.
What seems peculiar is "True" coming across as -1, "False" as 0.....does
that suggest any clues to any of you?....in this schema what surname would
then generate a 1 ?
Perhaps someone can try a parse/import via code of some names like I R True,
I R False etc to see what they get.
BTW, I'm using Office xp Pro, up to date with all MS downloads as of
yesterday.
Thanks again for the assistance. The instances are rare enough so that I can
go into the table and manually edit them but it would be nice to know what
is going on!
Cheers, Peter J.

 

[onedaywhen]

Try replacing:

.Fields(Cells(1, mc)) = Cells(mr, mc).Value

with

.Fields(Cells(1, mc)).Value = CStr(Cells(mr, mc).Value)

To demo what might be going on, try this in the Excel VBE Immediate Window:

? true
True
? CStr(true)
True

The results looks the same, right? But now try this:

? typename(true)
Boolean
? typename(CStr(true))
String

--

 

[Steve Schapel]

Peter,

As you possibly know, the type of thing which is called a macro in Excel
is called a VBA procedure in Access, where an Access macro is a very
different creature altogether. To achieve your purpose within Access,
either with a macro or with a vba procedure, would be relatively simple,
possibly involving a TransferSpreadsheet action followed by running an
Append Query. Your method of doing this from within Excel is in essence
based on processing the data cell by cell, so I suppose data types are
assessed cell by cell, rather than column by column, in which case the
True and False names are being evaluated as being Boolean rather than
Text type, and therefore assuming the values -1 and 0 as you would
expect for Boolean data. I would like to know if the same would apply
if you had people named Yes or No or On or Off. Still, the result you
are getting is not what I would have expected, and it is fascinating. I
am passing the information along.

 

[Steve Schapel]

Peter,

Excel MVP Bill Manville has suggested trying changing the code to...
.Fields(Cells(1, mc)) = CStr(Cells(mr, mc).Value)

I understood from your earlier comments that this was not the case, but
can I just confirm... Are any of the True or False records within the
first 8 rows of the worksheet data?

 

[Peter Jamieson]

G'day onedaywhen,
Your solution did the trick!!....greatly appreciate your assistance
and interest.....6-pack of cyber-beer sent!....Cheers, Peter J.

 

[Peter Jamieson]

G'day Steve,
Thanks for your interest and suggestions. The fix you suggested via Bill
Manville was also proposed by "onedaywhen" and it worked a treat.
Much appreciated!
Yes some of the records were in the first 8 rows of the worksheet data and
some were not. No sheet had more than 20 records by the way mostly 6 to 16.
I will try your suggestions for No, Yes, On and Off as surnames and let you
know the outcome.
Reminds me of a celebrated case about 10 years ago(? memory!) I think in New
York where a certain Mr O I think his name was, a Korean guy, who was having
a devil of a time with banks and bureaucracy generally who's computerized
systems just could not deal with someone with a single character name. I
haven't checked the Sydney directory for a surname of Yes or No but there is
definitely no Ms or Mr Right....go figure!!
Cheers and thanks again for your help Steve, Peter J.

 

[Steve Schapel]

Peter,

Pleased to hear that you've got a workable solution now.

By the way, Access MVP John Nurick has suggested this an another option:
.Fields(Cells(1, mc)) = Cells(mr, mc).Formula

 

[onedaywhen]

Steve Schapel ...

Access MVP John Nurick has suggested this an another option:
.Fields(Cells(1, mc)) = Cells(mr, mc).Formula

Tell John Nurick this could end up being worse! What if the cell
contents are e.g.

=IF(B3="","{{NA}}",B3)

The OP wants the Value not the Formula. Even better perhaps than Value
is Text, being the Value after any cell formatting has been applied
e.g.

.Fields(Cells(1, mc)).Value = CStr(Cells(mr, mc).Text)

--