Not getting data using WHERE clause between table and recordset 
Author Message
 Not getting data using WHERE clause between table and recordset

I am new to Access VBA.  I am writing a module that is supposed to search an
export table containing projects, workdates, hours, employee names and
export all records pertaining to each particular project to separate
spreadsheets. (Using Access 97)

I have a recordset based on a table that just has a project field.  I am
trying to use that to set the WHERE clause correctly.

The macro operates by establishing the export tables and transferring the
spreadsheets; however, no data is transferred.  I think that I'm not calling
the fields correctly in the WHERE clause.

rst is recordset
Project is Project field in Export2Table

DoCmd.RunSQL "SELECT * INTO [projExport] FROM Export2Table WHERE Project = "
& rst!Project

Thanks,

Carole



Mon, 23 Dec 2002 03:00:00 GMT  
 Not getting data using WHERE clause between table and recordset

If Project is a text field, you have to put quotes around your recordset
string.

Example: ...WHERE Project = "'" & rst!Project & "'"

Calvin Smith
http://www.CalvinSmithSoftware.com
"Real-world Source Code Solutions"


Quote:
> I am new to Access VBA.  I am writing a module that is supposed to search
an
> export table containing projects, workdates, hours, employee names and
> export all records pertaining to each particular project to separate
> spreadsheets. (Using Access 97)

> I have a recordset based on a table that just has a project field.  I am
> trying to use that to set the WHERE clause correctly.

> The macro operates by establishing the export tables and transferring the
> spreadsheets; however, no data is transferred.  I think that I'm not
calling
> the fields correctly in the WHERE clause.

> rst is recordset
> Project is Project field in Export2Table

> DoCmd.RunSQL "SELECT * INTO [projExport] FROM Export2Table WHERE Project =
"
> & rst!Project

> Thanks,

> Carole



Mon, 23 Dec 2002 03:00:00 GMT  
 Not getting data using WHERE clause between table and recordset
Thanks,

I also had to add in a single quote because it is inside a set of "".

Carole

Quote:

>If Project is a text field, you have to put quotes around your recordset
>string.

>Example: ...WHERE Project = "'" & rst!Project & "'"

>Calvin Smith
>http://www.CalvinSmithSoftware.com
>"Real-world Source Code Solutions"



Tue, 24 Dec 2002 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Getting data from #TEMP table using ADO

2. Querry cannot be updated because the From clause is not a single simple table name Error

3. Help - Simply Getting Data from Extrenal Data Files (not from Access Data Base)

4. Using QueryDef or Recordset in FROM clause of SQL

5. using recordset(subquery) as target of FROM clause

6. LIKE clause does not return data as supposed

7. Trying to get data from MS Access table (not using ASP)

8. slq guru help needed: how to select data not in another table using sql

9. Copy/Saving a recordset from one data table to another table

10. Restrict data using SQL Where clause

11. Dynamic ADO Recordset Not Dynamic (Does Not Refresh Data)

12. Dynamic ADO Recordset Not Dynamic (Does Not Refresh Data)

 

 
Powered by phpBB® Forum Software