Data type mismatch in criteria expression (1 Viewer)

[PRTP]

Hi

See attached spreadsheet for an illustration. But note, the values in ProgramDetails are Text in the real database

For quick reference, a slimmed down version of the data looks like this:
ProgramList
PROGRAM.......START_DATE
X-ABC123....... 12/04/2012
None in force.... 31/05/2018
Y-34-FG-343..... 24/02/2019

ProgramDetails
PROGRAM_CODE.....YEAR.......PERCENT
X-ABC123..................... 1.................. 20
X-ABC123..................... 2..................100
Y-34-FG-343.................. 1..................30
Y-34-FG-343...................2..................60
Y-34-FG-343...................3..................100

I want a query that returns
PROGRAM........START_DATE.......DURATION
X-ABC123..........12/04/2012....................2
None in force.......31/05/2018.................... this should be blank/NULL
Y-34-FG-343.......24/02/2019....................3

where DURATION is now a number


I've written a query in Design View with a Totals row.
I've got a "Include all records from ProgramList" joint

Then I've written an expression that says
Duration: IIF([PROGRAM] = "None in force", NULL, Max(Val[Year])))

I'm getting the message
Data type mismatch in criteria expression

I don't understand why that is?
I'm not telling it to perform a Max function if Program was "None in force", just simply return NULL.

Can someone please explain why it's not that simple?

Thanks

 

[Gasman]

What is the type for the Year field?

 

[PRTP]

Hi Gasman

Sorry I painted bit of a misleading picture there. So I've now updated the question to match below.

Year is a text field (despite containing a number)

and the expression I'd written for duration actually says
Duration: IIF([PROGRAM] = "None in force", NULL, Max(val([Year])))

I'm going to keep getting fresh versions of this data, which annoyingly has numbers formatted as text in some places. So with it not being a one-off, I'm trying to use Val in many places instead of having to do upfront data formatting each time.

 

[arnelgp]

select Program, Start_Date,
   (select Max(T1.[Year]) from ProgramDetails as T1 
        where T1.Program_Code = ProgramList.Program) 
            as Duration 
from ProgramList;

 

[PRTP]

Hi arnelgp

That would work if the YEAR field was formatted as number.
Apologies how just before you posted, I updated the question to mention that the YEAR field is formatted as text and I had used the Val function in the query to try and get around that........but the IIF, Max, Val don't seem to want to work together and I don't get why.

 

[arnelgp]

it will work even if [Year] is a text, eg:

? "3" > "1" --> True

I just tried the query I posted and it works.

 

[PRTP]

I tried it and it doesn't give the right result if there are values >10 with how they are formatted as text

Max(Val[Year]))

works if there were no "None in force" records.


I don't get why a "None in force" record is stopping it from working when I'm telling it not to do any Max/Val operations to such records.

IIF([PROGRAM] = "None in force", NULL, Max(Val[Year])))

 

[arnelgp]

try this one:

SELECT 
      ProgramList.Program, 
      ProgramList.Start_Date, 
      IIF(ISNULL(DMax("[Year]","ProgramDetails", 
           "Program_Code = " & Chr(34) & ProgramList.Program & Chr(34))),
                 NULL, 
                 CINT(DMax("[Year]","ProgramDetails", 
           "Program_Code = " & Chr(34) & ProgramList.Program & Chr(34)))) AS Duration
FROM ProgramList;

 

[CJ_London]

three things

1. Year is a reserved word, using it as a field name can generate misleading error messages. Using square brackets usually solves the problem, but not always.

2. the iif function will evaluate both potential results so if Year can be null, you need to protect against that e.g. val(null) will produce an error

3. you say the error is

Data type mismatch in criteria expression

which is to do with the WHERE part of the SQL, not a calculation

Suggest provide your full sql since I suspect the issue is elsewhere.

as an aside, in SQL (not vba) you don't have to provide the second outcome to an iif function so (assuming it did work) this

Duration: IIF([PROGRAM] = "None in force", NULL, Max(Val[Year])))

could be written as

Duration: IIF([PROGRAM] <> "None in force", Max(Val(nz([Year],"0"))))

Note I have corrected for the missing bracket for the Val function - I presume this is a typo on your part. Which to be fair, should not happen if you copy/paste. If you are typing it in in becomes 'aircode' because you are potentially introducing mistakes which prevent us from determining what the real problem is.

I've also added in the nz function to protect against nulls