select @var from Not working

[mikela]

Hello all!

How can I create a query that the select depends on a variable that the user introduces?

would it be something like:

Select [@var] from table ????

it is not working. Example:

@var would be number from 1 to 31 (according to a day on the month that the user wants to view), then the result for it, is the number that the user introduced, instead of the vaule kept in that number...

Hope i make myself clear....

Thanks in advance!

 

[MStef]

In query criteria put.
[Forms]![FormName]![FieldName]

 

[mikela]

ok!

Thanks for the information, but still is not appearing what I need. the column I need to select is a number. (The name of the column is 26, 25, 24...), then, if I trie [Forms!FormName!Fields] it throws an error, and won't work.

If i don't put the backets, it won't show the correct data, it shows the data that I introduce...

 

[Jurjen]

make a dynamic query with CreateQueryDef

Hello,

You cannot parameterize field names in a static query. You could, however, make a dynamic query, either temporary or permanent.
First of all you will need a reference to Microsoft DAO 3.6 Object Library (in VB menu Tools: References). Make sure that you put the priority as high as possible.

In the following example i made a form with:
- a listbox (lbx_nums) with a Value List 1;2;3 as RowSource
- a command button (but_query)

Furthermore, there is a table TEST with 4 fields:
test_id (long) --> primary key
1 (text)
2 (text)
3 (text)

The form has the following VB code:

Option Compare Database
Option Explicit

Dim db As Database
Dim qdf As QueryDef

Private Sub but_query_Click()
    If Nz(lbx_nums) = "" Then
        MsgBox "You have not selected a number"
        Exit Sub
    End If
    Set qdf = db.CreateQueryDef("qry_test", "SELECT " & lbx_nums & " FROM TEST")
End Sub

Private Sub Form_Load()
    Set db = CurrentDb
End Sub

Private Sub Form_Unload(Cancel As Integer)
    Set db = Nothing
End Sub

Clicking the button will create a permanent query (qry_test) where the SELECT depends on the selected number in the listbox. You can also make a temporary query by leaving the first argument to CreateQueryDef empty. Just look it up in the VB help documentation.

 

[mikela]

Hi!

Thanks again! I tried to create that query and it seems ok, but it throws an error saying: join expression not supported...

Any idea why the error?

my code is the following:

Dim db As database
Dim qrf As QueryDef
Dim sql As String

sql = "SELECT [c." & Combo6.Value & "], e.nombre "
sql = sql & "FROM empleados AS e INNER JOIN controlarFichaje AS c ON e.EmpleadoID = c.EmpleadoID"
sql = sql & "WHERE (((e.Fichaje)=True));"

Set db = CurrentDb
Set qrf = db.CreateQueryDef("test2", sql)
db.Close
qrf.Close



Thanks in advance!

 

[Jurjen]

test query expression

... add a space after c.EmpleadoID, since now it will be concatenated directly to WHERE

 

[neileg]

This is made more difficult because your table design is wrong. You should not have 31 columns, you should have up to 31 records in a related table. You need to read up on normalisation. Wikipedia has some good stuff on this, I believe.

 

[mikela]

Hey,

I know the design may be wrong, but it is too late to change it... Anyway as a row i'll have the information for every day, and it is variable...


Ok, so now it creates a query everytime, so the second time i execute it, a message is displayed:

The query test2 already exists... i think i should delete it first, but no way to figure it out how to delete it...

Then, how do i create a report that takes that query to display the data??
My guessing:

To create the query->create a report that takes that query,

then everytime delete the query and create it again with the new values... am i too wrong?????

Thanks!

 

[Rabbie]

Hey,

I know the design may be wrong, but it is too late to change it... Anyway as a row i'll have the information for every day, and it is variable...


Ok, so now it creates a query everytime, so the second time i execute it, a message is displayed:

The query test2 already exists... i think i should delete it first, but no way to figure it out how to delete it...

Then, how do i create a report that takes that query to display the data??
My guessing:

To create the query->create a report that takes that query,

then everytime delete the query and create it again with the new values... am i too wrong?????

Thanks!

Never too late to change something that is wrong! If your design is wrong you will just have problem after problem.

 

[neileg]

I know the design may be wrong, but it is too late to change it... Anyway as a row i'll have the information for every day, and it is variable...

Unless this is simply course work, you will live to regret not getting the design right. I have been there!

If your design was right, you could use a simple parameter query and base the report on that. When you run the report, you supply the parameter, and the report uses the current data for that parameter value. As it is now, you will have to delete the query, run your module and then open the report.

 

[mikela]

bbbffff... I know!!! But i have to meet the death line! When i meet it, i may do some improvements... But first is first! I'm still learning, and not having enought time... stressed out!

Pleeeeease!! Anyone knows how to delete a query dinamycally?? and create a new query, and open a form with that new query?

 

[Rabbie]

try docmd.deleteobject acQuery,"QueryName" to delete the query.

 

[neileg]

Run your module once to create the querydef. Design the report based on that query. Anytime you open the report it will look for the querydef, run it and then produce the report.

DeleteQueryDef will delete your existing query so you could add that into the module.

 

[Jurjen]

DeleteQueryDef does not exist ... this is the solution

DeleteQueryDef does not exist ... this is the solution:

db.QueryDefs.Delete (<query name>)

 

[neileg]

DeleteQueryDef does not exist ...

oops

I thought I'd used this in the past...