I am having problem using SQL in procedures when it involves string.  I 
copied the  SQL  statement below and would like to use it as s string in a 
procedure but it is not working.


SELECT tblFollowup.CustFollowupID, tblFollowup.Followup, 
tblFollowup.FollowupDate
FROM tblFollowup
WHERE (((tblFollowup.Followup) = -1) And ((tblFollowup.FollowupDate) = 
Date))
WITH OWNERACCESS OPTION;

tblFollowup.Followup is a checkbox, the others are text boxes

How do I format this to use in a procedure ?.  Please comment on the 
general way of doing this if you can.

Thanks

Idelle,

' **********************************************************
Dim dbs As Database
Dim rst As Recordset
Dim strSQL As String

Set dbs = CurrentDb

strSQL = "SELECT CustFollowupID, Followup, tblFollowup " & _
         "FROM tblFollowup " & _
         "WHERE Followup = -1 And " & _
         "FollowupDate = #" & Date & "# " & _
         "WITH OWNERACCESS OPTION;"
Set rst = dbs.OpenRecordSet(strSQL)

If rst.EOF And rst.BOF Then
   MsgBox("There are no records")
   Exit Sub
Else
   MsgBox("CustomerID = " & rst!CustFollowupID)
   Exit Sub
End If
' **********************************************************

If FollowupDate was a string you would change the # to 
single quotes.

Numbers have no delimiters,
Strings have single quotes,
Dates have #.

hth,
Wayne

You would also remove tblFollowup on your first line of your strSQL statement.
Good Luck,
Derrick Flores

-----Original Message-----
From:	Wayne Ryan [SMTP:wayne.ryan@t...]
Sent:	Thursday, December 26, 2002 12:19 PM
To:	Access
Subject:	[access] Re: SQL string in procedure

Idelle,

' **********************************************************
Dim dbs As Database
Dim rst As Recordset
Dim strSQL As String

Set dbs = CurrentDb

strSQL = "SELECT CustFollowupID, Followup, tblFollowup " & _
         "FROM tblFollowup " & _
         "WHERE Followup = -1 And " & _
         "FollowupDate = #" & Date & "# " & _
         "WITH OWNERACCESS OPTION;"
Set rst = dbs.OpenRecordSet(strSQL)

If rst.EOF And rst.BOF Then
   MsgBox("There are no records")
   Exit Sub
Else
   MsgBox("CustomerID = " & rst!CustFollowupID)
   Exit Sub
End If
' **********************************************************

If FollowupDate was a string you would change the # to 
single quotes.

Numbers have no delimiters,
Strings have single quotes,
Dates have #.

hth,
Wayne

oops,

just one of those cut and paste things ...

strSQL = "SELECT CustFollowupID, Followup, FollowupDate " & _
         "FROM tblFollowup " & _
         "WHERE Followup = -1 And " & _
         "FollowupDate = #" & Date & "# " & _
         "WITH OWNERACCESS OPTION;"

Wayne