Query Field Selector

[Guirg]

Hey All,

Im trying to change the field selection in a query that im exporting by breaking up the SQL statement but am having some difficulty:

    qryTest.SQL = "SELECT" & varselect & "FROM qrySummary " & BuildFilter

And is there a way to carry a string over from one form to another? The Buildup for the varselect is in another form....

Cheers Tim

 

[DCrake]

If you have the varselect defined publically in a module then yes you can refer to it anytime. also you need a space After the word SELECT and before the word FROM in your string

David

 

[SOS]

Also, you can't just refer to the query's SQL as qryTest.SQL. You would need to use a QueryDef Object for this (unless you are already doing so but haven't shared the rest of your code).

 

[Guirg]

David: Hey i threw it into a module and then defined the sub as public but im still getting enter paremeter Value... my code for the definition is

qryTest.SQL = "SELECT " & "varselect" & "FROM qry..."[\CODE] the rest is the same as above

 

[Galaxiom]

Wasn't varselect a Variable rather than a string?
You will also need a space before FROM.

 

[Guirg]

ok changed to a variant, but its still popping up the input paremeter

 

[namliam]

qryTest.SQL = "SELECT " & varselect & " FROM qrySummary " & BuildFilter

NOTE the spaces after the Select and before the FROM

To prevent any more confusion, NOTE THE SPACES!!! and please post your full code concerning this!

 

[Guirg]

 Private Sub Command253_Click()
    Dim dbsCurrent As Database
    Dim qryTest As QueryDef
    Dim OldSQL As String
    Set dbsCurrent = CurrentDb
    Set qryTest = dbsCurrent.QueryDefs("Query1")
    OldSQL = qryTest.SQL
    qryTest.SQL = "SELECT " & varselect & " FROM qryCampaignSummary " & BuildFilter
    DoEvents
    DoCmd.OutputTo acOutputQuery, "Query1", acFormatXLS, , True
End Sub[\CODE]
 
causes and error 3141 with quoteation marks around the varselect causes an input parameter box

 

[Guirg]

oh and varselect is defined as "Public varselect As Variant" in a module and the sub is defined as a public sub....

 

[DCrake]

What is contained within varselect?

Do a debug.print varselect to see what is being stored

 

[namliam]

Use a forward slash in your /Code to end the coding...

Dim dbsCurrent As Database
Dim qryTest As QueryDef

Disambiguate.. this could be ADO or DAO...
Dim dbsCurrent As DAO.Database
Dim qryTest As DAO.QueryDef

Make sure you have referenced the DAO as well in the menu Tools > References


Your variables varselect and BuildFilter are
1) not beeing defined anywhere...
While in a module or in VBA window tools > Options and tick the "require Variable Declaration" tick box...
This will make sure you get "Option Explicit" in each module which will make you declare varaibles.

2) Not beeing filled
The variables are empty??

Unless they are filled elsewhere???
Select from qryCampaignSummary
Is not valid syntax

p.s. Please dont leave default names i.e. Command253, it will eventually cause a maintenance nightmare!

 

[Guirg]

damn its returning varselect as empty... i was building it up

 Public Sub Var()
    Public varselect As Variant
    If optA = -1 Then
            varselect = varselect & "qrySummary.[A],"
    End If

 

[namliam]

If you want the public variable... you have to declare it outside any function/sub

 Public varselect As Variant
 Public Sub Var()
    If optA = -1 Then
            varselect = varselect & "qrySummary.[A],"
    End If

 

[DCrake]

If your query one contains one table then you do not need to explicitly define the table.

[B]Select A, B, C From Table1[/B] is just the same as [B]Select Table1.A, Table1.B, Table1.C From Table1[/B]

Plus the only time you need square brackets is if there are spaces in the field names.

David

 

[Guirg]

Ok declaring it outside of the funtion worked, but its returing all of the fields instead of the selected... hmmm

 

[namliam]

Thats probably because you are not resetting it
varselect = ""

 

[Guirg]

Thanks for everything, its all a little stupid, i had to declare varselect in a module but leave the code in the form.... buuuuuut it all works! Thanks again, i think im finally done with this thing !!!!!!

 

[namliam]

Modules are the only thing that is TRULY public, inside the form most everything is "private" to the form.

 

[SOS]

Modules are the only thing that is TRULY public, inside the form most everything is "private" to the form.

Unless of course you change it to PUBLIC and then you can call just about anything on a form or report module (although it isn't necessarily a good thing to do, but it can be done).

 

[Galaxiom]

Unless of course you change it to PUBLIC and then you can call just about anything on a form or report module (although it isn't necessarily a good thing to do, but it can be done).

Could someone please elaborate this. I always thought Subs had to be in a module to be globally available.

I tried setting a Sub on what I assume is meant by a "form module" (Microsoft Office Access Class Object) to Public but it was still not found by other modules. (Access 2007)