Solved

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

Posted on 2014-03-19
13
404 Views
Last Modified: 2016-02-10
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
Comment
Question by:gigifarrow
13 Comments
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 39939801
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
 
LVL 38

Expert Comment

by:Jim P.
ID: 39939811
It looks like you're missing closing parentheses.
0
 

Author Comment

by:gigifarrow
ID: 39939812
Thank you for your help I tried that but I get  message that says syntax error and then it highlights


"qryAppendQuestions"
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

Author Comment

by:gigifarrow
ID: 39939819
the number one is  a selection in the drop down. FormID is datatype is autonumber.
0
 
LVL 35

Accepted Solution

by:
PatHartman earned 500 total points
ID: 39940569
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
 

Author Comment

by:gigifarrow
ID: 39940767
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
 
LVL 35

Assisted Solution

by:PatHartman
PatHartman earned 500 total points
ID: 39940978
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
 

Author Comment

by:gigifarrow
ID: 39945178
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
 
LVL 35

Assisted Solution

by:PatHartman
PatHartman earned 500 total points
ID: 39945886
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
 

Author Comment

by:gigifarrow
ID: 39948797
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
 
LVL 35

Assisted Solution

by:PatHartman
PatHartman earned 500 total points
ID: 39949285
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question