"Data type mismatch in criteria expression" after changing criteria

[andylaw31]

I have a query that pulls out student assessment data. It was looking at 2014/15 data but now it's come time to move on to 2015/16 data. I have two lines (by lines, I mean lines in design view, not lines of SQL code) of criteria on my query, each of which filter by academic year. After updating these, I get the "Data type mismatch in criteria expression" error.

If I change the academic year in only one of the criteria lines, it works. If I take out the only criteria that are different between the two lines entirely (so removing that element of filtration), it doesn't work. So it looks like it may be because of one criterion, but that it needs some filtration on that field to work. Confusing.

For information, the field that differs in its criteria across the two lines is "actual grade (module): smr_actg" - one excludes certain values and one includes null values (as I found that when you filter out certain values it annoyingly also filters out records with null values in that field, so you have to filter them back in :banghead.

I've tried converting the academic year "2015/6" using Val but it gave me the same error.

Any ideas? The SQL is below (but I generally use the design view to edit, as I'm less comfortable editing the SQL directly).

SELECT DISTINCT srs_fac.fac_name AS faculty, [awd_desc] & " " & [rou_name] AS [full award title], ins_mod.mod_code AS [module code], cam_sas.spr_code AS [ID number], Left([stu_name],Len([stu_fnm1])) AS [first name], Right([stu_name],Len([stu_surn])) AS [last name], StrConv([spr_snam],3) AS [known as], [smr_actm]/100 AS [actual mark (module)], IIf(IsNull([smr_agrg]),'','Yes') AS agreed, ins_smr.smr_actg AS [actual grade (module)], srs_scj.scj_stac AS status, ins_smr.mav_occur AS occurrence, ins_spr.rou_code AS route, ins_smr.psl_code AS period, cam_sas.ayr_code, Right([cam_sas.mab_seq],1) AS component, [sas_actm]/100 AS [actual mark (component)]
FROM (((srs_scj INNER JOIN (((ins_spr INNER JOIN ((cam_ast INNER JOIN cam_mab ON cam_ast.[ast_code] = cam_mab.[ast_code]) INNER JOIN (cam_sas INNER JOIN ins_mod ON cam_sas.mod_code = ins_mod.mod_code) ON (cam_sas.mab_seq = cam_mab.mab_seq) AND (cam_mab.map_code = cam_sas.map_code)) ON ins_spr.spr_code = cam_sas.spr_code) INNER JOIN ins_rou ON ins_spr.rou_code = ins_rou.rou_code) INNER JOIN ins_awd ON ins_spr.awd_code = ins_awd.awd_code) ON srs_scj.scj_sprc = ins_spr.spr_code) INNER JOIN (srs_crs INNER JOIN srs_fac ON srs_crs.crs_facc = srs_fac.fac_code) ON srs_scj.scj_crsc = srs_crs.crs_code) INNER JOIN ins_smr ON (ins_smr.spr_code = cam_sas.spr_code) AND (cam_sas.mod_code = ins_smr.mod_code)) INNER JOIN ins_stu ON ins_spr.spr_stuc = ins_stu.stu_code
WHERE (((ins_smr.smr_actg)<>"DR" And (ins_smr.smr_actg)<>"FR" And (ins_smr.smr_actg)<>"NW" And (ins_smr.smr_actg)<>"FW") AND ((srs_scj.scj_stac)<>"T" And (srs_scj.scj_stac)<>"T1") AND ((cam_sas.ayr_code)="2015/6") AND ((ins_mod.mod_iuse)="Y") AND ((ins_smr.smr_proc)="SAS")) OR (((ins_smr.smr_actg) Is Null) AND ((srs_scj.scj_stac)<>"T" And (srs_scj.scj_stac)<>"T1") AND ((cam_sas.ayr_code)="2015/6") AND ((ins_mod.mod_iuse)="Y") AND ((ins_smr.smr_proc)="SAS"));

 

[Minty]

Should your criteria be 2015/16 ?

 

[andylaw31]

Should your criteria be 2015/16 ?

Nope. Apologies for the confusion. I use the style "2015/16" in prose, but we use the style "2015/6" on our student records system (hence the unhelpful inconsistency in my original post). I have made that mistake before though!

 

[andylaw31]

Just amended the query to put the two lines of criteria onto one, which seems to work. New code below, but still producing the same behaviour on update of academic year (works fine for 2014/15, but not 2015/16).

SELECT DISTINCT srs_fac.fac_name AS faculty, [awd_desc] & " " & [rou_name] AS [full award title], ins_mod.mod_code AS [module code], cam_sas.spr_code AS [ID number], Left([stu_name],Len([stu_fnm1])) AS [first name], Right([stu_name],Len([stu_surn])) AS [last name], StrConv([spr_snam],3) AS [known as], [smr_actm]/100 AS [actual mark (module)], IIf(IsNull([smr_agrg]),'','Yes') AS agreed, ins_smr.smr_actg AS [actual grade (module)], srs_scj.scj_stac AS status, ins_smr.mav_occur AS occurrence, ins_spr.rou_code AS route, ins_smr.psl_code AS period, cam_sas.ayr_code, Right([cam_sas.mab_seq],1) AS component, [sas_actm]/100 AS [actual mark (component)]
FROM (((srs_scj INNER JOIN (((ins_spr INNER JOIN ((cam_ast INNER JOIN cam_mab ON cam_ast.[ast_code] = cam_mab.[ast_code]) INNER JOIN (cam_sas INNER JOIN ins_mod ON cam_sas.mod_code = ins_mod.mod_code) ON (cam_sas.mab_seq = cam_mab.mab_seq) AND (cam_mab.map_code = cam_sas.map_code)) ON ins_spr.spr_code = cam_sas.spr_code) INNER JOIN ins_rou ON ins_spr.rou_code = ins_rou.rou_code) INNER JOIN ins_awd ON ins_spr.awd_code = ins_awd.awd_code) ON srs_scj.scj_sprc = ins_spr.spr_code) INNER JOIN (srs_crs INNER JOIN srs_fac ON srs_crs.crs_facc = srs_fac.fac_code) ON srs_scj.scj_crsc = srs_crs.crs_code) INNER JOIN ins_smr ON (ins_smr.spr_code = cam_sas.spr_code) AND (cam_sas.mod_code = ins_smr.mod_code)) INNER JOIN ins_stu ON ins_spr.spr_stuc = ins_stu.stu_code
WHERE (((ins_smr.smr_actg) Is Null Or ((ins_smr.smr_actg)<>"DR" And (ins_smr.smr_actg)<>"FR" And (ins_smr.smr_actg)<>"NW" And (ins_smr.smr_actg)<>"FW")) AND ((srs_scj.scj_stac)<>"T" And (srs_scj.scj_stac)<>"T1") AND ((cam_sas.ayr_code)="2014/5") AND ((ins_mod.mod_iuse)="Y") AND ((ins_smr.smr_proc)="SAS"));

 

[andylaw31]

Anyone got any ideas on this? Still not working.

 

[JHB]

Post a stripped down database with some sample data, (zip it) + what result you want to get.

 

[andylaw31]

Thanks for the offer JHB. We'd have had to anonymise the data before posting it, as it contains marks for student assessment. We've now managed to create a workaround by splitting it into two queries - one with the details about each student and one with their assessment data - and then a further query to connect them. Don't know why this works, but it does!