record retrieval

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

Chris

I have a command button that when I click it I would like
it to take each record in a table, let say the ID field,
and use the id in a query to filter records and then
export to excel.
I have the SQL query down, I only need to add the "Like"
filter. How do I do this for each record in a table?
Thanks
 
If you are creating the SQL statement in code, you need to concatenate the
value into the SQL string.

Example:
strSQL = "SELECT ..... WHERE Table1.Field1 Like """ & Me.txtMyTextbox &
""";"

If you wanted to, you could then assign this to a stored query to edit that.

Example:
CurrentDb.QueryDefs("qryMyQuery").SQL = strSQL
 
Thanks for the reply.

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 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.
 
If you want to take one value at a time and then export it, use the query to
open a Recordset. You can then step through the recordset one value at a
time.

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset (strSQL, dbOpenSnapshot)
If Not (rst.EOF And rst.BOF) Then 'check for no records
rst.MoveFirst
Do Until rst.EOF
'export to Excel here
rst.MoveNext
Loop
End If
rst.Close
Set rst = Nothing

If you do this, you should be able to grab all records that match your value
at one time. This is easier than trying to grab them one at a time. It also
means that once you have them, the rest of the work can be done in memory
instead of going back to the disk for each next record.

Also, in the Like statement, you will may need to concatenate in some quotes
as well. The final result should look like

Like "text string here"

To do that, change
((([GW Sample Info].Well) Like" & h & Filtername & h
& "));"
Click to expand...
to
((([GW Sample Info].Well) Like """ & h & Filtername & h & """));"

Although, since you say that what you have works, that is the main thing.

--
Wayne Morgan
Microsoft Access MVP


Chris said:
Thanks for the reply.

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 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.
-----Original Message-----
If you are creating the SQL statement in code, you need to concatenate the
value into the SQL string.

Example:
strSQL = "SELECT ..... WHERE Table1.Field1 Like """ & Me.txtMyTextbox &
""";"

If you wanted to, you could then assign this to a stored query to edit that.

Example:
CurrentDb.QueryDefs("qryMyQuery").SQL = strSQL

--
Wayne Morgan
Microsoft Access MVP





.
Click to expand...
Click to expand...
 
Back
Top