Bookmarks

Yahoo Gmail Google Facebook Delicious Twitter Reddit Stumpleupon Myspace Digg

Search queries

error 800c5000, Wwwxxxc,memo, inserting images in postgresql, sending diag reset !!, bhavin retain ni style words, Www.ottest3, Php-cgi.exe not found , unable to change directory to null, www.xxx.con, kerio and iis post same error

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

#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" <wpw0501@hotmail.com> 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" <wpw0501@hotmail.com> 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