IIF Type Mismatch 1

[scottetombleson]

I am trying to populate a Yes/No field based on wether or not tempMaster_P.PPAYAMT, a currency field, is greater than 0. I am using the following in the Values portion of my SQL statement. I keep getting a type mismatch error.

IIf("tempMaster_P.PPAYAMT > 0", "-1", "0")

I have tried so many variations i hardly remember. Please somebody put me out of my misery and tell me what the &^%$ I am doing wrong.

Thanks in advance for your help.

 

[PHV]

Why not simply this ?
=(tempMaster_P.PPAYAMT > 0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dpk136]

What is the data type that you are trying to put -1 or 0 into. if it is a numeric field, i think you can leave off the quotes...if it's a string, leave those on and check to see if the data type for tempMaster_P.PPAYAMT is a numeric field.

if its numeric it should look like this

...
IIF("tempMaster_P.PPAYAMT > 0",-1,0)
...

David Kuhn
------------------

http://www.davidpkuhn.com

 

[dpk136]

Sorry, i read your post again and saw that you put the field types...still try the piece of code that i put up there. hope that helps.

David Kuhn
------------------

http://www.davidpkuhn.com

 

[PHV]

still try the piece of code that i put up there
I'm not sure, I'd try this:
IIF(tempMaster_P.PPAYAMT > 0,-1,0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kpal29]

If that doesn't work, also try:

IIF("tempMaster_P.PPAYAMT > 0",true,false)
[code]

 

[PHV]

kpal29, same advice as for dpk136: get rid of the quotes.

 

[scottetombleson]

First of all Thanks! But none of these options worked. Tried em all. Without quotes around the first argument I get an "Object Required" error.

Here is my statement.

sqlc = "INSERT INTO tblCHARGES ( PPAY ) SELECT " & IIf("tempMaster_P.PPAYAMT > 0", True, False) & " FROM tempMaster_P;"

Ay other ideas?

 

[PHV]

sqlc = "INSERT INTO tblCHARGES ( PPAY ) SELECT (tempMaster_P.PPAYAMT > 0) AS PPAY FROM tempMaster_P"


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[scottetombleson]

PHV, BRILLIANT!

It worked like a charm, now a follow up question.....

I want to populate another field with a date variable called startdate if tempMaster_P.PPAYAMT > 0 and leave it null if not.

What would the syntax be for that?

 

[PHV]

sqlc = "INSERT INTO tblCHARGES ( PPAY, PPAYDATE ) SELECT (tempMaster_P.PPAYAMT > 0) AS PPAY , IIf(tempMaster_P.PPAYAMT > 0, #" & startdate & "#, Null) AS PPAYDATE FROM tempMaster_P"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886