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
402 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 48

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
 

Author Comment

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

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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

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 34

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 34

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 34

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Read about achieving the basic levels of HRIS security in the workplace.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now