Jass Saini
asked on
If I hard code my query...why do I need to built in my DB??
I have my query hard coded..
Public Sub querydef()
Dim db As Database, qdf As querydef, strSQL As String
Set db = CurrentDb
Set qdf = db.QueryDefs("Allot_Q")
Analyst = ""
For Each Item In Forms!AllotSearch_frm!lstE mployeeID. ItemsSelec ted
If Analyst = "" Then
Analyst = "'" & Forms!AllotSearch_frm!lstE mployeeID. ItemData(I tem) & "'"
Else:
Analyst = Analyst & " , '" & Forms!AllotSearch_frm!lstE mployeeID. ItemData(I tem) & "'"
End If
Next
Analyst = "IN(" & Analyst & ")"
Org = ""
For Each Item In Forms!AllotSearch_frm!lstO rg.ItemsSe lected
If Org = "" Then
Org = "'" & Forms!AllotSearch_frm!lstO rg.ItemDat a(Item) & "'"
Else:
Org = Org & " , '" & Forms!AllotSearch_frm!lstO rg.ItemDat a(Item) & "'"
End If
Next
Org = "IN(" & Org & ")"
CostCenter = ""
For Each Item In Forms!AllotSearch_frm!lstC ostCenter. ItemsSelec ted
If CostCenter = "" Then
CostCenter = "'" & Forms!AllotSearch_frm!lstC ostCenter. ItemData(I tem) & "'"
Else:
CostCenter = CostCenter & " , '" & Forms!AllotSearch_frm!lstC ostCenter. ItemData(I tem) & "'"
End If
Next
CostCenter = "IN(" & CostCenter & ")"
Fund = ""
For Each Item In Forms!AllotSearch_frm!lstF und.ItemsS elected
If Fund = "" Then
Fund = "'" & Forms!AllotSearch_frm!lstF und.ItemDa ta(Item) & "'"
Else:
Fund = Fund & " , '" & Forms!AllotSearch_frm!lstF und.ItemDa ta(Item) & "'"
End If
Next
Fund = "IN(" & Fund & ")"
PEC = ""
For Each Item In Forms!AllotSearch_frm!lstP EC.ItemsSe lected
If PEC = "" Then
PEC = "'" & Forms!AllotSearch_frm!lstP EC.ItemDat a(Item) & "'"
Else:
PEC = PEC & " , '" & Forms!AllotSearch_frm!lstP EC.ItemDat a(Item) & "'"
End If
Next
PEC = "IN(" & PEC & ")"
strSQL = "SELECT Distinct Final_Table.ID, dbo_tblOrgLook_master.Anal yst, dbo_tblOrgLook_master.Org, dbo_tblOrgLook_master.OrgN ame, dbo_tblOrgLook_master.Cost Center, dbo_tblOrgLook_master.Fund , dbo_tblOrgLook_master.PEC, dbo_tblOrgLook_master.Prog ramName, Final_Table.[Org Name:], Final_Table.CostCen, Final_Table.[Fund:], Final_Table.[Line Item:], Final_Table.[Item Number:], Final_Table.[Total Initial:], Final_Table.[BC1Change], Final_Table.[TotalBC1], Final_Table.[BC2Change], Final_Table.[TotalBC2], Final_Table.[BC3Change], Final_Table.[TotalBC3], Final_Table.[BC4Change], Final_Table.[TotalBC4]"
strSQL = strSQL & "FROM Final_Table LEFT JOIN dbo_tblOrgLook_master ON (Final_Table.CostCen = dbo_tblOrgLook_master.Cost Center) AND (Final_Table.PEC = dbo_tblOrgLook_master.PEC) WHERE dbo_tblOrgLook_master.Anal yst " & Analyst & " AND dbo_tblOrgLook_master.Org " & Org & " AND dbo_tblOrgLook_master.Cost Center " & CostCenter & " AND dbo_tblOrgLook_master.Fund " & Fund & " AND dbo_tblOrgLook_master.PEC " & PEC
qdf.SQL = strSQL
End Sub
Ad I have it in my DB..is this where I am having my problem?? I have some fields that I need user input and it will not allow User input.
Public Sub querydef()
Dim db As Database, qdf As querydef, strSQL As String
Set db = CurrentDb
Set qdf = db.QueryDefs("Allot_Q")
Analyst = ""
For Each Item In Forms!AllotSearch_frm!lstE
If Analyst = "" Then
Analyst = "'" & Forms!AllotSearch_frm!lstE
Else:
Analyst = Analyst & " , '" & Forms!AllotSearch_frm!lstE
End If
Next
Analyst = "IN(" & Analyst & ")"
Org = ""
For Each Item In Forms!AllotSearch_frm!lstO
If Org = "" Then
Org = "'" & Forms!AllotSearch_frm!lstO
Else:
Org = Org & " , '" & Forms!AllotSearch_frm!lstO
End If
Next
Org = "IN(" & Org & ")"
CostCenter = ""
For Each Item In Forms!AllotSearch_frm!lstC
If CostCenter = "" Then
CostCenter = "'" & Forms!AllotSearch_frm!lstC
Else:
CostCenter = CostCenter & " , '" & Forms!AllotSearch_frm!lstC
End If
Next
CostCenter = "IN(" & CostCenter & ")"
Fund = ""
For Each Item In Forms!AllotSearch_frm!lstF
If Fund = "" Then
Fund = "'" & Forms!AllotSearch_frm!lstF
Else:
Fund = Fund & " , '" & Forms!AllotSearch_frm!lstF
End If
Next
Fund = "IN(" & Fund & ")"
PEC = ""
For Each Item In Forms!AllotSearch_frm!lstP
If PEC = "" Then
PEC = "'" & Forms!AllotSearch_frm!lstP
Else:
PEC = PEC & " , '" & Forms!AllotSearch_frm!lstP
End If
Next
PEC = "IN(" & PEC & ")"
strSQL = "SELECT Distinct Final_Table.ID, dbo_tblOrgLook_master.Anal
strSQL = strSQL & "FROM Final_Table LEFT JOIN dbo_tblOrgLook_master ON (Final_Table.CostCen = dbo_tblOrgLook_master.Cost
qdf.SQL = strSQL
End Sub
Ad I have it in my DB..is this where I am having my problem?? I have some fields that I need user input and it will not allow User input.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER