Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 414
  • 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
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

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