I have a data type mismatch if I select "H", but "A" selects.

[Howard in Hammondsport]

I am using Access 2003. I have a field defined as text one position. It
will contain only "A" or "H", but this is not included in the properties. If
I query the table, I can select either value, and get a valid dataset. In
another query, using several files. I try to select by this field. If I try
"A". I get a valid dataset. If I try "R" or "T", I get an empty dataset. If
I select "H", I get a "Data type mismatch" error. I believe this identifies
the error as "Error 3464. I am running out of things to try. Any suggestions?

Thank you.

 

[xRoachx]

When you say "select" do you mean "Type"? Also, please post your SQL for the
query you are getting an error.

 

[Howard in Hammondsport]

When you say "select" do you mean "Type"? Also, please post your SQL for the
query you are getting an error.

By select, I mean I am entering in Criteria "A" (as shown in SQL) or other
value.

SQL:INSERT INTO VDavgfile ( VDavgscore, VDavgpuntnum, VDavgpuntyd,
VDavgretnum, VDavgretyd, VDavgintnum, VDavgintyd, VDavgpennum, VDavgpenyd,
VDavgfumb, VDavgfumblost, VDavgrushyd, VDavgrushnum, VDavgpassyd,
VDavgpasscomp, VDavgpassatt, VDavgsack, VDavgsackyd, VDavgteam,
VDavghomeaway, VDavgweek )
SELECT Avg(HGSdivide([gamefile2.gamescore],[oavgfile.oavgscore])) AS
Avgscore, Avg(HGSdivide([Gamefile2.Gamepuntnum],[oavgfile!oavgpuntnum])) AS
Avgpuntnum, Avg(HGSdivide([Gamefile2!Gamepuntyd],[oavgfile!oavgpuntyd])) AS
Avgpuntyd, Avg(HGSdivide(Gamefile2!Gameretnum,oavgfile!oavgretnum)) AS
Avgretnum, Avg(HGSdivide(Gamefile2!Gameretyd,oavgfile!oavgretyd)) AS
Avgretyd, Avg(HGSdivide(Gamefile2!Gameintnum,oavgfile!oavgintnum)) AS
Avgintnum, Avg(HGSdivide(Gamefile2!Gameintyd,oavgfile!oavgintyd)) AS
Avgintyd, Avg(HGSdivide(Gamefile2!Gamepennum,oavgfile!oavgpennum)) AS
Avgpennum, Avg(HGSdivide(Gamefile2!Gamepenyd,oavgfile!oavgpenyd)) AS
Avgpenyd, Avg(HGSdivide(Gamefile2!Gamefumb,oavgfile!oavgfumb)) AS Avgfumb,
Avg(HGSdivide(Gamefile2!Gamefumblost,oavgfile!oavgfumblost)) AS Avgfumblost,
Avg(HGSdivide(Gamefile2!Gamerushyd,oavgfile!oavgrushyd)) AS Avgrushyd,
Avg(HGSdivide(Gamefile2!Gamerushnum,oavgfile!oavgrushnum)) AS Avgrushnum,
Avg(HGSdivide(Gamefile2!Gamepassyd,oavgfile!oavgpassyd)) AS Avgpassyd,
Avg(HGSdivide(Gamefile2!Gamepasscomp,oavgfile!oavgpasscomp)) AS Avgpasscomp,
Avg(HGSdivide(Gamefile2!Gamepassatt,oavgfile!oavgpassatt)) AS Avgpassatt,
Avg(HGSdivide(Gamefile2!Gamesack,oavgfile!oavgsack)) AS Avgsack,
Avg(HGSdivide(Gamefile2!Gamesackyd,oavgfile!oavgsackyd)) AS Avgsackyd,
Teamfile.Teamnumber, Gamefile2.Gamehomeaway, Max(Weekfile.CurrentWeek) AS
MaxOfCurrentWeek
FROM (Teamfile INNER JOIN Gamefile2 ON Teamfile.Teamnumber =
Gamefile2.Gameteam) INNER JOIN (Weekfile INNER JOIN Oavgfile ON
Weekfile.CurrentWeek = Oavgfile.Oavgweek) ON Gamefile2.Gameopponent =
Oavgfile.Oavgteam
GROUP BY Teamfile.Teamnumber, Gamefile2.Gamehomeaway
HAVING (((Gamefile2.Gamehomeaway)="A"));

 

[Douglas J Steele]

What's the data type of Gamefile2.Gamehomeaway?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

When you say "select" do you mean "Type"? Also, please post your SQL for the
query you are getting an error.

By select, I mean I am entering in Criteria "A" (as shown in SQL) or other
value.

SQL:INSERT INTO VDavgfile ( VDavgscore, VDavgpuntnum, VDavgpuntyd,
VDavgretnum, VDavgretyd, VDavgintnum, VDavgintyd, VDavgpennum, VDavgpenyd,
VDavgfumb, VDavgfumblost, VDavgrushyd, VDavgrushnum, VDavgpassyd,
VDavgpasscomp, VDavgpassatt, VDavgsack, VDavgsackyd, VDavgteam,
VDavghomeaway, VDavgweek )
SELECT Avg(HGSdivide([gamefile2.gamescore],[oavgfile.oavgscore])) AS
Avgscore, Avg(HGSdivide([Gamefile2.Gamepuntnum],[oavgfile!oavgpuntnum])) AS
Avgpuntnum, Avg(HGSdivide([Gamefile2!Gamepuntyd],[oavgfile!oavgpuntyd])) AS
Avgpuntyd, Avg(HGSdivide(Gamefile2!Gameretnum,oavgfile!oavgretnum)) AS
Avgretnum, Avg(HGSdivide(Gamefile2!Gameretyd,oavgfile!oavgretyd)) AS
Avgretyd, Avg(HGSdivide(Gamefile2!Gameintnum,oavgfile!oavgintnum)) AS
Avgintnum, Avg(HGSdivide(Gamefile2!Gameintyd,oavgfile!oavgintyd)) AS
Avgintyd, Avg(HGSdivide(Gamefile2!Gamepennum,oavgfile!oavgpennum)) AS
Avgpennum, Avg(HGSdivide(Gamefile2!Gamepenyd,oavgfile!oavgpenyd)) AS
Avgpenyd, Avg(HGSdivide(Gamefile2!Gamefumb,oavgfile!oavgfumb)) AS Avgfumb,
Avg(HGSdivide(Gamefile2!Gamefumblost,oavgfile!oavgfumblost)) AS Avgfumblost,
Avg(HGSdivide(Gamefile2!Gamerushyd,oavgfile!oavgrushyd)) AS Avgrushyd,
Avg(HGSdivide(Gamefile2!Gamerushnum,oavgfile!oavgrushnum)) AS Avgrushnum,
Avg(HGSdivide(Gamefile2!Gamepassyd,oavgfile!oavgpassyd)) AS Avgpassyd,
Avg(HGSdivide(Gamefile2!Gamepasscomp,oavgfile!oavgpasscomp)) AS Avgpasscomp,
Avg(HGSdivide(Gamefile2!Gamepassatt,oavgfile!oavgpassatt)) AS Avgpassatt,
Avg(HGSdivide(Gamefile2!Gamesack,oavgfile!oavgsack)) AS Avgsack,
Avg(HGSdivide(Gamefile2!Gamesackyd,oavgfile!oavgsackyd)) AS Avgsackyd,
Teamfile.Teamnumber, Gamefile2.Gamehomeaway, Max(Weekfile.CurrentWeek) AS
MaxOfCurrentWeek
FROM (Teamfile INNER JOIN Gamefile2 ON Teamfile.Teamnumber =
Gamefile2.Gameteam) INNER JOIN (Weekfile INNER JOIN Oavgfile ON
Weekfile.CurrentWeek = Oavgfile.Oavgweek) ON Gamefile2.Gameopponent =
Oavgfile.Oavgteam
GROUP BY Teamfile.Teamnumber, Gamefile2.Gamehomeaway
HAVING (((Gamefile2.Gamehomeaway)="A"));

 

[Howard in Hammondsport]

What's the data type of Gamefile2.Gamehomeaway?

Sorry about taking so long to answer. I work usually with Firefox and
Thunderbird, and Microsoft doesn't like the way they work, so I have to do
some massaging to get back to you.

Gamehomeaway is a one-position text field; Allow Zero Length is False;
Attributes: Variable Length; Collating Order: General; Data Updatable: False;
Display Control: Text Box; Required: True.

I hope this is what you need.