Query Problem and Data Report Question

[Rev. Michael L. Burns]

I have a database that I created for use at my church that uses the Data Environment and Data Reports. For the most part, everything works as desired but I do have a couple of questions.

1. On one of the forms I have a DataGrid that displays the database contents based on a data Environment command containg an SQL statement. This works fine. What I want to do is click a button that brings up a input box that allows me to enter a "LastName" and pass this parameter to a Data Environment command object that I've created and then display the results in the DataGrid. Basically, displaying only the records that contain that last name. The command object displays the correct information when I test it (the SQL) in design mode. But I can't seem to get the grid to display the correct results. Any help here would be most appriciated.

2. This question is somewhat related to the first. I have a number of reports that work fine but I'd like to be able to generate these reports between two dates. For instance, All "Email - Incoming" between March 1, 2000 and March 31, 2000. I know how to generate the reports based on a single criteria but how do I do it for two as mentioned above. I'd like to click a button, bring up a from containg to drop down calendar controls that allow me to enter beginning date and ending date and have the report generated based on these two dates. Any help here would be most welcomed also.

Thanks,
Rev. Michael L. Burns

[raicheman]

1. To diusplay the conntents of the lastname = 'Smith' you must set the datagrid to the new database after the call is made. Example:
Data.recordsource = "SELECT * FROM YourDATABASE WHERE LNAME = 'Smith'

dbgrid.DataSource = Data1

2.Let SQL in Data Recordsource read between. Example:
data1.recordsource = SELECT * FROM YourDataBASE Where fieldDate > 01/01/2000 and fieldDate < 03/31/2000

Hope it helped

[JohnAtWork]

1) Try this code


Dim i as String
Dim SQL as String

i = InputBox("Please Enter Last Name to Search For")

SQL = "SELECT TABLE.FIELD1, TABLE.LAST_NAME, " _
& "TABLE.FIELD3, TABLE.FIELD4 FROM TABLE" _
" WHERE (((TABLE.LAST_NAME)='" & i & "*'));"

That's Access SQL.

The date range criteria you're looking for is the Between function in SQL. Insert into the Where Clause:

Where TABLE.DATE Between #01/01/00# and #03/01/00#

If you're investigating doing it with the Calendar Control, set up the Variables as Dates. . .

[code]
Dim Begin as Date
Dim Ending as Date
Dim SQL as String

'Code to grab dates and bind to variables goes here

SQL = "SELECT TABLE.MATERIAL_NUMBER, TABLE.DATE, " _
& "TABLE.REQUIRED_QTY, TABLE.INDEP_REQ_QTY FROM TABLE " _
& "WHERE (((TABLE.DATE) Between " & Begin & " and " & Ending & "));"

[Rev. Michael L. Burns]

Thanks you all for your help. I give these tips a try later this week when I find time to get back to the project.