Descision based on a query
in [Queries]
|
From: poorboy13 via AccessMonster.com on 20 May 2010 14:10 Hello, I'm hoping this should be easy - I just can't seem to find the answer. I'm guessing my syntax is wrong but I can not figure it out. I am making a Db for work to keep track of Tasks and Performance. The 2 main functions I am trying to keep track of is total hours signed in and how long it takes to complete each of the tasks assigned to the Employee. I'm trying to put a small check in it that an employee can not begin a task without being signed in for the day. This is being tracked on 2 different tables. (Attendance.tbl and Times.tbl) So in the form to begin the task when you click on the command button I want it to run a query, in the background, of the attendance table to see if the employee is present (Yes/No Check Box). If No it will bring up a MsgBox to tell him he has not signed in yet and then cancel the operation and not put anything in the Times table. If yes then it will go through and complete the form with the date and time it was started. I have it so that it runs the query, not in the back ground but at least it runs, The Problem is regardless of whether the employee is signed in or not I still get the message that he is not signed in. Here is the code so far: Private Sub Command14_Click() DoCmd.OpenQuery ("qry_GetWork") If Present = 0 Then MsgBox "Please Sign in to begin working!", vbOKOnly, "Can Not Continue" DoCmd.Close 'To Close the Query Window DoCmd.Close 'To Close the Form Else StartDate.Locked = False StartTime.Locked = False StartDate = Date StartTime = Now() StartTime.Locked = True StartDate.Locked = True DoCmd.Close 'Close Form End If End Sub I can live with the query window showing if the thing would get the right answer. Or maybe there is a completely different way I should attack it???? Thanks, in Advance! -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201005/1
From: Marshall Barton on 21 May 2010 17:51 poorboy13 via AccessMonster.com wrote: >I'm hoping this should be easy - I just can't seem to find the answer. I'm >guessing my syntax is wrong but I can not figure it out. >I am making a Db for work to keep track of Tasks and Performance. >The 2 main functions I am trying to keep track of is total hours signed in >and how long it takes to complete each of the tasks assigned to the Employee. > >I'm trying to put a small check in it that an employee can not begin a task >without being signed in for the day. This is being tracked on 2 different >tables. (Attendance.tbl and Times.tbl) >So in the form to begin the task when you click on the command button I want >it to run a query, in the background, of the attendance table to see if the >employee is present (Yes/No Check Box). > >If No it will bring up a MsgBox to tell him he has not signed in yet and then >cancel the operation and not put anything in the Times table. >If yes then it will go through and complete the form with the date and time >it was started. >I have it so that it runs the query, not in the back ground but at least it >runs, >The Problem is regardless of whether the employee is signed in or not I still >get the message that he is not signed in. > >Here is the code so far: >Private Sub Command14_Click() >DoCmd.OpenQuery ("qry_GetWork") > If Present = 0 Then > MsgBox "Please Sign in to begin working!", vbOKOnly, "Can Not Continue" > DoCmd.Close 'To Close the Query Window > DoCmd.Close 'To Close the Form > > Else > >StartDate.Locked = False >StartTime.Locked = False >StartDate = Date >StartTime = Now() >StartTime.Locked = True >StartDate.Locked = True >DoCmd.Close 'Close Form > End If >End Sub > >I can live with the query window showing if the thing would get the right >answer. >Or maybe there is a completely different way I should attack it???? Opening a query's datasheet does not make the query's data available to your VBA code. In your case, I think you want to use the DLookup function. How you would do that depends on what you have in the query, probably something vaguely like: If DLookup("Present", "Attendance", "EmpID=" & something _ & " And datefield= Date()") = 0 Then -- Marsh MVP [MS Access]
From: poorboy13 via AccessMonster.com on 24 May 2010 13:53 I appreciate the response - However, I have tried every which way I can think of to write it and I'm not coming up with a correct answer. To Clarify; In the query "qry_GetWork" I have specified the criteria for "ShiftDate = Date()" and "EmpID = [Forms]![Begin]![Employee ID]". (This, by itself, works fine.) So as best I can tell the If statement in VBA should look like this: If DLookup("Present", "qry_GetWork") <> -1 Then Now I get the opposite problem everything is going through like the box is checked. Even if it is not, and even if there are no records at all in the query. I did the "<>-1" because if I have not signed in yet then the query will not return any records. Is that wrong? Thanks again! Marshall Barton wrote: >>I'm hoping this should be easy - I just can't seem to find the answer. I'm >>guessing my syntax is wrong but I can not figure it out. >[quoted text clipped - 41 lines] >>answer. >>Or maybe there is a completely different way I should attack it???? > >Opening a query's datasheet does not make the query's data >available to your VBA code. > >In your case, I think you want to use the DLookup function. >How you would do that depends on what you have in the query, >probably something vaguely like: > If DLookup("Present", "Attendance", "EmpID=" & something _ > & " And datefield= Date()") = 0 Then > -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201005/1
From: Marshall Barton on 24 May 2010 15:40 If the query does not return any records then the DLookup will faill to find anything so the result will be Null making the If statement fail. If someone is checking in, there won't be a record yet, right? On this case you should check for Null: If IsNull(DLookup("Present", "qry_GetWork")) Then Are you sure the Present field in its table is a Yes/No field? If it is, double check the value of the field is either 0 or -1 (eg. SQL Server uses 0 and +1 instead of Access's 0 and -1). If none of that helps, try my earlier suggestion skip the the query and have DLookup go directly to the table. poorboy13 via AccessMonster.com wrote: >I appreciate the response - However, I have tried every which way I can think >of to write it and I'm not coming up with a correct answer. > >To Clarify; >In the query "qry_GetWork" I have specified the criteria for "ShiftDate = >Date()" and "EmpID = [Forms]![Begin]![Employee ID]". (This, by itself, works >fine.) > >So as best I can tell the If statement in VBA should look like this: > If DLookup("Present", "qry_GetWork") <> -1 Then > >Now I get the opposite problem everything is going through like the box is >checked. Even if it is not, and even if there are no records at all in the >query. > >I did the "<>-1" because if I have not signed in yet then the query will not >return any records. >Is that wrong? > > >Marshall Barton wrote: >>>I'm hoping this should be easy - I just can't seem to find the answer. I'm >>>guessing my syntax is wrong but I can not figure it out. >>[quoted text clipped - 41 lines] >>>answer. >>>Or maybe there is a completely different way I should attack it???? >> >>Opening a query's datasheet does not make the query's data >>available to your VBA code. >> >>In your case, I think you want to use the DLookup function. >>How you would do that depends on what you have in the query, >>probably something vaguely like: >> If DLookup("Present", "Attendance", "EmpID=" & something _ >> & " And datefield= Date()") = 0 Then >> -- Marsh MVP [MS Access]
From: poorboy13 via AccessMonster.com on 26 May 2010 08:17 The If IsNull did the trick! Thank you Thank you I appreciate your willingness to share your knowledge!! Thanks again, Scott Pawling Marshall Barton wrote: >If the query does not return any records then the DLookup >will faill to find anything so the result will be Null >making the If statement fail. If someone is checking in, >there won't be a record yet, right? On this case you should >check for Null: > If IsNull(DLookup("Present", "qry_GetWork")) Then > >Are you sure the Present field in its table is a Yes/No >field? If it is, double check the value of the field is >either 0 or -1 (eg. SQL Server uses 0 and +1 instead of >Access's 0 and -1). > >If none of that helps, try my earlier suggestion skip the >the query and have DLookup go directly to the table. > >>I appreciate the response - However, I have tried every which way I can think >>of to write it and I'm not coming up with a correct answer. >[quoted text clipped - 29 lines] >>> If DLookup("Present", "Attendance", "EmpID=" & something _ >>> & " And datefield= Date()") = 0 Then > -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201005/1
|
Pages: 1 Prev: Left join in query Next: Multiple Totals Columns in a Pivot Table |