Form to select values for query

[Ozzie via AccessMonster.com]

Guys,

I have a table with numerous fields, some of which are;

Bal YTD, mth1, mth2, mth3, etc to mth12 (all number fields).

what I need to do is to be able to select or limit the mth fields to whatever
is selected via ...... a form?

currently all mths 1-12 are shown but say I choose mth2 (being feb) only
want to see columns for mth1 and mth2

If anybody could help it would be really appreciated

Many thanks

David

 

[Klatuu]

If you are saying you want only include the selected fields in your query,
then you will need to construct an SQL statment programmatically.

 

[Ozzie via AccessMonster.com]

hi Klatuu,

Would you happen to know who to write such a sql statement ??

many thanks for your help,

David

If you are saying you want only include the selected fields in your query,
then you will need to construct an SQL statment programmatically.

Guys,

[quoted text clipped - 13 lines]

David

 

[Klatuu]

Yes, I do.
One good way to start, is to create a query that uses every field you may
want to incldue in the query, as well as any Criteria (Where Conditions) and
Totals (Group By and Having)

Then, you can select SQL view in the query builder, and copy and paste it it
your code. This takes a little doing, because it will paste as multipe
lines, but it is faster and easier than writting it all by hand. So you
would end up with something like this:

strSQL = "SELECT MyTable.[Bal YTD], MyTable.mnth1, MyTable.mnth2, " _
& "MyTable.mnth12 FROM MyTable;"

So where ever you select what fields to include, you need to start with the
SELECT statement and build on that. For example purposes, I will show how to
include all months through the selected month:

strSQL = "SELECT "
For lngCtr = 1 to lngMonthsSelected
strSQL = strSQL & "MyTable.mnth" & cStr(lngCtr) & ","
Next lngCtr

strSQL = strSQL & "FROM MyTable;"

Now the SQL statement is ready to use.



Bal YTD, mth1, mth2, mth3, etc to mth12 (all number fields).

hi Klatuu,

Would you happen to know who to write such a sql statement ??

many thanks for your help,

David

If you are saying you want only include the selected fields in your query,
then you will need to construct an SQL statment programmatically.

Guys,

[quoted text clipped - 13 lines]

David

 

[Ozzie via AccessMonster.com]

Hi Klatuu,

Thanks for your response below, however I am still struggling to apply the
code successfully.

would you mind explaining it step by step in idiot's language! as I keep
having a blonde moment on this.

to re-cap I need to either include or exclude fields depending upon what is
selected via a form

many, many, many thanks for your help

Yes, I do.
One good way to start, is to create a query that uses every field you may
want to incldue in the query, as well as any Criteria (Where Conditions) and
Totals (Group By and Having)

Then, you can select SQL view in the query builder, and copy and paste it it
your code. This takes a little doing, because it will paste as multipe
lines, but it is faster and easier than writting it all by hand. So you
would end up with something like this:

strSQL = "SELECT MyTable.[Bal YTD], MyTable.mnth1, MyTable.mnth2, " _
& "MyTable.mnth12 FROM MyTable;"

So where ever you select what fields to include, you need to start with the
SELECT statement and build on that. For example purposes, I will show how to
include all months through the selected month:

strSQL = "SELECT "
For lngCtr = 1 to lngMonthsSelected
strSQL = strSQL & "MyTable.mnth" & cStr(lngCtr) & ","
Next lngCtr

strSQL = strSQL & "FROM MyTable;"

Now the SQL statement is ready to use.

Bal YTD, mth1, mth2, mth3, etc to mth12 (all number fields).

hi Klatuu,

[quoted text clipped - 12 lines]

 

[Klatuu]

Okay, Idiot is my native language

What you have to do here is build an SQL statement with the correct syntax.
If you want to return all fields in a table, then it is pretty simple:
"SELECT * FROM TableName;"

If you want to include only rows in the table where a field named STATE is
"TX" then it would be:
"SELECT * FROM TableName WHERE TableName.STATE = 'TX';"

So let's say you want only the fields Bal YTD (spaces in names are bad),
mnth1, and mnth2

"SELECT TableName.[Bal YTD], TableName.mnth1, TableName.mnth2 FROM TableName;"

Now, since you need to build the statement dynamically, you have to have all
the required minimal parts and the parts you will add depending on user
selection. So, you will need to start your string with "SELECT ". Then you
will need to include the field names. All the field names have to be
followed by a comma except for the last one. Then you will need the "FROM
TableName" and end the string with a ";" to make it a valid SQL statement.

So the trick is concatenating strings to come up with a complete statement.
For example purposes, I will use the concept that there is a check box for
each field the user wants to include:

strSQL = "SELECT "
If Me!txtChkBalYTD = True Then
strSQL = strSQL & "TableName.[Bal YTD],"
End If
If Me!txtChkMnth1 = True Then
strSQL = strSQL & " TableName.mnth1,"
End If
If Me!txtChkMnth2 = True Then
strSQL = strSQL & "TableName.mnth2,"
End If
....
....
If Me!txtChkMnth12 = True Then
strSQL = strSQL & "TableName.mnth12,"
End If

'Take off the last comma
strSQL = Left(strSQL, Len(strSQL) - 1)

'Add the Last Part

strSQL = strSQL & "FROM TableName;"

Now you have a complete Select Query.

Hi Klatuu,

Thanks for your response below, however I am still struggling to apply the
code successfully.

would you mind explaining it step by step in idiot's language! as I keep
having a blonde moment on this.

to re-cap I need to either include or exclude fields depending upon what is
selected via a form

many, many, many thanks for your help

Yes, I do.
One good way to start, is to create a query that uses every field you may
want to incldue in the query, as well as any Criteria (Where Conditions) and
Totals (Group By and Having)

Then, you can select SQL view in the query builder, and copy and paste it it
your code. This takes a little doing, because it will paste as multipe
lines, but it is faster and easier than writting it all by hand. So you
would end up with something like this:

strSQL = "SELECT MyTable.[Bal YTD], MyTable.mnth1, MyTable.mnth2, " _
& "MyTable.mnth12 FROM MyTable;"

So where ever you select what fields to include, you need to start with the
SELECT statement and build on that. For example purposes, I will show how to
include all months through the selected month:

strSQL = "SELECT "
For lngCtr = 1 to lngMonthsSelected
strSQL = strSQL & "MyTable.mnth" & cStr(lngCtr) & ","
Next lngCtr

strSQL = strSQL & "FROM MyTable;"

Now the SQL statement is ready to use.

Bal YTD, mth1, mth2, mth3, etc to mth12 (all number fields).

hi Klatuu,

[quoted text clipped - 12 lines]

David