WhereCondition linking to criteria (1 Viewer)

Mr_Si

Registered User.
Local time
Today, 05:34
Joined
Dec 8, 2007
Messages
163
Hi all,

I'm trying to use VBA to open a form with link criteria to column(5) in a form control but I get invalid function. (referring to "lngzEntrantID.Column" in the link criteria)

Basically, I need to make sure that it doesn't equal either value in 2 variables I have filled up.

My code is as follows:

Code:
strBanksianSelect = "SELECT TOP 1 qryShowEntrantClassPoints2.lngzShowID, " _
                                & "qryShowEntrantClassPoints2.lngzEntrantID, " _
                                & "qryShowEntrantClassPoints2.SumOfNumPoints " _
                                & "FROM qryShowEntrantClassPoints2 " _
                                & "ORDER BY SumOfNumPoints DESC;"
                
                'Instead of a where clause in the SQL Statement.
                strBanksianLinkCriteria = "lngzEntrantID.column(5) <> '" & strBanksianWinner1 & "' AND '" & strBanksianWinner2 & "'"
                
                'replace existing query with the above one
                CurrentDb.QueryDefs("qryShowEntrantClassPoints").SQL = strBanksianSelect
                
                'print sql statement
                MsgBox strBanksianSelect
                
                DoCmd.OpenForm "frmShowEntrantClassPoints", , , strBanksianLinkCriteria, , acDialog
                Me.lngzEntrantID = Form_frmShowEntrantClassPoints.lngzEntrantID


Any ideas?

I initially tried to do it in SQL but couldn't get it to work, so thought I could try it with VBA link criteria when opening the form instead.


Thanks in Advance,
Simon
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 22:34
Joined
Aug 30, 2003
Messages
36,118
I'm not totally clear on what you're doing, but in this line:

strBanksianLinkCriteria = "lngzEntrantID.column(5) <> '" & strBanksianWinner1 & "' AND '" & strBanksianWinner2 & "'"

the reference to the form control is supposed to a field name. If you're saying that column contains a field name, you'd have to concatenate that value in as you did the others.
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 22:34
Joined
Aug 30, 2003
Messages
36,118
Oh, and you have to repeat the field name:

FieldName <> 'abc' AND FieldName <> 'def'
 

Mr_Si

Registered User.
Local time
Today, 05:34
Joined
Dec 8, 2007
Messages
163
I'm not totally clear on what you're doing, but in this line:

strBanksianLinkCriteria = "lngzEntrantID.column(5) <> '" & strBanksianWinner1 & "' AND '" & strBanksianWinner2 & "'"

the reference to the form control is supposed to a field name. If you're saying that column contains a field name, you'd have to concatenate that value in as you did the others.

Hi and thanks for your response. I guess I will have to change the way i do it. lngzEntrantID is a lookup to another query with 6 different fields in.

I have an idea... Can i put, say, an unbound control on the form, make it equal to column(5) and then link the where condition to that instead? Or does it have to link to a proper field?

Oh, and yes about the repeating of the field name. That's no problem.

Thanks
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 22:34
Joined
Aug 30, 2003
Messages
36,118
Are you saying that is the field name? You can do it like this:

strBanksianLinkCriteria = lngzEntrantID.column(5) & " <> '" & strBanksianWinner1...

I guess I'm not understanding what values are where.
 

Mr_Si

Registered User.
Local time
Today, 05:34
Joined
Dec 8, 2007
Messages
163
More likely i didn't explain it well.
So if i remove the quotes in most places it will work? Cool i will give that a go and report back. Thanks
 

Mr_Si

Registered User.
Local time
Today, 05:34
Joined
Dec 8, 2007
Messages
163
That worked! Thank you.
I should have tried without the double quotes before.

However, since my values had dots in them, my final code for the link criteria was:

Code:
strBanksianLinkCriteria = "'" & lngzEntrantID.column(5) & "' <> '" & strBanksianWinner1 & " '" & lngzEntrantID.column(5) & "' <> '" & strBanksianWinner2 & "'"

That way, using the single quotes, I didn't get an invalid use of a . in my statement.

Thanks very much for your help.
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 22:34
Joined
Aug 30, 2003
Messages
36,118
Glad you got it sorted out.
 

Users who are viewing this thread

Top Bottom