Data Type Mismatch In Criteria Expression error in join query

Question

I'm attempting to join the query table qryJoinValidTXwithStationScaling to the basic table timezonesCombined to the make a single field TZfactor from the table available for a larger query. This seems so simple but I'm getting

data type mismatch in criteria expression

when I run the query.

Here's the SQL:

SELECT qryJoinValidTXwithStationScaling.EbiquityId,
       qryJoinValidTXwithStationScaling.AdTitle,
       qryJoinValidTXwithStationScaling.Brand,
       qryJoinValidTXwithStationScaling.Product,
       qryJoinValidTXwithStationScaling.TransmissionDate,
       qryJoinValidTXwithStationScaling.tTime,
       qryJoinValidTXwithStationScaling.Region,
       qryJoinValidTXwithStationScaling.nMedium,
       qryJoinValidTXwithStationScaling.[Scaling Factor],
       qryJoinValidTXwithStationScaling.ZoneLookup,
       timezonesCombined.TZFactor
FROM qryJoinValidTXwithStationScaling
  INNER JOIN timezonesCombined
    ON qryJoinValidTXwithStationScaling.ZoneLookup = timezonesCombined.[Zone#];

Hope someone can help, thanks in advance

Ever considered using table aliases? (E.g. INNER JOIN timezonesCombined AS tc ON q.ZoneLookup = tc.[Zone#].) — jarlh
What are data types of columns qryJoinValidTXwithStationScaling.ZoneLookup and timezonesCombined.[Zone#]? Looks like they are different, so Access cannot compare them properly. — Sergey S.
thanks @jarlh, I'm very new at Acces and SQL, but have started using alias in queries that exceed the build expression out of necessity, don't see how it would help in this scenario though? — Glenn Langford
@SergeyS. Both are numbers with no null values, TZfactor is to 4 decimals, Zonelookup is result of checking times in tTime are between Start and End for each [Zone#] and returning the number of the zone (integer from 1 to 11). Oddly, this query is not throwing the same error when I open it at home just now (Windows 10, Office 2016)(Work is WIndows7, Offfice 2013). However it is picking up the wrong values from [TZfactor]. I'll investigate further, thanks for your help so far. — Glenn Langford
Played around trying to get correct values from [TZfactor] and now it's throwing the Data type Mismatch error again. — Glenn Langford

Glenn Langford Glenn Langford · Accepted Answer · 2015-12-16T13:08:32

Solved it by going back a step to qryJoinValidTXwithStationScaling and using criteria on tTime for each Timezone (Between starttime And endtime ), then added correlating criteria for Zone# (1 thru 11) which returns correct TZfactor.

Data Type Mismatch In Criteria Expression error in join query

1 Answers