[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 411
  • Last Modified:

How to use IIf statement using three criterias if true then append the records. Syntax error but no clue whats wrong?

I am trying to append records based on three criterias if they meet these criterias then that record is appended. I am getting a error. But it just says syntax error. what am I doing wrong experts?

This code is comparing what is in the table it is appending to.
tblFS3Question = table that has data that needs to append to another table
tblFS3Responses= table that data needs to append to.


If the FormID is 1 in the tblFS3Responses and the count of the questiontext =76  and the time is the same then dont append it.  else if this is not a true statement then append the record.


IIf([tblFS3Responses]![QuestionText]![FormID]= "1" AND Count([tblFS3Responses]![QuestionText]>76 AND ([tblFS3QuestionResponses]![TimeInduction]=[tblFS3Responses]![TimeInduction],"This Record Has Been Already Saved!!! Please Try Again!",(Docmd.OpenQuery "qryAppendQuestions")
0
gigifarrow
Asked:
gigifarrow
4 Solutions
 
Rgonzo1971Commented:
Hi,
Are you sure  FormID = "1" not 1

IIf([tblFS3Responses]![QuestionText]![FormID]= 1 AND Count([tblFS3Responses]![QuestionText]>76 AND ([tblFS3QuestionResponses]![TimeInduction]=[tblFS3Responses]![TimeInduction],"This Record Has Been Already Saved!!! Please Try Again!",(Docmd.OpenQuery "qryAppendQuestions") 

Open in new window

Regards
0
 
Jim P.Commented:
It looks like you're missing closing parentheses.
0
 
gigifarrowAuthor Commented:
Thank you for your help I tried that but I get  message that says syntax error and then it highlights


"qryAppendQuestions"
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
gigifarrowAuthor Commented:
the number one is  a selection in the drop down. FormID is datatype is autonumber.
0
 
PatHartmanCommented:
You are misusing the expression.

The IIf() is intended to return a value but you have it returning a value as the true path but running an action query in the false path.  If you are trying to do this in VBA, you need to use an If rather than an IIF() function since in VBA all paths of the function are evaluated not just the true path.  With an If statement, only the true path is ever evaluated.
0
 
gigifarrowAuthor Commented:
ok so now i changed it to a if statement and it is not stoping dupilcates from appending.

This is a continuous form that the append query is appending.
formId = selection of the form they need to use. which could be 1-8
so if the serial number and form Id is the same then they cant save that data.


If DCount("*", "tblFS3Responses", "[Serial]=" & Chr(34) & SID & Chr(34) & [FormID] = " & Me.FormID & ")>0 Then

Below is one record. I cant remove duplicates because all the data is the same except for the answers.


Serial    formID     question#        Question                                                                  Name

 139      3      151      Latch, Bar Drivers Hatch Rework:      Zte, Juan
 139      3      151      If Deficiencies Please Explain:            Zte, Juan
 139      3      152      TOW Missile Launcher &                                   Zte, Juan
0
 
PatHartmanCommented:
The syntax is incorrect:

If DCount("*", "tblFS3Responses", "[Serial] = "  & SID  & " AND [FormID] = " & Me.FormID )>0 Then
It was missing the " AND " and I removed the Chr(34)'s because it looked like the data was numeric.  If Serial and FormID are not numeric, then single quotes should suffice:

If DCount("*", "tblFS3Responses", "[Serial] = '"  & SID  & "' AND [FormID] = '" & Me.FormID & "'")>0 Then
0
 
gigifarrowAuthor Commented:
Ok I fixed that but it still keeps saying data mismatch.


If DCount("*", "tblFS3Responses", "[Serial]=" & Chr(34) & SID & Chr(34) And [FormID] = " Me.FormName & [QuestionID] = Me.frmFS3Questionaire.Form.QuestionID") > 0 Then
0
 
PatHartmanCommented:
Text values need to be delimited by single or double quotes -- "12ab3"
Dates need to be delimited by pound signs (#) -- #3/21/14#
Numeric values are not delimited at all - 12343

What is the data type of Serial?
What is the data type of FormID?
What is the data type of QuestionID?
What is the data type of Me.FormName?
What is the data type of Me.frmFS3Questionaire.Form.QuestionID

AND - your syntax is still not correct.  Go back and review my example to see how you create a compound condition.  You need to get a grip on what is inside of quotes and what is outside.  Inside the quotes will be strings.  Outside will be the references to columns on your forms.

Frequently it helps to create a separate variable and build the string in there.  That way it is easy to evaluate at runtime to see where there might be a problem.

Dim strWhere as String
strWhere = "[Serial]=" & Chr(34) & SID & Chr(34) & " And [FormID] = " & Me.FormName &  " AND [QuestionID] = " & Me.frmFS3Questionaire.Form.QuestionID

AND is the relational operator that is used to combine  conditional statements.  Ampersand (&) is the concatenation operator that is used to combine the different elements to build a string.  The string you are building looks just like a WHERE clause but without the WHERE.

If DCount("*", "tblFS3Responses", strWhere) > 0 Then

strWhere will look something like the following if you print it to the debug window.

[Serial] = 'ab123' AND [FormID] = 123 AND [QuestionID] = 456

PS - lay off the square brackets unless you need them.  They just get in the way of typing and reading.
0
 
gigifarrowAuthor Commented:
Thank you for the great information I am not getting a errror message but is not giving out a message when its duplicate.When I put a "And"  in front of FormID I get a data missmatch error.
serial  = Text
FormID = number  (In the form the name of the field is FormName )


    Dim SID As String
    Dim stLinkCriteria As String
    Dim rsc As DAO.Recordset

    Set rsc = Me.RecordsetClone

    SID = Me.[Serial]
    stLinkCriteria = "[Serial]=" & "'" & SID & "'"

If DCount("*", "tblFS3Responses", "[Serial] = '" & SID & "' &[FormID] = '" & Me.FormName & "'") > 0 Then
  'Undo duplicate entry
        Me.Undo
        'Message box warning of duplication
        MsgBox "Warning Vehicle Serial  Number " _
             & SID & " WARNING, Duplicate Record!" _
             & vbCr & vbCr & "You Can Not Save This Information.", _
               vbInformation, "Duplicate Information"
        'Go to record of original  Number
        rsc.FindFirst stLinkCriteria
        Me.Bookmark = rsc.Bookmark
        'Go to record of original Serial Number
    Set rsc = Nothing
    Exit Sub
    End If
0
 
PatHartmanCommented:
As I said - Your syntax is WRONG.  The WHERE argument of the DCount()  will look like -
[Serial] = 'xxx' &[FormID] = 'formname'
It should look like
[Serial] = 'xxx' AND [FormID] = 'formname'

Please do what I suggested and build the string in a variable so you can see where your mistake is.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now