VBA Help!

[kelnozz]

I am currently writing a module to automate running a series of queries to process data outside of our FoxPro 2.6 databases. I can currently do this, but it involves running 10-15 make table queries and I wanted to get this done as fast as possible to save me time processing these sales.

I have 2 global variables for the data range to pull the data for and a identifier for the name of the table to be made. I am currently having a problem the SQL statement in VBA. It gives me an error when I have an AND statement in my WHERE clause of the SELECT INTO query. Pasted below is one of the subs that I am working on. Also this is in Access 97 because of linking to FoxPro 2.6 Databases.

Sub Chesterfield()

Dim rst As Recordset
Dim rcount As Long
Dim source As String
source = "NWNODEMB"
table_name = "Chesterfield_" + table_date
Set dbs = OpenDatabase("\\laurel\data2\home\working.mdb")
dbs.Execute ("SELECT [Hartford].FSLAST AS LAST_NAME, [Hartford].FSFIRST AS FIRST_NAME, [Hartford].FSSAL AS SALUTATION, " _
& "[Hartford].FSADDR1 AS ADDR1, [Hartford].FSADDR2 AS ADDR2, [Hartford].FSCITY AS CITY, [Hartford].FSSTATE AS STATE, [Hartford].FSZIP AS ZIP, " _
& "[Hartford].FSPHONE AS PHONE, [Hartford].FSSET AS AGENT_ID, [Hartford].FDSET AS CALL_DATE, [Hartford].FHSET AS CALL_TIME, " _
& "[Hartford].F02 AS COMP_TYPE, [Hartford].F04 AS USE_INTERNET, [Hartford].F03 AS G3_OR_BETTER, [Hartford].F05 AS WIN95_98, " _
& "[Hartford].F22 AS CD_ROM, [Hartford].F21 AS LAPTOP, [Hartford].F20 AS CABLE_OUTLET, [Hartford].F19 AS WHY_NOT_INT, " _
& "[Hartford].F17 AS LOGIN, [Hartford].F18 AS LOGIN2, [Hartford].F25 AS HAS_USB_PORT, [Hartford].F26 AS COMPUTER_LESS_THAN_1YR, " _
& "[Hartford].F32 AS TYPE_OF_APPT, [Hartford].FDAPPT AS INSTALL_DATE, [Hartford].FHAPPT AS INSTALL_TIME, [Hartford].F31 AS OFFER, " _
& "[Hartford].F13 AS PLAN " _
& "INTO " & table_name & " FROM [Hartford] WHERE [Hartford].FDSET= #" & sale_date & "# And [Hartford].FSSOURCE = " & source & " ORDER BY [Hartford].F32;")
Set rst = dbs.OpenRecordset("SELECT * FROM " & table_name)
If rst.RecordCount = 0 Then
rst.Close
dbs.Execute ("Drop Table " & table_name)
End If
dbs.Close

End Sub


Thanks for any help you can offer!

-Kelnozz

 

[Jon K]

Being a text string, source needs to be enclosed in single quotes.

Change the SQL statement to:-

... And [Hartford].FSSOURCE = '" & source & "' ORDER BY ...