?
Solved

stLinkCriteria Syntax Error

Posted on 2009-04-29
9
Medium Priority
?
573 Views
Last Modified: 2013-11-27
Hi Peoples :)

I need some help identifing the syntax error in my code.  My goal is make sure that no one is adding duplicate records using a data entry form..

The code is as follows:

    Dim strLoc1 As String
    Dim strLoc2 As String
    Dim strDist As String
    Dim strDir As String
    Dim stLinkCriteria As String
    Dim rsc As DAO.Recordset

    Set rsc = Me.RecordsetClone

    strLoc1 = Me.LOCATE1.Value
    strLoc2 = Me.LOCATE2.Value
    strDist = Me.DISTANCE.Value
    strDir = Me.DIRECTION.Value
   
        stLinkCriteria = "[DISTANCE]=" & strDist & " AND [LOCATE2]=" & strLoc2 & " AND [DIRECTION]=" & strDir & "'"

    If DCount("LOCATE2", "tblcounts", stLinkCriteria) > 0 Then
    If DCount("DISTANCE", "tblcounts", stLinkCriteria) > 0 Then
    If DCount("DIRECTION", "tblcounts", stLinkCriteria) > 0 Then
        Me.Undo
        MsgBox "Warning DUPLICATE COUNT " _
             & strLoc1 & " " & strLoc2 & " " & strDist & " " & strDir & " " & " has already been entered." _
             & vbCr & vbCr & "PLEASE VERIFY COUNT.", _
               vbInformation, "Duplicate Information"
   
    End If
    End If
    End If


The Error I get:

Syntax Error (Missing Operator) in Query expression..

ANY HELP WOULD BE GREATLY APPERICIATED !!  Thanks :)
0
Comment
Question by:naeemahmad
  • 6
  • 3
9 Comments
 
LVL 27

Expert Comment

by:MikeToole
ID: 24268174
< stLinkCriteria = "[DISTANCE]=" & strDist & " AND [LOCATE2]=" & strLoc2 & " AND [DIRECTION]=" & strDir & "'" >
You're missing a single quote - it should be [DIRECTION]='" &
0
 
LVL 27

Expert Comment

by:MikeToole
ID: 24268247
Also, your link criteria determine the records to be returned you don't need to count them three times - this would do the trick

If DCount("1", "tblcounts", stLinkCriteria) > 0 Then

The only function of the expression in the first parameter is that the count isn't incremented if a row is returned but the first parameter evaluates to Null.
0
 

Author Comment

by:naeemahmad
ID: 24270639
Mike,

Thanks for your responce.  I tried the single quote fix, but I still got a missing operator error.  I have attached the specific error (.bmp) screen shot.

Here is the updated code..

'-----------------------
Dim strLoc1 As String
    Dim strLoc2 As String
    Dim strDist As String
    Dim strDir As String
    Dim stLinkCriteria As String
    Dim rsc As DAO.Recordset

    Set rsc = Me.RecordsetClone

    strLoc1 = Me.LOCATE1.Value
    strLoc2 = Me.LOCATE2.Value
    strDist = Me.DISTANCE.Value
    strDir = Me.DIRECTION.Value
   
        stLinkCriteria = "[DISTANCE]=" & strDist & " AND [LOCATE2]=" & strLoc2 & " AND [DIRECTION]='" & strDir & "'"

     If DCount("1", "tblcounts", stLinkCriteria) > 0 Then
        Me.Undo
        MsgBox "Warning DUPLICATE COUNT " _
             & strLoc1 & " " & strLoc2 & " " & strDist & " " & strDir & " " & " has already been entered." _
             & vbCr & vbCr & "PLEASE VERIFY COUNT.", _
               vbInformation, "Duplicate Information"
   
    End If
'-----------------------

This thing is driving me nutz :)  I'm almost there.. I just need to get the syntax right.. Can you see where its failing?

Thanks a bunch Mike !
error.bmp
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 27

Accepted Solution

by:
MikeToole earned 1200 total points
ID: 24271140
Locate2 is a text field so it will also need quotes round the value:

        stLinkCriteria = "[DISTANCE]=" & strDist & " AND [LOCATE2]='" & strLoc2 & "' AND [DIRECTION]='" & strDir & "'"

0
 

Author Comment

by:naeemahmad
ID: 24271229
ok  let me try it.. also..
[DISTANCE] is a number
[LOCATE2] and [DIRECTION] are text

Thanks..
0
 

Author Comment

by:naeemahmad
ID: 24271527
Awesome. It worked.

I just have to remember the syntax's for the stLinkCritera variables..

Text - (') single quote
Number - (") double quotes
Date - (#) pound sign

Hope that wuz correct :)

Thanks again for the wonderdul help Mike!  Great job!
0
 

Author Closing Comment

by:naeemahmad
ID: 31576138
Thanks Mike!  Great Job!
0
 

Author Comment

by:naeemahmad
ID: 24271672
Quick Additional Question:

What is the correct syntax if I want to add a date variable also?

Thanks :)
0
 

Author Comment

by:naeemahmad
ID: 24271931
Mike..

I think I got it..

stLinkCriteria = "[DISTANCE]=" & strDist & " AND [LOCATE2]='" & strLoc2 & "' AND [DIRECTION]='" & strDir & "' AND [DATE_]= #" & strDate & "#"

I believe that should be the correct syntax, right?

Thanks again for your help.  I think all is working well, at least for now :)
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

807 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