PLEASE HELP: Assign column names to variables

[sam]

Hi All,

I have designed a form that searches from the current database based on the
criteria that user selects(criterias are column names in the database table )
and clicks "search" button. Right now I have all column names (criterias in
search form) hardcoded in the Select statement. But, I want the column names
to be dynamic. What I mean is, I want to display columns that the users
select in the form and not hardcode it.
How can I assign column names to a variable?

Thanks in advance

 

[Douglas J. Steele]

Create a combo box (or list box), set its RowSourceType property to "Field
List" and its RowSource property to the name of the table (or query) whose
field names you want.

 

[sam]

Hey Douglas,

I want it the other way around, I have specific values for the drop down
list (which are column names in the result table), and I want those columns
to be displayed in the result table based on what user selects from the
dropdown list.

 

[Douglas J. Steele]

Sorry, I don't understand what you're asking for.

You said you wanted "the column names to be dynamic". Isn't that what I gave
you: the ability to automatically generate the names of the columns for a
given table?

 

[sam]

Hey Douglas,

Yes, I want the columns of the result table to be dynamic.

For eg:

There are three dropdown lists named, Day1, Day2 and Day3.
The dropdown lists (search criterias) values are:
Monday
Tuesday
Thursday
Friday
Saturday
Sunday

This lists are fixed.

so now, if I select Monday from "Day1" list and click "Search" then i want
only Day1 column displayed in the searh result table and not Day2 and Day3
columns

My SQL statement is:

SELECT Subject, Day1, Day2, Day3, StudentId
FROM Weekly_Schedule
WHERE Subject = Me.subject.value
AND
Day = Me.day.value

so once I input student Id and select a value from Day1 dropdown list, I
only want those two columns to be displayed which are "StudentID" and "Day1"
column.

If I choose days from Day1 and Day2 dropdown then I want "Day1" and "Day2"
both columns displayed in the search result table and so on...

 

[Douglas J. Steele]

You'll have to generate your SQL statement dynamically based on the values
that have been chosen.

 

[sam]

oh ok, how can I do that? can you help me with that?

 

[Douglas J. Steele]

If I'm understanding you correctly, it would be something like

Dim strSQL As String

strSQL = "SELECT Subject, "
If IsNull(Me!Day1) = False Then
strSQL = strSQL & "Day1, "
End If
If IsNull(Me!Day2) = False Then
strSQL = strSQL & "Day2, "
End If
If IsNull(Me!Day3) = False Then
strSQL = strSQL & "Day3, "
End If
strSQL = strSQL & "StudentId" & _
"FROM Weekly_Schedule " & _
"WHERE Subject = " & Me.subject.value
If IsNull(Me!Day1) = False Then
strSQL = strSQL & "AND Day1 = '" Me!Day1 & "' "
End If
If IsNull(Me!Day2) = False Then
strSQL = strSQL & "AND Day2 = '" Me!Day2 & "' "
End If
If IsNull(Me!Day3) = False Then
strSQL = strSQL & "AND Day3 = '" Me!Day3 & "' "
End If

 

[sam]

Hey Douglas,

Thanks a lot for your help.

This worked fine, But If I do it this way I will end up writing code for
each column seperately and I have like 15-20 columns in a table and that
might increase too. So , Is there a way to assign a variable in more dynamic
way, where we dont hardcode the column names?

I tried the following but it didnt work.

strSQL = "SELECT Subject, "
If IsNull(Me!Day1) = False Then
strSQL = strSQL & "[" & me.day1.value & "]"
End If
If IsNull(Me!Day2) = False Then
strSQL = strSQL & ",[" & me.day2.value & "]"
End If
If IsNull(Me!Day3) = False Then
strSQL = strSQL & & ",[" & me.day3.value & "],"
End If

Thanks again

 

[Douglas J. Steele]

What does "disn't work" mean in this context?

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

Hey Douglas,

Thanks a lot for your help.

This worked fine, But If I do it this way I will end up writing code for
each column seperately and I have like 15-20 columns in a table and that
might increase too. So , Is there a way to assign a variable in more
dynamic
way, where we dont hardcode the column names?

I tried the following but it didnt work.

strSQL = "SELECT Subject, "
If IsNull(Me!Day1) = False Then
strSQL = strSQL & "[" & me.day1.value & "]"
End If
If IsNull(Me!Day2) = False Then
strSQL = strSQL & ",[" & me.day2.value & "]"
End If
If IsNull(Me!Day3) = False Then
strSQL = strSQL & & ",[" & me.day3.value & "],"
End If

Thanks again

If I'm understanding you correctly, it would be something like

Dim strSQL As String

strSQL = "SELECT Subject, "
If IsNull(Me!Day1) = False Then
strSQL = strSQL & "Day1, "
End If
If IsNull(Me!Day2) = False Then
strSQL = strSQL & "Day2, "
End If
If IsNull(Me!Day3) = False Then
strSQL = strSQL & "Day3, "
End If
strSQL = strSQL & "StudentId" & _
"FROM Weekly_Schedule " & _
"WHERE Subject = " & Me.subject.value
If IsNull(Me!Day1) = False Then
strSQL = strSQL & "AND Day1 = '" Me!Day1 & "' "
End If
If IsNull(Me!Day2) = False Then
strSQL = strSQL & "AND Day2 = '" Me!Day2 & "' "
End If
If IsNull(Me!Day3) = False Then
strSQL = strSQL & "AND Day3 = '" Me!Day3 & "' "
End If

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)




.