UPDATE query syntax help!

[Guest]

Hey all,

I am working on some time tracking stuff for my database. I have the
employee hours entering part working fine. Now I want to be able have a
manager verify the time sheets. So far I have an unbound form with 3 controls
(Employee, start date, end date) which simply filter a subform based on a
query of the time tracking details.

I have a button which, when pressed, should update all the records displayed
such that the validation field is set to "true". I'm doing this with an
update query but so far I'm getting syntax errors.

Here is the current sql string that I'm using:

"UPDATE tbl_Time_Tracking_Details" & _
"Set tbl_Time_Tracking_Details.Validation = True Where Time_Track_ID" & _
"=(SELECT Time_Track_ID FROM tbl_Time_Tracking WHERE" & _
"EmpID = " & Employee & "AND date>" & start_date & _
"AND date<" & end_date & ")"

For the time tracking there are 2 tables. First one has an identifier
(Time_Track_ID), the employeeID and date. The second table is linked through
Time_Track_ID and has its own identifier (Time_Track_Details_ID) along with
some project and work related fields, the number of hours worked, and a
validation check box.

So as you can see I need to update the details table based on info in the
main table with the update query.

Any help is appreciated!

RY

 

[Dirk Goldgar]

Hey all,

I am working on some time tracking stuff for my database. I have the
employee hours entering part working fine. Now I want to be able have
a manager verify the time sheets. So far I have an unbound form with
3 controls (Employee, start date, end date) which simply filter a
subform based on a query of the time tracking details.

I have a button which, when pressed, should update all the records
displayed such that the validation field is set to "true". I'm doing
this with an update query but so far I'm getting syntax errors.

Here is the current sql string that I'm using:

"UPDATE tbl_Time_Tracking_Details" & _
"Set tbl_Time_Tracking_Details.Validation = True Where Time_Track_ID"
& _ "=(SELECT Time_Track_ID FROM tbl_Time_Tracking WHERE" & _
"EmpID = " & Employee & "AND date>" & start_date & _
"AND date<" & end_date & ")"

For the time tracking there are 2 tables. First one has an identifier
(Time_Track_ID), the employeeID and date. The second table is linked
through Time_Track_ID and has its own identifier
(Time_Track_Details_ID) along with some project and work related
fields, the number of hours worked, and a validation check box.

So as you can see I need to update the details table based on info in
the main table with the update query.

Any help is appreciated!

RY

I see several problems. Is the subquery -- "(SELECT Time_Track_ID FROM
tbl_Time_Tracking WHERE ...)" -- expected to return more than one
Time_Track_ID? If so, you need to use the In operator to compare
tbl_Time_Tracking_Details.Time_Track_ID to that set of IDs, instead of
the '=' operator.

Also, you're missing spaces in the SQL string in various places, and you
need to put square brackets around "date", since otherwise that will
probably be interpreted as a reference to the Date function. ("Date"
really isn't a good name for a field, for this reason.) And you'll need
to format your date values with the date-literal "quote", '#'. It would
be best, also, to ensure that they are expressed in an unambiguous
format. Jet SQL prefers MM/DD/YYYY.

So try something like this:

"UPDATE tbl_Time_Tracking_Details " & _
"Set tbl_Time_Tracking_Details.Validation = True " & _
"WHERE Time_Track_ID In " & _
"(SELECT Time_Track_ID FROM tbl_Time_Tracking " & _
"WHERE EmpID = " & Employee & _
" AND [date]>" & Format(start_date, "\#mm/dd/yyyy\#") & _
" AND [date]<" & Format(end_date, "\#mm/dd/yyyy\#") & ")"

I'm not sure whether your date comparisons should be using >= and <=
instead of > and <. That's up to you and the logic of your application.

 

[Guest]

Dirk,

That was awesome. Worked great first time. Thanks for mentioning the >= and
<=, totally forgot.

RY

Hey all,

I am working on some time tracking stuff for my database. I have the
employee hours entering part working fine. Now I want to be able have
a manager verify the time sheets. So far I have an unbound form with
3 controls (Employee, start date, end date) which simply filter a
subform based on a query of the time tracking details.

I have a button which, when pressed, should update all the records
displayed such that the validation field is set to "true". I'm doing
this with an update query but so far I'm getting syntax errors.

Here is the current sql string that I'm using:

"UPDATE tbl_Time_Tracking_Details" & _
"Set tbl_Time_Tracking_Details.Validation = True Where Time_Track_ID"
& _ "=(SELECT Time_Track_ID FROM tbl_Time_Tracking WHERE" & _
"EmpID = " & Employee & "AND date>" & start_date & _
"AND date<" & end_date & ")"

For the time tracking there are 2 tables. First one has an identifier
(Time_Track_ID), the employeeID and date. The second table is linked
through Time_Track_ID and has its own identifier
(Time_Track_Details_ID) along with some project and work related
fields, the number of hours worked, and a validation check box.

So as you can see I need to update the details table based on info in
the main table with the update query.

Any help is appreciated!

RY

I see several problems. Is the subquery -- "(SELECT Time_Track_ID FROM
tbl_Time_Tracking WHERE ...)" -- expected to return more than one
Time_Track_ID? If so, you need to use the In operator to compare
tbl_Time_Tracking_Details.Time_Track_ID to that set of IDs, instead of
the '=' operator.

Also, you're missing spaces in the SQL string in various places, and you
need to put square brackets around "date", since otherwise that will
probably be interpreted as a reference to the Date function. ("Date"
really isn't a good name for a field, for this reason.) And you'll need
to format your date values with the date-literal "quote", '#'. It would
be best, also, to ensure that they are expressed in an unambiguous
format. Jet SQL prefers MM/DD/YYYY.

So try something like this:

"UPDATE tbl_Time_Tracking_Details " & _
"Set tbl_Time_Tracking_Details.Validation = True " & _
"WHERE Time_Track_ID In " & _
"(SELECT Time_Track_ID FROM tbl_Time_Tracking " & _
"WHERE EmpID = " & Employee & _
" AND [date]>" & Format(start_date, "\#mm/dd/yyyy\#") & _
" AND [date]<" & Format(end_date, "\#mm/dd/yyyy\#") & ")"

I'm not sure whether your date comparisons should be using >= and <=
instead of > and <. That's up to you and the logic of your application.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)