Link to home
Start Free TrialLog in
Avatar of CaptainGiblets
CaptainGibletsFlag for United Kingdom of Great Britain and Northern Ireland

asked on

access vba

I am trying to run some code that builds a select query. However once it has finished building the columns i am trying to use for the where clauses are not being displayed properly

here is my code

Dim length As Integer
Dim length2 As Integer
Dim labelname As String
Dim status As String

calculation = Screen.ActiveControl.NAME
length = Len(calculation) - 4
calculation = right(calculation, length)
length = (InStr(calculation, "_") - 1)
calculation = Left(calculation, length)

labelname = Screen.ActiveControl.NAME
length2 = (InStrRev(labelname, "_"))
length2 = Len(labelname) - length2
labelname = right(labelname, length2)
labelname = "label" & labelname
datecolumn = labelname & ".Column(0)"
status = labelname & ".Column(1)"

If Forms!frm_dashboard!cbo_ecomm.Value = -1 Then
    view1 = view1 & "_ecomm"
End If

Dim dashboardstr As String

If calculation = "1to7" Then
    dashboardstr = "select * from v_dashboard"
    dashboardstr = dashboardstr & " where getdate() - " & datecolumn & " <=7 and [internal status] = " & status & ""
End If

Open in new window

I have a message box that displays dashboardstr and it shows the actual string for datecolumn and status rather than the column from the combo box.

What can i change to get it to select the actual values rather than use the string?
Avatar of mbizup
Flag of Kazakhstan image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of CaptainGiblets


brilliant, thanks for quick response.