IIF Statement Access 2007 Error Message: Data Type Mismatch

[Martano0811]

Hello, I'm new user in Access 2007. I attempting to build nested IIf Statements mostly on Date/Time Fields. This example is a new field or (flag comapring two data sets with same variables) I'm getting error message: "Data type Mismatch in criteria expression". Im not sure what I need to update to get this corrected. I go the first part of the IIF statement to work...its the paert where And begins...this is where I'm having trouble. Any help would be much appreciated.

I would like place an N/A in the cell where the old and new data sets have an empty value.


Here is an example:

IIf([CPC_Old].[Project released for planning Actual]=[CPC_New].[Project released for planning Actual],"No","Yes")

And IIf(IsNull([CPC_Old].[Project released for planning Actual])," ","N/A") And IIf(IsNull([CPC_New].[Project released for planning Actual])," ","N/A")

 

[pr2-eugin]

I believe in this statement you are using a Logical evaluation for String which is not correct..

IIf(IsNull([CPC_Old].[Project released for planning Actual])," ","N/A") And IIf(IsNull([CPC_New].[Project released for planning Actual])," ","N/A")

Say if [CPC_Old].[Project released for planning Actual] is NULL and [CPC_New].[Project released for planning Actual]) is NOT NULL, what you are trying to do is trying to perform a logical AND on String.

Simply checking NULL and N/A..

Does not make any sense. So try to see what you want to do.

Do you want to place the values 'N/A' or 'NULL' in a field based on some conditions?
Do you want to return some fields as part of a query?

 

[spikepl]

Give an example of input values and desired ouutput.

What you have listed makes no sense, because you cannot put an AND between two strings, which is what your lower statement does.

 

[Martano0811]

Thank you for the quick response, Yes I would like to place an N/A in this criteria.. where both the Old and the New tables have empty cells or (in this example don't have a date) and (basesd on these condistions) give me an N/A.

 

[spikepl]

For nested IFFs =

Iff (logicalexpression, ShownIfTrue,ShowIfFalse)

This you can nest to a number of levels by replacing, eg., ShownIfTrue by a new IIF.
the logical expression can consist of one or more conditions to be fulfilled at the same time.

 

[pr2-eugin]

I believe this is what you are trying to do, but ccannot be 100% sure without seeing some examples of how it needs to work for you..

IIf(IsNull([CPC_Old].[Project released for planning Actual]) And IsNull([CPC_New].[Project released for planning Actual])," ","N/A")

the above will return N/A if either of the two fields([CPC_Old].[Project released for planning Actual] or [CPC_New].[Project released for planning Actual]) are NOT empty.

 

[Martano0811]

Give an example of input values and desired ouutput.

What you have listed makes no sense, because you cannot put an AND between two strings, which is what your lower statement does.

Input values are comapring two tables with exact feilds but at different time frames. Im trying to tell the query to give me a Yes if the two variables are differrent dates, No if they are the same and, N/A if both fields are blank. The third part I'm having trouble writing the logic.

So this part seems to work: IIf([CPC_Old].[Project released for planning Actual]=[CPC_New].[Project released for planning Actual],"No","Yes")

The above gives me a yes or no.

I cant firgure out correct logic if both fields are blank from the old and new CPC tables then give me an N/A.

Thanks!

 

[pr2-eugin]

Have you suggested post #6? I believe that is what you need. Except, based on the requierment placed in post #7 if both are NULL then return N/A you should try the following..

IIf(IsNull([CPC_Old].[Project released for planning Actual]) And IsNull([CPC_New].[Project released for planning Actual]),"N/A"," ")

 

[spikepl]

The above does not give you a Yes or No. It gives you a "Yes" or a "No". The first two are boolean variables that you can do a logical test on. The last two are strings, that you cannot use an AND for.

 

[Martano0811]

Ok this makes more sense:


Is it safe to use this in the Criteria:

IIf([CPC_Old].[Project released for planning Actual]=[CPC_New].[Project released for planning Actual],"No","Yes") And IIf(IsNull([CPC_Old].[Project released for planning Actual]) And IsNull([CPC_New].[Project released for planning Actual])," ","N/A")

 

[spikepl]

No because it makes no sense.

DO you know anything about variable types? Boolean vs String?

You have two options now: work out yourself how things are supposed to be put together (lookup examples of IIF in docs and on the net), or give an example of input, conditions and desired output, and let someone else work it out for you. Perhaps pr2-eugin will do that -I won't

 

[Martano0811]

Have you suggested post #6? I believe that is what you need. Except, based on the requierment placed in post #7 if both are NULL then return N/A you should try the following..

IIf(IsNull([CPC_Old].[Project released for planning Actual]) And IsNull([CPC_New].[Project released for planning Actual]),"N/A"," ")

pr2-eugin,

So my understading is that I can place these two toghter due to boolean and string....are you able to help?

IIf([CPC_Old].[Project released for planning Actual]=[CPC_New].[Project released for planning Actual],"No","Yes")

And IIf(IsNull([CPC_Old].[Project released for planning Actual]) And IsNull([CPC_New].[Project released for planning Actual])," ","N/A")

Output: I need in "Yes", "No", "N/A"

 

[Martano0811]

No because it makes no sense.

DO you know anything about variable types? Boolean vs String?

You have two options now: work out yourself how things are supposed to be put together (lookup examples of IIF in docs and on the net), or give an example of input, conditions and desired output, and let someone else work it out for you. Perhaps pr2-eugin will do that -I won't

This closer ? Its running but not giving me N/A in the output just yes for the cells that are empty...


IIf([CPC_Old].[Project released for planning Actual]=[CPC_New].[Project released for planning Actual],"No","Yes") & IIf(IsNull([CPC_Old].[Project released for planning Actual]) & IsNull([CPC_New].[Project released for planning Actual])," ","N/A")

 

[pr2-eugin]

Martano0811, I would love to help but I really need some examples or some cases which will show what your desired output should be.. in simple english.. as spikepl has mentioned it really does not make any sense at all.. you are trying to match between a String and a Boolean, which CANNOT BE DONE AT ALL..

 

[jloving]

Hello all. I have 3 nested IIf() statements that are in use in a working make table query. I would like to convert these statements to VBA to make multiple tables with the same column names but different table names. The only way to do this is to take the Access 2007 query, view the SQL code and convert to VBA. The original make table query is long and includes nested IIf() statements to check the existence of a string and also to run and return a value from a user defined function. All statements and functions work in the make table query but I'm having an extremely hard time with the conversions of these statements into VBA and get them to work.

Statements that are giving me issues:

strSQL = strSQL & "IIf((InStr(exp_SBST.[remarks_set],"", ""))>0,Left(exp_SBST.[remarks_set],(InStr(exp_SBST.[remarks_set],"", "")-1)),"") AS [CustomerComments], "

strSQL = strSQL & "IIf(exp_SBRV.[remarks]<>"",GetFullURL([Folder],[Contract Number],exp_SBRV.[remarks])) AS [Original], "

strSQL = strSQL & "IIf([CustomerComments]<>"",GetFullURL([Folder],[Contract Number],[CustomerComments]),"") AS [Comment], "
-------------------------------------------------------------------------------------------
GetFullURL( ) is a function in VBA. Which works when being called from the query but after adding, what I believe are the necessary number of quotes I receive the following error message:
Error creating table. Syntax error (missing operator) in query expression 'IIf(exp_SBRV.[remarks]<>"",GetFullURL([Folder],[Contract Number]...
-------------------------------------------------------------------------------------------
Original SQL statement:
SELECT [exp_VNMT]![company_name] AS Vendor, [exp_SBMT]![DRAWING] AS [Drawing No], [exp_SBMT]![TITLE] AS Title, [exp_SBRV]![description] AS Rev, Left([exp_SBMT]![SUBMITTAL_NO],8) AS WBS, [exp_SBRV]![received_date] AS Received, [exp_SBRV]![remarks] AS Filename, exp_PROJ.job_number, IIf((InStr([remarks_set],", "))>0,Left([remarks_set],(InStr([remarks_set],", ")-1)),"") AS CustomerComments, exp_PROJ.project_name, [exp_PROJ]![project_number] AS [Contract Number], "T:\released\" AS Folder, IIf([exp_SBRV]![remarks]<>"",GetFullURL([Folder],[Contract Number],[exp_SBRV]![remarks]),"FILENAME MISSING!") AS Original, [exp_SBST]![sent_date_set] AS Sent, IIf([CustomerComments]<>"",GetFullURL([Folder],[Contract Number],[CustomerComments]),"") AS Comment, [exp_SBMT]![SUBMITTAL_NO] AS [Submittal No], [exp_SBRV]![PE_STATUS] AS [PE Status], exp_SBMT.LATEST_REV_NUMBER, exp_SBRV.revision_number, exp_SBST.description_set, exp_SBST.revision_number_set, exp_SBMT.STATUS, IIf([exp_SBMT]![LATEST_REV_NUMBER]=[exp_SBRV]![revision_number],"Y","N") AS Latest, IIf([exp_SBMT]!

="E","Y","N") AS Erection,  [exp_SBRV]![Proform_Number] AS Proform, exp_SBRV.created_by,  exp_SBRV.last_edit_by, exp_SBRV.created_date, exp_SBMT.LAST_EDIT_DATE,  DateValue([exp_SBRV]![created_date]) AS ExpCreatedDate,  DateValue([exp_SBMT]![LAST_EDIT_DATE]) AS ExpLastEditDate, Date() AS  QueryRun, [exp_VNMT]![company_name] & " " & [exp_SBMT]![DRAWING]  & " " & [exp_SBRV]![description] & " " &  [exp_SBMT]![SUBMITTAL_NO] AS StringKey, exp_SBST.remarks_set,  exp_SBST.reviewer_set_vndr INTO tblLocalE2B000109
FROM (exp_VNMT  INNER JOIN ((exp_SBMT INNER JOIN exp_SBRV ON exp_SBMT.MASTER_KEY =  exp_SBRV.parent_key) LEFT JOIN exp_SBST ON exp_SBRV.master_key =  exp_SBST.parent_key) ON exp_VNMT.vendor_abbrev = exp_SBMT.RECEIVED_FROM)  INNER JOIN exp_PROJ ON exp_VNMT.project_name = exp_PROJ.project_name
WHERE ((([exp_SBRV]![remarks]) Not Like ""))
ORDER BY [exp_VNMT]![company_name], [exp_SBMT]![DRAWING], [exp_SBRV]![description] DESC , [exp_SBMT]![SUBMITTAL_NO];[/SIZE]
---------------------------------------------------------------------------------------------
Thanks in advance for any help you can provide.  Have a great day!
~Jackie