Bookmarks

Yahoo Gmail Google Facebook Delicious Twitter Reddit Stumpleupon Myspace Digg

Search queries



Links

Issociate
Impressum

#1: Run Time Error 3075 Missing Operator

Posted on 2005-11-29 01:10:40 by William

Hello Everyone,

I'm using Access 2000. I entered the following code to do some data
validation, however it is not working. I'm trying to ensure that the
user cannot enter a duplicate inspection date for the same room. I'm
getting the following error message: Runtime error 3075. Syntax error
(missing operator) in query expression '(Room = 189B) AND
(InspectionDate = #10/05/05#)'.

Here is the code:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim strWhere As String 'Where clause to lookup dupe.
Dim varResult As Variant 'Result of lookup: null if no dupe.

If IsNull(Me.InspectionDate) Or IsNull(Room) Then
Cancel = True
MsgBox "Both Room Number and Inspection Date are required."
Else
'Build the Where clause.
strWhere = "(Room = " & Me.Room & ") AND (InspectionDate = " &
Format(Me.InspectionDate, "\#mm\/dd\/yy\#" & ")")
'Record doesn't clash with itself.
If Not Me.NewRecord Then
strWhere = strWhere & " AND (InspectionID <> " &
Me.InspectionID & ")"
End If
'Do the lookup.
varResult = DLookup("InspectionID", "tblInspections", strWhere)
If Not IsNull(varResult) Then
Cancel = True
MsgBox "Inspection Record No." & varResult & " has already
been entered.", vbExclamation, "Duplicate Entry"
Me.InspectionDate.SetFocus
End If
End If
End Sub

Thanks for your help

William

Report this message

Mr Ad

Google

#2: Re: Run Time Error 3075 Missing Operator

Posted on 2005-11-29 01:49:29 by mbpatpas

On 28 Nov 2005 16:10:40 -0800, "william" wrote:

>Hello Everyone,
>
>I'm using Access 2000. I entered the following code to do some data
>validation, however it is not working. I'm trying to ensure that the
>user cannot enter a duplicate inspection date for the same room. I'm
>getting the following error message: Runtime error 3075. Syntax error
>(missing operator) in query expression '(Room = 189B) AND
>(InspectionDate = #10/05/05#)'.
>
>Here is the code:
>
>Private Sub Form_BeforeUpdate(Cancel As Integer)
>
>Dim strWhere As String 'Where clause to lookup dupe.
> Dim varResult As Variant 'Result of lookup: null if no dupe.
>
> If IsNull(Me.InspectionDate) Or IsNull(Room) Then
> Cancel = True
> MsgBox "Both Room Number and Inspection Date are required."
> Else
> 'Build the Where clause.
> strWhere = "(Room = " & Me.Room & ") AND (InspectionDate = " &
>Format(Me.InspectionDate, "\#mm\/dd\/yy\#" & ")")

Looks like the above should be (watch word wrap):

strWhere = "(Room = '" & Me.Room & "') AND (InspectionDate =
" & Format(Me.InspectionDate, "\#mm\/dd\/yy\#" & ")")


Note that I enclosed the Me.Room control value in single quotes.

mike

> 'Record doesn't clash with itself.
> If Not Me.NewRecord Then
> strWhere = strWhere & " AND (InspectionID <> " &
>Me.InspectionID & ")"
> End If
> 'Do the lookup.
> varResult = DLookup("InspectionID", "tblInspections", strWhere)
> If Not IsNull(varResult) Then
> Cancel = True
> MsgBox "Inspection Record No." & varResult & " has already
>been entered.", vbExclamation, "Duplicate Entry"
> Me.InspectionDate.SetFocus
> End If
> End If
>End Sub
>
>Thanks for your help
>
>William
>

Report this message

#3: Re: Run Time Error 3075 Missing Operator

Posted on 2005-11-29 02:36:03 by William

Thanks Mike. That worked perfectly. Just curious, why did that change
work? The [Room] field is a text data type. Is that why you need the
single quotes?

William

Report this message

#4: Re: Run Time Error 3075 Missing Operator

Posted on 2005-11-29 23:00:41 by mbpatpas

On 28 Nov 2005 17:36:03 -0800, "william" wrote:

>Thanks Mike. That worked perfectly. Just curious, why did that change
>work? The [Room] field is a text data type. Is that why you need the
>single quotes?

You got it.

mike

Report this message