Refer a field using variable name in select statement.

aman

Registered User.
Local time
Today, 15:45
Joined
Oct 16, 2008
Messages
1,251
Hi guys

I have table with various fields named as ename,dname,Mon,Tue,Wed,Thu,Fri,Sat,Sun.

I want to pop up those enames in the combobox whose corresponding day value is not equal to 0. I am writing the following code but I have a doubt in using variable name(b) to specify a field name in the table. Please have a look at the code and see how i am using variable b to specify any day which is a field in the table.
Code:
Private Sub Combo8_LostFocus()
Text12.Value = ""
Dim strText, strFind As String
Dim a As String
Dim b As String
a = Label14.Caption
If a = "Monday" Then
b = "Mon"
ElseIf a = "Tuesday" Then
b = "Tue"
ElseIf a = "Wednesday" Then
b = "Wed"
ElseIf a = "Thursday" Then
b = "Thu"
ElseIf a = "Friday" Then
b = "Fri"
End If
strFind = "dname='" & Combo8.Value & "'"
       
       ' If Len(Trim(strText)) > 0 Then
           
            strFind = strFind & " and [COLOR=red]" & b & " <> 0"
[/COLOR]         
            strSql = "SELECT ename FROM tbstaff1 Where " & _
                      strFind & " ORDER BY ename;"
          
            Me.Combo10.RowSource = strSql
End Sub

Thanks
Aman
 
Usually a need to dynamically change the fields in a query indicates the data should be structured differently. Instead of a field for each day, your table should have multiple records with one field indicating datatype (in your case day) the record applies to rather than changing to another field in the query.

Then the query uses a clause like WHERE tablename.datatype = "whatever"

This is much simpler and more versatile than having to build the query because you can substute and expression in place of the "whatever".
 
Hi galaxiom

Actually the timesheet system that i am developing has two main forms. One is for admin staff and the other one for team leader . In the admin staff form,the people enter the info like name,dept,employee no,salary by hr,contracted hrs for each day.so there are different textbox for different days. if suppose Mon=7,Tue=5,Wed=6,Thu=7,Fri=3 then thats why i used 7 fields from Mon to Sun in the table. and everything is working fine except if suppose i have a variable b=Mon and I want to use variable b in the select statement to check for field value e.g if b<>0 then....

I hope you understand my problem.

Thanks
Aman
 
I agree with Galaxiom - there's a normalization problem here - multiple instances of the same kind of data should not be separate columns in a table, but should instead be separate rows (usually in a child table, with each record relating back to a key field in the main table).
 
Thanks guys for your help. My problem is sorted out.

Aman
 

Users who are viewing this thread

Back
Top Bottom