Conditional Statements

[tigers08]

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 many different If Then statements but can't seem to get it right.

[alex_read]

If (strNewPay <> "") OR (strNewLaborAcct <> "") _
OR (strNewTitle <> "") then 
    Rs.Addnew

Well it sounds as though you want an OR operator in there as opposed to AND. I personally find wrapping each condition being evaluated within brackets and/or separating the If statement on several lines also makes the statement more easily readable but that's a side note.

You haven't told us where the Rs.Recordcount comes into play here. If you needed any of those textboxes to contain an entry or alternatively the rs.Recordcount to equal a value of 0 before you perform your AddNew operation, then you would need this code:

If (strNewPay <> "") OR (strNewLaborAcct <> "") _
OR (strNewTitle <> "") OR (Rs.Recordcount = 0) then 
    Rs.Addnew

However if you need any of the textboxes to contain a value, as well as the Rs.Recordcount to equal a value of 0, then you would need this code. In this version, the If statement works similar to a maths equation such as "(1*2)*3", where the calculation of the numbers within the brackets is performed first (below, the equivalent is to check whether any of the textboxes contain a value within brackets first, prior to the check for the rs.Recordcount value equaling 0. If both of these are true, the AddNew operation is performed).

If ((strNewPay <> "") OR (strNewLaborAcct <> "") _
OR (strNewTitle <> "")) AND (Rs.Recordcount = 0) then 
    Rs.Addnew

[tigers08]

Alex - thanks for the reply

I tried your suggestion and it returned all values in the loop - in this case 20.

I'm looping thru the form to grap values (only displaying one below to save space) from the fields, then want to write to the datbase if one of the 3 fields contains a value.

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_account_code") = strCurrentLaborAcct
Rs("job_title") = strCurrentTitle
Rs("company") = txtCompanyNumber.Value
Rs("monthly_pay_amount") = strNewPay
Rs("labor_account_code") = strNewLaborAcct
Rs("job_title") = strNewTitle


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

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

Next