VBA - Building a SQL statement with a table field variable proper formating

[keusch]

The task at hand is to compare the data in two tables and write the mismatches to a table. There are hundreds of fields and millions of records.
The process is currently opening the the "master table" and capture each field names (SelectedField) when plug the SelectedField name into the SQL statement (for both tables).
It may be a formating issue as the sql (including the if statements and the insert work find) but I have tried so many differnet scenerios.
Here is the variable to select the field name:
SelectedField = rec.Fields(i).Name
How to insert the item above ( SelectedField ) into the SQL is the issue - example substitute dbo_Table.field_item with SelectField ((dbo_Table.field_item) Is Not Null))
I have tried using the dim variable as a string and as a field, I have tried using the rec value itself
'" & rec.Fields(i).Name & "'
I've trid putting the square brackeds and parenthesis.
I'd appreciate any help.

 

[PHV]

I'd appreciate any help
To WHICH code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[keusch]

Within the VB code I have built a SQL statement. I need to understand how to use a table field variable inside the VB code for the SQL statement
Any where there the field name "carrier_description" appears below, I want to substitute the varible field name.

Dim db As Database
Dim rec As Recordset
Dim i As Integer
Dim FieldName As String

Dim strSQL As String

Set db = CurrentDb()
Set rec = db.OpenRecordset("dbo_CD_PERF_INTERVAL")

i = 0

While Not rec.EOF

TblDV = "dbo_CD_PERF_INTERVAL!"

FieldName = rec.Fields(i).Name

strSQL = " INSERT INTO x_qc_tables ( well_id, wellbore_id, perf_id, perf_interval_id, DV, OW, ITEM ) " & _
" SELECT dbo_CD_PERF_INTERVAL.well_id, dbo_CD_PERF_INTERVAL.wellbore_id, dbo_CD_PERF_INTERVAL.perf_id, dbo_CD_PERF_INTERVAL.perf_interval_id, dbo_Dim_Perforation_Interval.carrier_description, dbo_CD_PERF_INTERVAL.carrier_description,'" & FieldName & "' As ITEM " & _
" FROM dbo_CD_PERF_INTERVAL INNER JOIN dbo_Dim_Perforation_Interval ON (dbo_CD_PERF_INTERVAL.well_id = dbo_Dim_Perforation_Interval.ow_well_id) AND (dbo_CD_PERF_INTERVAL.perf_id = dbo_Dim_Perforation_Interval.perf_id) AND (dbo_CD_PERF_INTERVAL.perf_interval_id = dbo_Dim_Perforation_Interval.perf_interval_id) " & _
" WHERE ((([dbo_CD_PERF_INTERVAL].perf_interval_id ) = '3Sikm') And ((dbo_Dim_Perforation_Interval.carrier_description) Is Not Null)) Or (((dbo_CD_PERF_INTERVAL.perf_interval_id) = '3Sikm') And ((dbo_CD_PERF_INTERVAL.carrier_description) Is Not Null)) " & _
" ORDER BY dbo_CD_PERF_INTERVAL.well_id, dbo_CD_PERF_INTERVAL.perf_id, dbo_CD_PERF_INTERVAL.perf_interval_id;"

DoCmd.RunSQL strSQL


i = i + 1


Wend

 

[Andrzejek]

Number of records does not matter, but "hundreds of fields" in both tables?

Have fun.

---- Andy

 

[keusch]

LOL - that was helpful!