Code some queries?

[marleyuk]

hello, i have compiled some code that enables the user to click a command button, which opens a dialogue box allowing the user to select a .csv file which is then automatically imported into a table. While its in this table i want to run queries on it. Does anyone know the code for manually creating queries? The first query i want is to display any numbers in field3 under 0.001 in the next available field. If anyone knows what im talking about id really appriciate the help, thanks,
Marley.

 

[Bat17]

how about creating the queries manualy and using code to run them?

Peter

 

[marleyuk]

because the data i want to query is going to change so i dont know how to query it.

 

[marleyuk]

because im unsure how to make a query for data that is going to change.. each time the file is imported its going to be different, so how to i query if the cells are changing?

 

[marleyuk]

because im unsure how to make a query for data that is going to change.. each time the file is imported its going to be different, so how to i query if the cells are changing?

 

[Bat17]

Is it the structure that is changing or the data value?

not sure what you want her
The first query i want is to display any numbers in field3 under 0.001 in the next available field
to filter for records under 0.001 is OK but what do you mean by next available field?

Peter

 

[marleyuk]

well the amount of columns and rows will change, depending on how many entries are made in the file im importing. Also the field names are being imported so field 3 could have a random name like "20387b1" and field 4 "25367b1". So im pretty clueless how to approach this.

I'm sure this is doable but Im out of my depth so im looking for a bit of advise in how to approach.

Thanks.

 

[Bat17]

OK, this code will create a query filtering on Field 3 and showing just field 4. It may not be what you want but it should show you enough of the structure to let you play around. Remember that fields are 0 based so may actualy want fields 2/3 not 3/4 as I have done it.
You may need to set a reference to DAO under Toos>References in any module

Sub getRecords()
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strField3 As String
Dim strField4 As String
Dim strTable As String
Dim strQuery As String
Dim strSql As String


strTable = "EliteTests" ' name of new table
strQuery = "qryTemp"    ' name of query to create

Set rst = CurrentDb.OpenRecordset(strTable)
strField3 = rst(3).Name
strField4 = rst(4).Name
strSql = "SELECT " & strField4 & " FROM " & strTable & " WHERE " & strField3 & ">2"

Set qdf = CurrentDb.CreateQueryDef(strQuery, strSql)

Set qdf = Nothing
Set rst = Nothing
End Sub

HTH

Peter

Edited to add variable for table

 

[marleyuk]

Thanks mate, but im not sure if thats gonna work because it needs to stretch to cover the amount of fields being imported.. so if 6 fields are imported it needs to cover 6 or if 9 are imported it needs to cover 9

 

[Bat17]

so you want to show all fields but use one as a filter?

Peter

 

[marleyuk]

i just want to run these queries but they need to be run over the entire db

 

[Bat17]

Can you build a query by hand and post the SQL, I know the names will vary but it will help me see what you are after. I'm afraid I just don't know what sort of query you want to build.


What will you be doing with the data? viewing it as a query? using it a source for a report?

Peter