PLEASE HELP: Assign column names to variables

  • Thread starter Thread starter sam
  • Start date Start date
S

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
 
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.
 
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.
 
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?
 
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...
 
You'll have to generate your SQL statement dynamically based on the values
that have been chosen.
 
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
 
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
 
What does "disn't work" mean in this context?

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

sam said:
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 said:
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!)




.
Click to expand...
Click to expand...
 
Back
Top