asked on

stLinkCriteria Syntax Error

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 :)

MikeToole

< stLinkCriteria = "[DISTANCE]=" & strDist & " AND [LOCATE2]=" & strLoc2 & " AND [DIRECTION]=" & strDir & "'" >
You're missing a single quote - it should be [DIRECTION]='" &

MikeToole

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.

naeemahmad

ASKER

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

ASKER CERTIFIED SOLUTION

MikeToole

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

naeemahmad

ASKER

ok let me try it.. also..
[DISTANCE] is a number
[LOCATE2] and [DIRECTION] are text

Thanks..

naeemahmad

ASKER

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!

naeemahmad

ASKER

Thanks Mike! Great Job!

naeemahmad

ASKER

Quick Additional Question:

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

Thanks :)

naeemahmad

ASKER

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 :)