<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2722.900" name=GENERATOR>
<STYLE>@font-face {
font-family: Wingdings;
}
@font-face {
font-family: Tahoma;
}
@page Section1 {size: 612.0pt 792.0pt; margin: 72.0pt 90.0pt 72.0pt 90.0pt; }
P.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0cm 0cm 0pt; FONT-FAMILY: "Times New Roman"
}
LI.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0cm 0cm 0pt; FONT-FAMILY: "Times New Roman"
}
DIV.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0cm 0cm 0pt; FONT-FAMILY: "Times New Roman"
}
H2 {
FONT-SIZE: 14pt; MARGIN: 12pt 0cm 3pt; FONT-STYLE: italic; FONT-FAMILY: Arial
}
H3 {
FONT-SIZE: 13pt; MARGIN: 12pt 0cm 3pt; FONT-FAMILY: Arial
}
H4 {
FONT-SIZE: 12pt; MARGIN: 3pt 0cm; FONT-FAMILY: Arial
}
A:link {
COLOR: blue; TEXT-DECORATION: underline
}
SPAN.MsoHyperlink {
COLOR: blue; TEXT-DECORATION: underline
}
A:visited {
COLOR: purple; TEXT-DECORATION: underline
}
SPAN.MsoHyperlinkFollowed {
COLOR: purple; TEXT-DECORATION: underline
}
P.Code {
FONT-SIZE: 12pt; MARGIN: 6pt 0cm 6pt 17.85pt; FONT-FAMILY: "Courier New"; punctuation-wrap: simple
}
LI.Code {
FONT-SIZE: 12pt; MARGIN: 6pt 0cm 6pt 17.85pt; FONT-FAMILY: "Courier New"; punctuation-wrap: simple
}
DIV.Code {
FONT-SIZE: 12pt; MARGIN: 6pt 0cm 6pt 17.85pt; FONT-FAMILY: "Courier New"; punctuation-wrap: simple
}
SPAN.EmailStyle18 {
COLOR: navy; FONT-FAMILY: Arial
}
DIV.Section1 {
page: Section1
}
</STYLE>
</HEAD>
<BODY lang=EN-US vLink=purple link=blue>
<DIV><SPAN class=477551105-12022003><FONT face=Arial color=#0000ff size=2>I
second that motion. I tend to write ADO with using the field position,
versus the name. Thus, when opening the recordset, I use SQL WITHOUT an
asterick, to make sure the fields are in the right order. If I do use an
asterick, for whatever reason, or I open the table directly (Ya, I do that once
in a while...<VBEG>), I try to use the field names if possible. That
allows for less things to break, when someone gets fidgety with my fields.
</FONT></SPAN></DIV>
<DIV><SPAN class=477551105-12022003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=477551105-12022003><FONT face=Arial color=#0000ff size=2>Of
course, I usually don't let anyone in my backends, so it's rarely an
issue....<VBG> </FONT></SPAN></DIV>
<DIV><SPAN class=477551105-12022003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=477551105-12022003><FONT face=Arial color=#0000ff
size=2>Drew</FONT></SPAN></DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B> Arthur Fuller
[mailto:artful@rogers.com]<BR><B>Sent:</B> Saturday, February 08, 2003 9:49
AM<BR><B>To:</B> accessd@databaseadvisors.com<BR><B>Subject:</B> RE: [AccessD]
Learned Something Today<BR><BR></FONT></DIV>
<DIV class=Section1>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">To which I would add,
this is a risk of SELECT *. Downstream you may one day decide to change the
structure of one of the tables, inserting a new column. This would break any
UNION statement, since the tables would no longer match. </SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"></SPAN></FONT> </P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">I have learned the
hard way to avoid SELECT * like the plague. Ask for exactly what you want, and
ye shall get it </SPAN></FONT><FONT face=Wingdings color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Wingdings">J</SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"></SPAN></FONT> </P>
<P class=MsoNormal style="MARGIN-LEFT: 36pt"><FONT face=Tahoma size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Tahoma">-----Original
Message-----<BR><B><SPAN style="FONT-WEIGHT: bold">From:</SPAN></B>
accessd-admin@databaseadvisors.com [mailto:accessd-admin@databaseadvisors.com]
<B><SPAN style="FONT-WEIGHT: bold">On Behalf Of
</SPAN></B>Hollis,Virginia<BR><B><SPAN
style="FONT-WEIGHT: bold">Sent:</SPAN></B> February 8, 2003 9:46
AM<BR><B><SPAN style="FONT-WEIGHT: bold">To:</SPAN></B>
'accessd@databaseadvisors.com'<BR><B><SPAN
style="FONT-WEIGHT: bold">Subject:</SPAN></B> [AccessD] Learned Something
Today</SPAN></FONT></P>
<P class=MsoNormal style="MARGIN-LEFT: 36pt"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 36pt"><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">I learned something today that
most of you probably already know, but just in case I thought I would pass it
along - especially since it took me 2 days to figure out what I was doing
wrong.</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 36pt"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 36pt"><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">I created a Union query using
SELECT * from both tables. The Union query worked when I viewed it, but when I
tried using it to create a report & joined it to the evaluator
table, I kept getting a type mismatch in join. The main table had the
EvaluatorID as a number & the Evaluator table had EvaluatorID as
autonumber (the tables were joined by EvaluatorID). So there should not have
been any problem. All the other joins worked fine.</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 36pt"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 36pt"><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">So after eliminating all the
possible combinations and trying different tables I compared the 2 tables used
in the Union to see if one of the fields might have been wrong. Here is where
the learning comes in! The table fields have to be in the same Order. In one
table I had EvaluatorID listed 3rd in the list & the other it was
2nd.</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 36pt"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 36pt"><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Another way I found this is that I
switched the order of the tables in the query, SELECT * from table 1 UNION ALL
table 2. When I did SELECT from table 2 UNION ALL table 1, I had text in the
EvaluatorID field.</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 36pt"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 36pt"><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Just thought I would pass that
along.</SPAN></FONT></P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 36pt"><FONT face="Times New Roman"
size=3><SPAN style="FONT-SIZE: 12pt"></SPAN></FONT> </P></DIV>
<DIV>
<P class=MsoNormal style="MARGIN-LEFT: 36pt"><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Virginia</SPAN></FONT></P></DIV></DIV></BLOCKQUOTE></BODY></HTML>