Union Query Promblem (1 Viewer)

[Richard M]

I have a union query that combines three queries. I'm using Access 2007. Every time I run it I get an error message "Data mismatch in criteria expression." I checked all queries and they work great.

1. Then I ran the union query with the first group (Select) and first Union Select. This worked great.
2. Then I ran the select and 2nd Union Select and that worked great.
3. Then I changed the first Union Select to Select and ran it with the last Union Select. This worked great.

I can't figure what I'm doing wrong. I built these before and never has a problem.

Can anybody look at this and guide me to what could cause this error message?

Thanks for the help,

Richard

 SELECT qryReport_ProjectSchedule_CommDates.CommYear, qryReport_ProjectSchedule_CommDates.ProjectId, qryReport_ProjectSchedule_CommDates.Comm_Jan, qryReport_ProjectSchedule_CommDates.Comm_Feb, qryReport_ProjectSchedule_CommDates.Comm_Mar, qryReport_ProjectSchedule_CommDates.Comm_Apr, qryReport_ProjectSchedule_CommDates.Comm_May, qryReport_ProjectSchedule_CommDates.Comm_Jun, qryReport_ProjectSchedule_CommDates.Comm_Jul, qryReport_ProjectSchedule_CommDates.Comm_Aug, qryReport_ProjectSchedule_CommDates.Comm_Sep, qryReport_ProjectSchedule_CommDates.Comm_Oct, qryReport_ProjectSchedule_CommDates.Comm_Nov, qryReport_ProjectSchedule_CommDates.Comm_Dec, qryReport_ProjectSchedule_CommDates.FCExpend, qryReport_ProjectSchedule_CommDates.MTHEXP, qryReport_ProjectSchedule_CommDates.ActSch, qryReport_ProjectSchedule_CommDates.ProjNo, qryReport_ProjectSchedule_CommDates.CMP, qryReport_ProjectSchedule_CommDates.ProjDesc, qryReport_ProjectSchedule_CommDates.ProjEng, qryReport_ProjectSchedule_CommDates.ProjBudget, qryReport_ProjectSchedule_CommDates.ProjEngArch, qryReport_ProjectSchedule_CommDates.FctSch, qryReport_ProjectSchedule_CommDates.fct_Jan, qryReport_ProjectSchedule_CommDates.Act_Jan, qryReport_ProjectSchedule_CommDates.Fct_Feb, qryReport_ProjectSchedule_CommDates.Act_Feb, qryReport_ProjectSchedule_CommDates.Fct_Mar, qryReport_ProjectSchedule_CommDates.Act_Mar, qryReport_ProjectSchedule_CommDates.Fct_Apr, qryReport_ProjectSchedule_CommDates.Act_Apr, qryReport_ProjectSchedule_CommDates.Fct_May, qryReport_ProjectSchedule_CommDates.Act_May, qryReport_ProjectSchedule_CommDates.Fct_Jun, qryReport_ProjectSchedule_CommDates.Act_Jun, qryReport_ProjectSchedule_CommDates.Fct_Jul, qryReport_ProjectSchedule_CommDates.Act_Jul, qryReport_ProjectSchedule_CommDates.Fct_Aug, qryReport_ProjectSchedule_CommDates.Act_Aug, qryReport_ProjectSchedule_CommDates.Fct_sep, qryReport_ProjectSchedule_CommDates.Act_sep, qryReport_ProjectSchedule_CommDates.Fct_Oct, qryReport_ProjectSchedule_CommDates.Act_Oct, qryReport_ProjectSchedule_CommDates.fct_Nov, qryReport_ProjectSchedule_CommDates.Act_Nov, qryReport_ProjectSchedule_CommDates.Fct_Dec, qryReport_ProjectSchedule_CommDates.Act_Dec, qryReport_ProjectSchedule_CommDates.DivisionID, qryReport_ProjectSchedule_CommDates.SeqNo, qryReport_ProjectSchedule_CommDates.StartMth, qryReport_ProjectSchedule_CommDates.DurationMths, qryReport_ProjectSchedule_CommDates.DurationColor, qryReport_ProjectSchedule_CommDates.startMthActual, qryReport_ProjectSchedule_CommDates.DurationMthsActual, qryReport_ProjectSchedule_CommDates.EventStart, qryReport_ProjectSchedule_CommDates.EventStop, qryReport_ProjectSchedule_CommDates.EventArrow, qryReport_ProjectSchedule_CommDates.EventNotes, qryReport_ProjectSchedule_CommDates.CommissionDate
FROM qryReport_ProjectSchedule_CommDates
  
  
 UNION SELECT qryReport_ProjectSchedule_ColorSchedule.CommYear, qryReport_ProjectSchedule_ColorSchedule.ProjectId, qryReport_ProjectSchedule_ColorSchedule.Comm_Jan, qryReport_ProjectSchedule_ColorSchedule.Comm_Feb, qryReport_ProjectSchedule_ColorSchedule.Comm_Mar, qryReport_ProjectSchedule_ColorSchedule.Comm_Apr, qryReport_ProjectSchedule_ColorSchedule.Comm_May, qryReport_ProjectSchedule_ColorSchedule.Comm_Jun, qryReport_ProjectSchedule_ColorSchedule.Comm_Jul, qryReport_ProjectSchedule_ColorSchedule.Comm_Aug, qryReport_ProjectSchedule_ColorSchedule.Comm_Sep, qryReport_ProjectSchedule_ColorSchedule.Comm_Oct, qryReport_ProjectSchedule_ColorSchedule.Comm_Nov, qryReport_ProjectSchedule_ColorSchedule.Comm_Dec, qryReport_ProjectSchedule_ColorSchedule.FCExpend, qryReport_ProjectSchedule_ColorSchedule.MTHEXP, qryReport_ProjectSchedule_ColorSchedule.ActSch, qryReport_ProjectSchedule_ColorSchedule.ProjNo, qryReport_ProjectSchedule_ColorSchedule.CMP, qryReport_ProjectSchedule_ColorSchedule.ProjDesc, qryReport_ProjectSchedule_ColorSchedule.ProjEng, qryReport_ProjectSchedule_ColorSchedule.ProjBudget, qryReport_ProjectSchedule_ColorSchedule.ProjEngArch, qryReport_ProjectSchedule_ColorSchedule.FctSch, qryReport_ProjectSchedule_ColorSchedule.Act_Jan, qryReport_ProjectSchedule_ColorSchedule.Act_Jan, qryReport_ProjectSchedule_ColorSchedule.Fct_Feb,qryReport_ProjectSchedule_ColorSchedule.Fct_Feb, qryReport_ProjectSchedule_ColorSchedule.Fct_Mar, qryReport_ProjectSchedule_ColorSchedule.Act_Mar, qryReport_ProjectSchedule_ColorSchedule.Fct_Apr, qryReport_ProjectSchedule_ColorSchedule.Act_Apr, qryReport_ProjectSchedule_ColorSchedule.Fct_May, qryReport_ProjectSchedule_ColorSchedule.Act_May, qryReport_ProjectSchedule_ColorSchedule.Fct_Jun, qryReport_ProjectSchedule_ColorSchedule.Act_Jun, qryReport_ProjectSchedule_ColorSchedule.Fct_Jul, qryReport_ProjectSchedule_ColorSchedule.Act_Jul, qryReport_ProjectSchedule_ColorSchedule.Fct_Aug, qryReport_ProjectSchedule_ColorSchedule.Act_Aug, qryReport_ProjectSchedule_ColorSchedule.Fct_sep, qryReport_ProjectSchedule_ColorSchedule.Act_sep, qryReport_ProjectSchedule_ColorSchedule.Fct_Oct, qryReport_ProjectSchedule_ColorSchedule.Act_Oct, qryReport_ProjectSchedule_ColorSchedule.fct_Nov, qryReport_ProjectSchedule_ColorSchedule.Act_Nov, qryReport_ProjectSchedule_ColorSchedule.Fct_Dec, qryReport_ProjectSchedule_ColorSchedule.Act_Dec, qryReport_ProjectSchedule_ColorSchedule.DivisionID, qryReport_ProjectSchedule_ColorSchedule.SeqNo, qryReport_ProjectSchedule_ColorSchedule.StartMth, qryReport_ProjectSchedule_ColorSchedule.DurationMths, qryReport_ProjectSchedule_ColorSchedule.DurationColor, qryReport_ProjectSchedule_ColorSchedule.startMthActual, qryReport_ProjectSchedule_ColorSchedule.DurationMthsActual, qryReport_ProjectSchedule_ColorSchedule.EventStart, qryReport_ProjectSchedule_ColorSchedule.EventStop, qryReport_ProjectSchedule_ColorSchedule.EventArrow, qryReport_ProjectSchedule_ColorSchedule.EventNotes, qryReport_ProjectSchedule_ColorSchedule.CommissionDate
FROM qryReport_ProjectSchedule_ColorSchedule
  
  
 UNION SELECT qryReport_ProjectSchedule_FC_MthlyExpenitures.Forecast_Year AS CommYear, qryReport_ProjectSchedule_FC_MthlyExpenitures.ProjectId, qryReport_ProjectSchedule_FC_MthlyExpenitures.Comm_Jan, qryReport_ProjectSchedule_FC_MthlyExpenitures.Comm_Feb, qryReport_ProjectSchedule_FC_MthlyExpenitures.Comm_Mar, qryReport_ProjectSchedule_FC_MthlyExpenitures.Comm_Apr, qryReport_ProjectSchedule_FC_MthlyExpenitures.Comm_May, qryReport_ProjectSchedule_FC_MthlyExpenitures.Comm_Jun, qryReport_ProjectSchedule_FC_MthlyExpenitures.Comm_Jul, qryReport_ProjectSchedule_FC_MthlyExpenitures.Comm_Aug, qryReport_ProjectSchedule_FC_MthlyExpenitures.Comm_Sep, qryReport_ProjectSchedule_FC_MthlyExpenitures.Comm_Oct, qryReport_ProjectSchedule_FC_MthlyExpenitures.Comm_Nov, qryReport_ProjectSchedule_FC_MthlyExpenitures.Comm_Dec, qryReport_ProjectSchedule_FC_MthlyExpenitures.FCExpend, qryReport_ProjectSchedule_FC_MthlyExpenitures.MTHEXP, qryReport_ProjectSchedule_FC_MthlyExpenitures.ActSch, qryReport_ProjectSchedule_FC_MthlyExpenitures.ProjNo, qryReport_ProjectSchedule_FC_MthlyExpenitures.CMP, qryReport_ProjectSchedule_FC_MthlyExpenitures.ProjDesc, qryReport_ProjectSchedule_FC_MthlyExpenitures.ProjEng, qryReport_ProjectSchedule_FC_MthlyExpenitures.ProjBudget, qryReport_ProjectSchedule_FC_MthlyExpenitures.ProjEngArch, qryReport_ProjectSchedule_FC_MthlyExpenitures.FctSch, qryReport_ProjectSchedule_FC_MthlyExpenitures.fct_Jan, qryReport_ProjectSchedule_FC_MthlyExpenitures.Act_Jan, qryReport_ProjectSchedule_FC_MthlyExpenitures.Fct_Feb, qryReport_ProjectSchedule_FC_MthlyExpenitures.Act_Feb,qryReport_ProjectSchedule_FC_MthlyExpenitures.Fct_Mar, qryReport_ProjectSchedule_FC_MthlyExpenitures.Act_Mar, qryReport_ProjectSchedule_FC_MthlyExpenitures.Fct_Apr, qryReport_ProjectSchedule_FC_MthlyExpenitures.Act_Apr, qryReport_ProjectSchedule_FC_MthlyExpenitures.Fct_May, qryReport_ProjectSchedule_FC_MthlyExpenitures.Act_May, qryReport_ProjectSchedule_FC_MthlyExpenitures.Fct_Jun, qryReport_ProjectSchedule_FC_MthlyExpenitures.Act_Jun, qryReport_ProjectSchedule_FC_MthlyExpenitures.Fct_Jul, qryReport_ProjectSchedule_FC_MthlyExpenitures.Act_Jul, qryReport_ProjectSchedule_FC_MthlyExpenitures.Fct_Aug, qryReport_ProjectSchedule_FC_MthlyExpenitures.Act_Aug, qryReport_ProjectSchedule_FC_MthlyExpenitures.Fct_sep, qryReport_ProjectSchedule_FC_MthlyExpenitures.Act_sep, qryReport_ProjectSchedule_FC_MthlyExpenitures.Fct_Oct, qryReport_ProjectSchedule_FC_MthlyExpenitures.Act_Oct, qryReport_ProjectSchedule_FC_MthlyExpenitures.fct_Nov, qryReport_ProjectSchedule_FC_MthlyExpenitures.Act_Nov, qryReport_ProjectSchedule_FC_MthlyExpenitures.Fct_Dec, qryReport_ProjectSchedule_FC_MthlyExpenitures.Act_Dec, qryReport_ProjectSchedule_FC_MthlyExpenitures.DivisionID, qryReport_ProjectSchedule_FC_MthlyExpenitures.SeqNo, qryReport_ProjectSchedule_FC_MthlyExpenitures.StartMth, qryReport_ProjectSchedule_FC_MthlyExpenitures.DurationMths, qryReport_ProjectSchedule_FC_MthlyExpenitures.DurationColor, qryReport_ProjectSchedule_FC_MthlyExpenitures.startMthActual, qryReport_ProjectSchedule_FC_MthlyExpenitures.DurationMthsActual, qryReport_ProjectSchedule_FC_MthlyExpenitures.EventStart, qryReport_ProjectSchedule_FC_MthlyExpenitures.EventStop, qryReport_ProjectSchedule_FC_MthlyExpenitures.EventArrow, qryReport_ProjectSchedule_FC_MthlyExpenitures.EventNotes, qryReport_ProjectSchedule_FC_MthlyExpenitures.CommissionDate
FROM qryReport_ProjectSchedule_FC_MthlyExpenitures;

 

[spikepl]

Try UNION ALL instead. Also, check that the data type of each column is the same in each query.

 

[Richard M]

I tried UNION ALL, but Access 2007 did not like it. I also made sure the data type was the same in all three queries, but I will check it again.

Thanks

Richard