Syntax error (missing operator)

[Opal]

I am trying to delete the data in one field
in a table via a module as follows:

Dim dbObject As DAO.Database
Dim strQuery As String
Dim HoldUnsafeTally As Variant


HoldUnsafeTally = Forms!frmDriveAudit!subfrmUnsafe.Form!
UnsafeTally.Value
Set dbObject = CurrentDb
strQuery = "UPDATE Unsafe " & _
"SET UnsafeTally = Null " & _
"WHERE UnsafeTally = " & Forms!frmDriveAudit!subfrmUnsafe.Form!
UnsafeTally


dbObject.Execute strQuery, dbFailOnError

I get the following error:

Syntax error (missing operator) in query expression 'UnsafeTally ='.

and for the life of me cannot figure out why.... :-S

Can anyone help?

 

[Marshall Barton]

I am trying to delete the data in one field
in a table via a module as follows:

Dim dbObject As DAO.Database
Dim strQuery As String
Dim HoldUnsafeTally As Variant

HoldUnsafeTally = Forms!frmDriveAudit!subfrmUnsafe.Form!
UnsafeTally.Value
Set dbObject = CurrentDb
strQuery = "UPDATE Unsafe " & _
"SET UnsafeTally = Null " & _
"WHERE UnsafeTally = " & Forms!frmDriveAudit!subfrmUnsafe.Form!
UnsafeTally

dbObject.Execute strQuery, dbFailOnError

I get the following error:

Syntax error (missing operator) in query expression 'UnsafeTally ='.

It looks like the value you are trying to match is Null so
the where clause ends up being:
WHERE UnsafeTally =

 

[Opal]

Sorry, Marshall, you lost me???

 

[Marshall Barton]

Sorry, Marshall, you lost me???

Your code that creates the SQL statement concatenates the
value of
Forms!frmDriveAudit!subfrmUnsafe.Form!UnsafeTally
into the WHERE clause. When the value of
Forms!frmDriveAudit!subfrmUnsafe.Form!UnsafeTally
is null, the Where clause will end up looking like
WHERE UnsafeTally =
which is incomplete because there is nothing being compared
to the UnsafeTally field.

A good way to see what your SQL statement ends up being is
to temporily add a
Debug.Print strQuery
or
MsgBox strQuery
line before the Execute line.


If you are certain that there is a non null value in the
UnsafeTally text box, then maybe the space between the ! and
Forms!frmDriveAudit!subfrmUnsafe.Form!UnsafeTally is really
there and not just a posting hiccup. If so, remove that
extra space.

 

[Opal]

The Forms!frmDriveAudit!subfrmUnsafe.Form!UnsafeTally
text box appears on the form 17 times as it a continuous
form bound to a table with 17 entries and will have a value
or will be null depending on the user entry as it corresponds
to the 17 separate description entries in the table.

I don't see any extra spaces in my code.....

 

[Marshall Barton]

The Forms!frmDriveAudit!subfrmUnsafe.Form!UnsafeTally
text box appears on the form 17 times as it a continuous
form bound to a table with 17 entries and will have a value
or will be null depending on the user entry as it corresponds
to the 17 separate description entries in the table.

There is only one UnsafeTally text box. It is just
displayed 17 times. The value of the UnsafeTally text box
is the value in the current record. The other copies are
just images for you to look at.

And what do you expect to happen when the value of the
current record's UnsafeTally field/text box is null? It
doesn't appear to make sunse for your Update query to update
all the records in that case, but if you can explain more
about what should happen, we can probably work it out.

 

[Opal]

Hi Marsh,

The form tracks specific unsafe activities for Driving
audits. The table bound to the subform has 17 entries
from failure to wearing a seatbelt to failure to come
to a complete stop, etc. The user tallies these
unsafe activities and records them on the form.
There is a separate module which writes these
unsafe activities to a separate table recording
user information, date, etc. I need to now clear
the form of entries for the next user. I do not want
to track the tally in this table. Each user will have
some entries in the 17 fields, but rarely have one in
all, so some fields will be null.

Does this help explain it better?

 

[Marshall Barton]

The form tracks specific unsafe activities for Driving
audits. The table bound to the subform has 17 entries
from failure to wearing a seatbelt to failure to come
to a complete stop, etc. The user tallies these
unsafe activities and records them on the form.
There is a separate module which writes these
unsafe activities to a separate table recording
user information, date, etc. I need to now clear
the form of entries for the next user. I do not want
to track the tally in this table. Each user will have
some entries in the 17 fields, but rarely have one in
all, so some fields will be null.

Does this help explain it better?

Somewhat better, but what does that have to do with running
an UPDATE query?

And, how does the UPDATE query relate to the form and table?
Does it get involved with your "separate module which writes
these unsafe activities to a separate table"?

I do not understand what "clearing the form" means. Is this
part of the UPDATE query or what?? Normally, the form would
be bound to a table/query and "clearing" the current values
is just a matter of moving to the new record.

 

[Opal]

I don't want to move to a new record.
The table to which the subform is bound is
not to contain a record of the tally.
Its more or less a place holder. By
clearing the form, I mean to remove any
tally record from the form (table). I do
not want to delete all records on the
table, just the number in the tally field.

 

[Marshall Barton]

I don't want to move to a new record.
The table to which the subform is bound is
not to contain a record of the tally.
Its more or less a place holder. By
clearing the form, I mean to remove any
tally record from the form (table). I do
not want to delete all records on the
table, just the number in the tally field.

OK, but when the UnsafeTally value on the form is null, what
is supposed to happen in the Unsafe table?

If you do not want to do anything in that case, try using:

. . .
If Not IsNull() Then
strQuery = "UPDATE Unsafe " & _
"SET UnsafeTally = Null " & _
"WHERE UnsafeTally = " &
Forms!frmDriveAudit!subfrmUnsafe.Form!UnsafeTally

dbObject.Execute strQuery, dbFailOnError
End If

 

[Opal]

Hmmm... I get a compile error - Argument not optional
and it highlights the "IsNull"

 

[Marshall Barton]

Hmmm... I get a compile error - Argument not optional
and it highlights the "IsNull"

Sorry, that part was supposed to be:

If Not
IsNull(Forms!frmDriveAudit!subfrmUnsafe.Form!UnsafeTally)
Then

all on one line.

 

[Opal]

Okay no more errors, but it is not removing
the entries from the table ......

 

[Marshall Barton]

Okay no more errors, but it is not removing
the entries from the table ......

Are you sure there are records in the table that exactly
match the value on the form?

If the table field, UnsafeTally, is a Text field, then the
code needs to be:

strQuery = "UPDATE Unsafe " & _
"SET UnsafeTally = Null " & _
"WHERE UnsafeTally = """ &
Forms!frmDriveAudit!subfrmUnsafe.Form!UnsafeTally & """"

 

[Opal]

The form looks sort of like this:

(Unsafe description) (Unsafe Tally)
1. Not wearing seat belt 2
2. Not coming to a complete stop
3. Not Checking mirror 1
4. Failure to watch for pedestrians 1
..
..
..

The field is a numeric field. I need the numbers
above removed from the table in the Unsafe Tally
field but the Unsafe Description field is not to be
touched.

 

[Marshall Barton]

The form looks sort of like this:

(Unsafe description) (Unsafe Tally)
1. Not wearing seat belt 2
2. Not coming to a complete stop
3. Not Checking mirror 1
4. Failure to watch for pedestrians 1
.

The field is a numeric field. I need the numbers
above removed from the table in the Unsafe Tally
field but the Unsafe Description field is not to be
touched.

Well I guess I am lost again. How the form looks may be
part of the issue, but what the records in the table look
like is very important.

Something about all this just doesn't add up for me. Your
Update query would remove both of the 1s if the form text
box, UnsafeTally, contained a 1. Is that what you want?

It seems to me that the table would serve no purpose if it
did not have more fields that identify the driver, and some
other stuff related to the driver's test. If you do have a
set of records for each driver test, your query would
"clear" the value for everyone.

I'm beginning to think that I need to understand what you
are trying to accomplish instead of how you are trying to do
it. I guess I am wondering why this table exists, or what
purpose it is supposed to serve.

 

[Opal]

Marsh,

User enters audit information into one form.
If there are unsafe activities noted, it prompts
the frmDriveAudit to open for detailed inputs
of the unsafe activities. The code to input
the unsafe activities by day and auditor is as
follows:

Dim HoldInputDate As Date
Dim HoldShift As String
Dim HoldGLGroup As String
Dim HoldArea As String
Dim HoldAuditedBy As String
Dim HoldUnsafeTally As Variant
Dim HoldUnsafeID As Integer
Dim dbObject As DAO.Database
Dim UnsafeRS As DAO.Recordset
Dim HistoryRS As DAO.Recordset
Dim strQuery As String
Dim strquery1 As String

HoldInputDate = Forms!SafetyInputForm.TxtDate.Value
HoldShift = Forms!SafetyInputForm.TxtShift.Value
HoldGLGroup = Forms!SafetyInputForm.TxtGLNumber.Value
HoldArea = Forms!frmDriveAudit.txtArea.Value
HoldAuditedBy = Forms!frmDriveAudit.txtAuditedBy.Value

Set dbObject = CurrentDb
strQuery = "SELECT * FROM Unsafe "
strquery1 = "SELECT * FROM DriveAuditCheck "
Set UnsafeRS = dbObject.OpenRecordset(strQuery)
Set HistoryRS = dbObject.OpenRecordset(strquery1)
With UnsafeRS
.MoveFirst
Do While Not .EOF
If .Fields("UnsafeTally").Value = 0 Then
.MoveNext
Else
If .Fields("UnsafeTally").Value > 0 Then
HoldUnsafeTally = .Fields
("UnsafeTally").Value
HoldUnsafeID = .Fields("UnsafeID").Value
End If

If .Fields("UnsafeTally").Value > 0 Then
With HistoryRS
.AddNew
.Fields("InputDate").Value = HoldInputDate
.Fields("Group").Value = HoldGLGroup
.Fields("Shift").Value = HoldShift
.Fields("Auditedby").Value = HoldAuditedBy
.Fields("AuditArea").Value = HoldArea
.Fields("UnsafeID").Value = HoldUnsafeID
.Fields("UnsafeTally").Value = HoldUnsafeTally
.Update
HoldUnsafeTally = 0
End With
End If
End If

.MoveNext

Loop
End With
UnsafeRS.Close

All this data is written to the DriveAuditCheck table.
The Unsafe Table is what is bound to subfrmUnsafe.
This table is a place holder containing all the activities
that could noted as unsafe (17 in total). When an audit
is done, it is unlikely that all 17 activities would be
tallied, so there would be blank or null textboxes on
the form. This method allows me to better chart
the activity for management so that they can run
a bar chart report and determine if the unsafe
activities are increasing or decreasing week over week.

My DriveAuditCheck table looks like this:

FieldName DataType
DriveAuditID Auto Number
InputDate Date/Time
Group Text
Shift Text
AuditArea Text
AuditedBy Text
UnsafeID Number
UnsafeTally Number

My Unsafe table looks like this

FieldName DataType
UnsafeID Auto Number
UnsafeActivity Text
UnsafeTally Number

My chart report query SQL looks like this:

PARAMETERS [Forms]![ReportFrm]![TxtStartDate] DateTime,
[Forms]![ReportFrm]![TxtEndDate] IEEEDouble;
TRANSFORM Sum(qryDriveCheck.UnsafeTally) AS SumOfUnsafeTally
SELECT qryDriveCheck.UnsafeID
FROM qryDriveCheck
WHERE (((qryDriveCheck.InputDate) Between
[Forms]![ReportFrm]![TxtStartDate] And
[Forms]![ReportFrm]![TxtEndDate]))
GROUP BY qryDriveCheck.UnsafeDesc,
qryDriveCheck.InputDate, qryDriveCheck.UnsafeID
ORDER BY qryDriveCheck.UnsafeID
PIVOT qryDriveCheck.ShiftName;

My qryDriveCheck looks like:

SELECT DriveAuditCheck.InputDate, [Shift] & " Shift"
AS ShiftName, DriveAuditCheck.UnsafeTally,
Unsafe.UnsafeDesc, Unsafe.UnsafeID
FROM Unsafe INNER JOIN DriveAuditCheck
ON Unsafe.UnsafeID = DriveAuditCheck.UnsafeID
ORDER BY Unsafe.UnsafeID;

Does this help explain what exactly I'm trying to do??

 

[Marshall Barton]

User enters audit information into one form.
If there are unsafe activities noted, it prompts
the frmDriveAudit to open for detailed inputs
of the unsafe activities. The code to input
the unsafe activities by day and auditor is as
follows:

Dim HoldInputDate As Date
Dim HoldShift As String
Dim HoldGLGroup As String
Dim HoldArea As String
Dim HoldAuditedBy As String
Dim HoldUnsafeTally As Variant
Dim HoldUnsafeID As Integer
Dim dbObject As DAO.Database
Dim UnsafeRS As DAO.Recordset
Dim HistoryRS As DAO.Recordset
Dim strQuery As String
Dim strquery1 As String

HoldInputDate = Forms!SafetyInputForm.TxtDate.Value
HoldShift = Forms!SafetyInputForm.TxtShift.Value
HoldGLGroup = Forms!SafetyInputForm.TxtGLNumber.Value
HoldArea = Forms!frmDriveAudit.txtArea.Value
HoldAuditedBy = Forms!frmDriveAudit.txtAuditedBy.Value

Set dbObject = CurrentDb
strQuery = "SELECT * FROM Unsafe "
strquery1 = "SELECT * FROM DriveAuditCheck "
Set UnsafeRS = dbObject.OpenRecordset(strQuery)
Set HistoryRS = dbObject.OpenRecordset(strquery1)
With UnsafeRS
.MoveFirst
Do While Not .EOF
If .Fields("UnsafeTally").Value = 0 Then
.MoveNext
Else
If .Fields("UnsafeTally").Value > 0 Then
HoldUnsafeTally = .Fields
("UnsafeTally").Value
HoldUnsafeID = .Fields("UnsafeID").Value
End If

If .Fields("UnsafeTally").Value > 0 Then
With HistoryRS
.AddNew
.Fields("InputDate").Value = HoldInputDate
.Fields("Group").Value = HoldGLGroup
.Fields("Shift").Value = HoldShift
.Fields("Auditedby").Value = HoldAuditedBy
.Fields("AuditArea").Value = HoldArea
.Fields("UnsafeID").Value = HoldUnsafeID
.Fields("UnsafeTally").Value = HoldUnsafeTally
.Update
HoldUnsafeTally = 0
End With
End If
End If

.MoveNext

Loop
End With
UnsafeRS.Close

All this data is written to the DriveAuditCheck table.
The Unsafe Table is what is bound to subfrmUnsafe.
This table is a place holder containing all the activities
that could noted as unsafe (17 in total). When an audit
is done, it is unlikely that all 17 activities would be
tallied, so there would be blank or null textboxes on
the form. This method allows me to better chart
the activity for management so that they can run
a bar chart report and determine if the unsafe
activities are increasing or decreasing week over week.

My DriveAuditCheck table looks like this:

FieldName DataType
DriveAuditID Auto Number
InputDate Date/Time
Group Text
Shift Text
AuditArea Text
AuditedBy Text
UnsafeID Number
UnsafeTally Number

My Unsafe table looks like this

FieldName DataType
UnsafeID Auto Number
UnsafeActivity Text
UnsafeTally Number

My chart report query SQL looks like this:

PARAMETERS [Forms]![ReportFrm]![TxtStartDate] DateTime,
[Forms]![ReportFrm]![TxtEndDate] IEEEDouble;
TRANSFORM Sum(qryDriveCheck.UnsafeTally) AS SumOfUnsafeTally
SELECT qryDriveCheck.UnsafeID
FROM qryDriveCheck
WHERE (((qryDriveCheck.InputDate) Between
[Forms]![ReportFrm]![TxtStartDate] And
[Forms]![ReportFrm]![TxtEndDate]))
GROUP BY qryDriveCheck.UnsafeDesc,
qryDriveCheck.InputDate, qryDriveCheck.UnsafeID
ORDER BY qryDriveCheck.UnsafeID
PIVOT qryDriveCheck.ShiftName;

My qryDriveCheck looks like:

SELECT DriveAuditCheck.InputDate, [Shift] & " Shift"
AS ShiftName, DriveAuditCheck.UnsafeTally,
Unsafe.UnsafeDesc, Unsafe.UnsafeID
FROM Unsafe INNER JOIN DriveAuditCheck
ON Unsafe.UnsafeID = DriveAuditCheck.UnsafeID
ORDER BY Unsafe.UnsafeID;

Does this help explain what exactly I'm trying to do??

I think I get at least some of the picture. I would never
do it that way so that explains some of my confusion.

If I really do understand it, your original question is how
to clear any/all of the UnsafeTally values from the Unsafe
table. In that case, I think your problem is the Where
clause in the Update query. You don't care what the form's
UnsafeTally value is, you just want to clear the values in
the table so I think this code should be sufficient:

Dim strQuery As String
strQuery = "UPDATE Unsafe " & _
"SET UnsafeTally = Null " & _
"WHERE UnsafeTally Is Not Null"

CurrentDb.Execute strQuery, dbFailOnError

 

[Opal]

Thank you so much, Marsh! That did it.....

I can't believe how long I was struggling with that....

You said that you "would never do it that way"

Any advice you can offer would be appreciated.

I was using another DB (here at work) as my
example to pull this together but if you have any
advise to improve, I would be grateful.

 

[Marshall Barton]

Thank you so much, Marsh! That did it.....

I can't believe how long I was struggling with that....

You said that you "would never do it that way"

Any advice you can offer would be appreciated.

I was using another DB (here at work) as my
example to pull this together but if you have any
advise to improve, I would be grateful.

I don't know where to start. I guess the first and most
important thing is to gain a basic understanding of Database
Normalization (Google it or go to your local library).

One of the first three fundamental rules is that every field
in a table can ony depend on the table's primary key
field(s). This means that a table should never have a field
that can be determined from values in other fields in the
same record. Another thing that it means is that a table
should never contain a field that depends on data in other
records or, even worse, records in other tables. Your
Unsafe table should not contain the UnsafeTally field,
because it is dependent on the primary key in the drivers
table.

Another point is that your drivers table and the Unsafe
table have a many to many relationship that needs to be
modeled by adding a "junction" table (call it the
UnsafeDriver table) between them. Both the drivers table
and the Unsafe table would then have a one to many
relationship with the UnsafeDriver table. The UnsafeDriver
table's primay key would be a compound key that consists of
the primary keys from both of the other tables along with
the UnsafeTally value.

It should be fairly easy to create a continuous subform
based on a query like:

SELECT UnsafeDriver.UnsafeID, UnsafeDriver.UnsafeTally,
UnsafeDriver.driverID, Unsafe.UnsafeActivity
FROM Unsafe LEFT JOIN UnsafeDriver
ON Unsafe.UnsafeID = UnsafeDriver.UnsafeID

Then the subform's Link Master/Child properties would be set
to the driverID field. The UnsafeActivity text box on the
form should be both Locked and disabled so users can not
interact with it.

I'm not sure where your DriveAuditCheck table fits into
that, but I'm pretty sure that the UnsafeID and UnsafeTally
fields should not be there.