A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Descision based on a query



 
 
Thread Tools Display Modes
  #1  
Old May 20th, 2010, 07:10 PM posted to microsoft.public.access.queries
poorboy13 via AccessMonster.com
external usenet poster
 
Posts: 3
Default Descision based on a query

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/For...eries/201005/1

  #2  
Old May 21st, 2010, 10:51 PM posted to microsoft.public.access.queries
Marshall Barton
external usenet poster
 
Posts: 5,361
Default Descision based on a query

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]
  #3  
Old May 24th, 2010, 06:53 PM posted to microsoft.public.access.queries
poorboy13 via AccessMonster.com
external usenet poster
 
Posts: 3
Default Descision based on a query

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/For...eries/201005/1

  #4  
Old May 24th, 2010, 08:40 PM posted to microsoft.public.access.queries
Marshall Barton
external usenet poster
 
Posts: 5,361
Default Descision based on a query

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]
  #5  
Old May 26th, 2010, 01:17 PM posted to microsoft.public.access.queries
poorboy13 via AccessMonster.com
external usenet poster
 
Posts: 3
Default Descision based on a query

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/For...eries/201005/1

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:21 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.