Where's the missing operator?

Question

I created a button on a form which opens another form where the AdjudicatorKey and Date fields in the second form equals the same fields in the first form.

In the On Click event I have the following code (along with the Dim, etc. lines)

    stDocName = "frmMiniSchedule"

    DoCmd.OpenForm stDocName, , , "[AdjudicatorKey] = " & Me.AdjudicatorKey & " AND [Date] = " & Me.Date & ""

when I click the button I get this error message:

Syntax error (missing operator) in query expression ‘[AdjudicatorKey] = 13 AND [Date] = Saturday November 13’.

Where's the missing operator?

Thanks in advance.

Jerry

Answer 1

"JWCrosby" wrote in message

news:*** Email address is removed for privacy ***...

> Thanks, Dirk. I created this database in an earlier life when I didn't

> know better than to use the field name "Date."  So I made some changes and

> in the queries for the two forms changed the name of the Date field to

> AssignedDate. Actually, that field is a string (ex, "Saturday November

> 20") and not really a date.

>

> So, now I have this code, but still get the same error:

>

>    DoCmd.OpenForm stDocName, , , "[AdjudicatorKey] = " & Me.AdjudicatorKey

> & " AND [AssignedDate] = " & Me.AssignedDate

>

> Appreciate any further help.

>

> Jerry

>

If it's not really a date, you won't use the # character to delimit the

value, but since what it really is is a text value, you need to put it in

quotes.  Assuming it won't ever contain the single-quote character ('), you

can use that as the quote for the text value, which makes life a bit easier:

'

DoCmd.OpenForm stDocName, , , _

"[AdjudicatorKey] = " & Me.AdjudicatorKey & _

" AND [AssignedDate] = '" & Me.AssignedDate & "'"

---

Dirk Goldgar, MS Access MVP

Access tips: www.datagnostics.com/tips.html

Answer 2

Blessings on you, Dirk, that did it!

Thanks!

Jerry

Answer 3

Thanks, Dirk. I created this database in an earlier life when I didn't know better than to use the field name "Date."  So I made some changes and in the queries for the two forms changed the name of the Date field to AssignedDate. Actually, that field is a string (ex, "Saturday November 20") and not really a date.

So, now I have this code, but still get the same error:

    DoCmd.OpenForm stDocName, , , "[AdjudicatorKey] = " & Me.AdjudicatorKey & " AND [AssignedDate] = " & Me.AssignedDate

Appreciate any further help.

Jerry

Answer 4

"JWCrosby" wrote in message

news:*** Email address is removed for privacy ***...

>I created a button on a form which opens another form where the

>AdjudicatorKey and Date fields in the second form equals the same fields in

>the first form.

>

> In the On Click event I have the following code (along with the Dim, etc.

> lines)

>

>

>    stDocName = "frmMiniSchedule"

>

>    DoCmd.OpenForm stDocName, , , "[AdjudicatorKey] = " & Me.AdjudicatorKey

> & " AND [Date] = " & Me.Date & ""

>

> when I click the button I get this error message:

>

> Syntax error (missing operator) in query expression ‘[AdjudicatorKey] = 13

> AND [Date] = Saturday November 13’.

>

> Where's the missing operator?

>

You need to enclose the date you want in the date-delimiters (#), and you

have to format it in such a way that it can be interpreted correctly.  The

standard for Access queries is MM/DD/YYYY format, although any unambiguous

format will do.  In your example above, your date is being placed into the

criteria string without any year.  I'm not sure without testing whether that

will be interpreted correctly or not.  I recommend this:

'

DoCmd.OpenForm stDocName, , , _

"[AdjudicatorKey] = " & Me.AdjudicatorKey & _

" AND [Date] = #" & Format(Me.Date, "mm/dd/yyyy") & "#"

'

By the way "Date" is a terrible name for a field, as it can easily be

misinterpreted as the built-in function, "Date".

---

Dirk Goldgar, MS Access MVP

Access tips: www.datagnostics.com/tips.html