SQL If Problem

[DS]

This statement is suppose to find an "Employee" in the "Time" table and
if the "IN" box is checked yes open the "Tables" form, else it opens the
"PayPad" form . I keep getting an Error on the FROM part.

Private Sub Command26_Click()
Dim mySQL As String
mySQL = " SELECT [EmployeeID],[IN] " & _
" FROM TIME " & _
" WHERE [EmployeeID]= " & Me.TextD & " """
DoCmd.RunSQL (mySQL)
If [Time].[IN] = -1 Then
DoCmd.OpenForm "Tables"
Else:
DoCmd.OpenForm "PayPad"
End If
End Sub

Thanks
DS

 

[Ken Snell [MVP]]

First, you're using Time as the name of a table. Time is a reserved word in
ACCESS, and refers to a VBA function. Therefore, you need to surround Time
in your SQL statement with [ ] characters:

mySQL = " SELECT [EmployeeID],[IN] " & _
" FROM [TIME] " & _
" WHERE [EmployeeID]= " & Me.TextD & " """


However, this SQL statement won't work for your code. DoCmd.RunSQL cannot be
used for a "SELECT" query; it's used for "action" queries only (append,
delete, maketable, update, etc.). I think the DLookup function will work for
you in this case:

Private Sub Command26_Click()
Dim lngIn As Long
lngIn = DLookup("[IN]", "[TIME]", "[EmployeeID]= " & Me.TextD)
If lngIn = -1 Then
DoCmd.OpenForm "Tables"
Else:
DoCmd.OpenForm "PayPad"
End If
End Sub


You're using TIME and IN in your table structure. Both are reserved words.
See these Knowledge Base articles for more information about reserved words
and symbols:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

 

[Klatuu]

Any time you do a DLookup into a variable, you should use a variant data type
to avoid Invalid Use of Null errors.

First, you're using Time as the name of a table. Time is a reserved word in
ACCESS, and refers to a VBA function. Therefore, you need to surround Time
in your SQL statement with [ ] characters:

mySQL = " SELECT [EmployeeID],[IN] " & _
" FROM [TIME] " & _
" WHERE [EmployeeID]= " & Me.TextD & " """


However, this SQL statement won't work for your code. DoCmd.RunSQL cannot be
used for a "SELECT" query; it's used for "action" queries only (append,
delete, maketable, update, etc.). I think the DLookup function will work for
you in this case:

Private Sub Command26_Click()
Dim lngIn As Long
lngIn = DLookup("[IN]", "[TIME]", "[EmployeeID]= " & Me.TextD)
If lngIn = -1 Then
DoCmd.OpenForm "Tables"
Else:
DoCmd.OpenForm "PayPad"
End If
End Sub


You're using TIME and IN in your table structure. Both are reserved words.
See these Knowledge Base articles for more information about reserved words
and symbols:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

--

Ken Snell
<MS ACCESS MVP>

This statement is suppose to find an "Employee" in the "Time" table and if
the "IN" box is checked yes open the "Tables" form, else it opens the
"PayPad" form . I keep getting an Error on the FROM part.

Private Sub Command26_Click()
Dim mySQL As String
mySQL = " SELECT [EmployeeID],[IN] " & _
" FROM TIME " & _
" WHERE [EmployeeID]= " & Me.TextD & " """
DoCmd.RunSQL (mySQL)
If [Time].[IN] = -1 Then
DoCmd.OpenForm "Tables"
Else:
DoCmd.OpenForm "PayPad"
End If
End Sub

Thanks
DS

 

[John Spencer]

1) Time is a reserved word - it means get the System Time.
2) Your Where clause is malformed - it is missing some quote marks
(assuming EmployeeID is a string).
3) You will get an error with DoCmd.RunSQL since your query is not a Action
Query (Delete, Insert, Update)
4) I would use the DLookup function for this
vResult = DLookup([In]","[Time]","EmployeeID=""" & Me.TextD & """")

If IsNull(vResult) then
Msgbox "Invalid EmployeeID"
ElseIF vResult = -1 then
'do something
Else
'do something else
End if

 

[Ken Snell [MVP]]

Completely depends upon the quality of the data, but that is one way to trap
for such a result, yes. Another way is to use the Nz function to convert a
Null to a "known" value that can be handled.

 

[DS]

Completely depends upon the quality of the data, but that is one way to trap
for such a result, yes. Another way is to use the Nz function to convert a
Null to a "known" value that can be handled.

OK Thanks for all of the input from everyone, at least I'm on the right
track now. Here is what I have so far...

Private Sub Command27_Click()
Dim lngActive As Long
Dim lngIN As Long
lngIN = DLookup("[EmployeeID]", "[Employees]", "[EmployeeID]=" &
Me.TextD, "[Active]= " - 1, "[SignedIN]= " - 1)
If lngActive = 0 Then
MsgBox "Invalid ID"
ElseIf lngIN = 0 Then
MsgBox "Not Signed In"
ElseIf lngIN = -1 Then
MsgBox "Orders"
End If
End Sub

What it needs to do is first look for the EmployeeID that is in the
TextD field on the form,
Next it sees if that EmployeeID is active, if not it gives the Message
"Not Active" and everything stops...
If that person Is Active then it looks to see if that person is SignedIN
If the answer is No then it gives the Message "Not Signed In" and
everything stops
If tha employee is signed in the it Proceeds, of course the MsgBoxes
will become forms.
This all comes from the Employee Table....
It's stll not working though??????
Thanks
DS
I

 

[DS]

1) Time is a reserved word - it means get the System Time.
2) Your Where clause is malformed - it is missing some quote marks
(assuming EmployeeID is a string).
3) You will get an error with DoCmd.RunSQL since your query is not a Action
Query (Delete, Insert, Update)
4) I would use the DLookup function for this
vResult = DLookup([In]","[Time]","EmployeeID=""" & Me.TextD & """")

If IsNull(vResult) then
Msgbox "Invalid EmployeeID"
ElseIF vResult = -1 then
'do something
Else
'do something else
End if

This statement is suppose to find an "Employee" in the "Time" table and if
the "IN" box is checked yes open the "Tables" form, else it opens the
"PayPad" form . I keep getting an Error on the FROM part.

Private Sub Command26_Click()
Dim mySQL As String
mySQL = " SELECT [EmployeeID],[IN] " & _
" FROM TIME " & _
" WHERE [EmployeeID]= " & Me.TextD & " """
DoCmd.RunSQL (mySQL)
If [Time].[IN] = -1 Then
DoCmd.OpenForm "Tables"
Else:
DoCmd.OpenForm "PayPad"
End If
End Sub

Thanks
DS

OK This Works, Unless Someone can see if I made a mistake somewhere
along the line, Once again Thanks Everyone.
DS

Private Sub Command22_Click()
Dim vResult As Long
vResult = Nz(DLookup("[SignedIN]", "[Employees]", "EmployeeID=" &
Me.TextD & "And Active=" & -1), 7)

If vResult = 7 Then
MsgBox "INVALID ID"
ElseIf vResult = -1 Then
MsgBox "ORDER"
Else:
MsgBox "NOT SIGNED IN"
End If
End Sub

 

[George Nicholson]

Dlookup returns a single value. You can't use it to get EmployeeID, Active
and SignedIn all at the same time (at least that's my guess as to what you
are trying to do with your illegal Dlookup syntax), and it doesn't appear
you even need to get EmployeeID, just Active and SignedIn:

lngActive = NZ(DLookup("[Active]", "Employees", "[EmployeeID]= "
Me.TextD),0)
lngIN = NZ(DLookup("[SignedIN]", "Employees", "[EmployeeID]= " Me.TextD),0)

HTH,
--
George Nicholson

Remove 'Junk' from return address.

Completely depends upon the quality of the data, but that is one way to
trap for such a result, yes. Another way is to use the Nz function to
convert a Null to a "known" value that can be handled.

OK Thanks for all of the input from everyone, at least I'm on the right
track now. Here is what I have so far...

Private Sub Command27_Click()
Dim lngActive As Long
Dim lngIN As Long
lngIN = DLookup("[EmployeeID]", "[Employees]", "[EmployeeID]=" &
Me.TextD, "[Active]= " - 1, "[SignedIN]= " - 1)
If lngActive = 0 Then
MsgBox "Invalid ID"
ElseIf lngIN = 0 Then
MsgBox "Not Signed In"
ElseIf lngIN = -1 Then
MsgBox "Orders"
End If
End Sub

What it needs to do is first look for the EmployeeID that is in the TextD
field on the form,
Next it sees if that EmployeeID is active, if not it gives the Message
"Not Active" and everything stops...
If that person Is Active then it looks to see if that person is SignedIN
If the answer is No then it gives the Message "Not Signed In" and
everything stops
If tha employee is signed in the it Proceeds, of course the MsgBoxes will
become forms.
This all comes from the Employee Table....
It's stll not working though??????
Thanks
DS
I