Using Filter condition on Select Query within a Crosstab Query in Code

[Jeff Simcock]

Hi gurus,

Apologies for the crosspost, not sure of the best place for this one.

I have a select query in Access and I use this Query in VB Code behind a
form with some combo boxes on it. What I want to do is to apply a filter to
the original Query using the Combo boxes on the form. This needs to happen
at the time I run the following:

lstrSQL = "TRANSFORM First(qrySelectDataDetails.Value) AS FirstOfValue "
& _
"SELECT id, Tag, Profile, Area " & _
"FROM qrySelectDataDetails " & _
"GROUP BY id, Tag, Profile, Area " & _
"PIVOT qrySelectDataDetails.Property;"

Set ldbsCurrent = CurrentDb()
Set lrstSource = ldbsCurrent.OpenRecordset(lstrSQL)

The filter needs to be applied to the query qrySelectDataDetails using
values from the Form Combo boxes. I have tried setting the criteria rows to
the form combos as follows:-

Forms![frmMain]![cmbProfile] for the Profile column and
Forms![frmMain]![cmbArea] for the Area column.

But I get the following error:

Error#3070 was generated by DAO.Database
The Microsoft Jet database engine does not recognize
'Forms!frmMain!cmbProfile' as a valid field name or expression.

If you understand what I am trying to achieve can you give me some advice on
what I should be doing or an alternative method?

Thanks people!
cheers
Jeff

 

[Duane Hookom]

Try:
lstrSQL = "TRANSFORM First([Value]) AS FirstOfValue " & _
"SELECT id, Tag, Profile, Area " & _
"FROM qrySelectDataDetails " & _
"WHERE Profile =" & Forms![frmMain]![cmbProfile] & _
" AND Area = '" & Forms![frmMain]![cmbArea] & "' " & _
"GROUP BY id, Tag, Profile, Area " & _
"PIVOT qrySelectDataDetails.Property;"

This assumes Profile is numeric and Area is text. I don't know if this is
correct or not.

 

[Jeff Simcock]

Hi people

Sometimes you just have to say it and write it down!!! DOH!!

lstrSQL = "TRANSFORM First(Value) AS FirstOfValue " & _
"SELECT id, Tag, Profile, Area " & _
"FROM qrySelectDataDetails " & _
"WHERE Profile LIKE '" & cmbProfile.Value & "' " & _
"AND Area LIKE '" & cmbArea.Value & "' " & _
"AND Tag LIKE '" & cmbTag.Value & "' " & _
"GROUP BY id, Tag, Profile, Area " & _
"PIVOT Property;"

mmmm and not only that...but it works...OMG...another one is born!! hehe

Unless you can suggest something better, thanks for at least reading this
one!!

cya folks
Jeff