Table

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

Here is my SQL code, I have tested this and know it works

SQL = "SELECT DISTINCT [GW Sample Info].Well, GWData.
[Sample Name], GWData.Analyte, Analyte.[CAS Number],
Analyte.Type, GWData.Units, [GWData].Concentration" & a &
h & " " & h & a & "[Qualifier] AS Combine, [GWData]!
[Sample Name]" & a & h & "( " & h & a & "[GWData]![Sample
Date] " & a & h & ")" & h & "AS [Name Combine],
GWData.Concentration, GWData.Qualifier, GWData.[Sample
Date] FROM (GWData INNER JOIN [GW Sample Info] ON GWData.
[Sample Name] = [GW Sample Info].[Sample Name]) INNER
JOIN Analyte ON GWData.Analyte = Analyte.Analyte WHERE
((([GW Sample Info].Well) Like" & h & Filtername & h
& "));"

I just need the "filtername" in the like porition of the
SQL statement (at the end) to be populated with a value
for the "MW Info Table", and the "Well" field. I would
like it to take the first value, run the query and export
to excel, then take the next value and repeat. I am just
not sure how to do this part. Thanks.
 
Hi,
You would need a loop. Something like this:

Dim rs as DAO.Recordset
Dim strSql as String
Dim SQL As String

strSql = "Select Well From [MW Info] Order By Well"

Set rs = CurrentDb.OpenRecordset(strSql)

Do While Not rs.EOF
SQL = "SELECT DISTINCT [GW Sample Info].Well, GWData.
[Sample Name], GWData.Analyte, Analyte.[CAS Number],
Analyte.Type, GWData.Units, [GWData].Concentration" & a &
h & " " & h & a & "[Qualifier] AS Combine, [GWData]!
[Sample Name]" & a & h & "( " & h & a & "[GWData]![Sample
Date] " & a & h & ")" & h & "AS [Name Combine],
GWData.Concentration, GWData.Qualifier, GWData.[Sample
Date] FROM (GWData INNER JOIN [GW Sample Info] ON GWData.
[Sample Name] = [GW Sample Info].[Sample Name]) INNER
JOIN Analyte ON GWData.Analyte = Analyte.Analyte WHERE
((([GW Sample Info].Well) Like" & h & rs!Well & h
& "));"

'do your stuff here
'get the next 'well' value
rs.moveNext
Loop
 
Back
Top