Case or IIF in a sql statement

[scottetombleson]

Follow up to an earlier question Subject "IIF Type Mismatch"...

Trying to populate a date field if tempMaster_P.PPAYAMOUNT > 0.

PHV Hit it right on, but now i need to populate something other than a yes no field.

I think I might need to use a CASE statement, but I keep getting a "Missing Operator" Error.

Thanks in advance.

 

[scottetombleson]

Here is my code.....

sqlc = "INSERT INTO tblCHARGES ( PPAY, PPAYDATE ) SELECT  (tempMaster_P.PPAYAMT > 0) AS PPAY , (CASE tempMaster_P.PPAYAMT WHEN tempMaster_P.PPAYAMT > 0 THEN #" & startdate & "# ELSE "" END CASE) AS PPAYDATE FROM tempMaster_P;"

Thanks again in advance for your help.

 

[Zion7]

I think you may need to try this?...

Dim rec As New ADODB.Recordset

rec.Open "tempMaster_P", currentProject.Connection, adForwardOnly, adLockOptimistic

Do Until rec.EOF

sqlc = "INSERT INTO tblCHARGES ( PPAY, PPAYDATE ) VALUES (" & rec!PPAYAMT & "," & ( IIF(rec!PPAYAMT > 0, "#" & rec!startdate & "#" ,"") & ")""

rec.MoveNext
Loop

It is multiple records you're adding, isn't it?

Is it ONLY records where PPAYMENT is greater than zero?
Is so, place a criterion in your recordset instead of the IIF()

 

[PHV]

sqlc = "INSERT INTO tblCHARGES ( PPAY, PPAYDATE ) SELECT (PPAYAMT > 0) AS PPAY , IIf(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