Conditional Statements

[tigers08]

Hey all,

I have 3 fields on a form. If any of theses fields contain a value then I want to write a record to a database. I've got all the logic down to write to the database, but I'm having problems with a complicated IF Then statement.

The three fields are stored in variables - strNewPay, strNewAcct, strNewTitle. I only want to write to the database if any of these fields contain data. It can be any combination of these 3....1, 2, all, etc.

I can make it work if all fields contain a value, but I'm struggling to get it to work with all the possible combinations.

If strNewPay <> "" and strNewLaborAcct <> "" and strNewTitle <> "" and Rs.Recordcount = 0 then
Rs.Addnew

Any advice would be greatly appreciated. I've tried mnay different If Then statements but can't seem to get it right.

 

[dm4ever]

If you accept any variation use an OR instead of AND

--------------------------------------------------------------------------------
dm4ever
My philosophy: K.I.S.S - Keep It Simple Stupid

 

[PHV]

The simplest way:
If strNewPay & strNewLaborAcct & strNewTitle <> "" And Rs.Recordcount = 0 Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tigers08]

I tried it as "or" insead of "and"...this returns every record found in the loop - in this case 20 records.

I'm looping thru the form for values (only displaying one below to save space) then writing to the database if one of three fields contain data. Here's some code:

'Loop for values
For x = 1 to 20
If Trim(form.find("txtSSN" & x).value) <> "" then
strSSN = mid(Form.Find("txtSSN" & x).value,1,9)
End If

Set Rs = DF.Query(sqlConnectString, "SELECT * FROM Database WHERE EMPLOYEE_NUMBER='" & strSSN & "'")
DC.SourceRecordset = Rs


If strNewPay <> "" or strNewLaborAcct <> "" or strNewTitle <> "" and Rs.Recordcount = 0 then
Rs.AddNew
End If

If not Rs.EOF then
Rs("employee_number") = strSSN
Rs("last_name") = strLastName
Rs("first_name") = strFirstName
Rs("monthly_pay_amount_old") = strCurrentPay
Rs("primary_labor_org_account_code") = strCurrentLaborAcct
Rs("job_title") = strCurrentTitle
Rs("company_number") = txtCompanyNumber.Value
Rs("monthly_pay_amount") = strNewPay
Rs("to_primary_labor_org_account_code") = strNewLaborAcct
Rs("to_job_title") = strNewTitle

Rs.Update
blnStatus = DF.SubmitChanges(sqlConnectString, Rs)
End If

strNewPay = ""
strNewLaborAcct = ""
strNewTitle = ""
Next