Two problems in one! Filter and <>. 1

[Petemush]

Hi all,

Got a couple of problems and one seems a bit odd!

First up, when constructing a string to be used with a filter, if the Field is a Integer field, or double or whatever, is the expression different? Ie.

"[Course Source] = 'Corporate'"

works fine and course source holds a text value but

"[Length of Course] = '2'"

where Length of Course is a General Number doesn't. I get asked to provide the parameter for Length of Course. I expect the answer for that is really obvious and I'm just being dumb!

The other thing is,

If [Cost Type].Value <> &quot;Variable&quot; Then

Where Cost Type is a combo box with two possible values Variable and Fixed(3 including null)

Does not seem to work. Even if the value of cost type isn't Variable it just does the test and doesn't notice. Can anyone see what I've missed, the full code for that bit is below.

Thanks a lot,

Pete

Private Sub RCost_Check_AfterUpdate()
If [RCost Check].Value = True Then

[Room Cost].Enabled = True

If [Cost Type].Value <> &quot;Variable&quot; Then

[CostType Check].Value = True
[Cost Type].Enabled = True
[Cost Type].Value = &quot;Variable&quot;
Call Cost_Type_AfterUpdate

End If

Else

[Room Cost].Enabled = False
[Room Cost].Value = Null

End If

End Sub

 

[ToeShot]

you do not need the single quotes around a numeric value, juststring.

when comparing [Cost Type] you need to compare the text and not the value. The value is assigned by Access and is a numeric value. What you want is this

[Cost Type].Text <> &quot;Variable&quot;

HTH Dave
ToeShot@Hotmail.com
Today Is Tomorrows Yesterday. So Why Wait

 

[jgoodman00]

When dealing with strings you need to enclose them in inverted commas.
When dealing with number fields, you omit the inverted commas.

Is your combo box for [Cost Type] based on a query or a value list. If it is a query, I suspect the bound column is an integer value (i.e. not the displayed text). You will either need to refernce the column you are interested in (Me.[Cost Type].Column(1) (where column index starts at 0), or use the bound column value which corresponds to the observed value 'Variable'... James Goodman

http://www.james-goodman.co.uk

 

[Petemush]

Thanks Dave and James,

I've still got a problem in reference to the Filter.

I've tried removing the single quote marks and I still get asked to enter the Length of Course parameter when FilterOn is set to true.

I thought it might be the fact I'm using a Nz statement in case the value is null but I tried just having

&quot;[Length of Course] = 2&quot;

and I still got the same error.

The full code is:

Public Sub Find_Query(CLength As CheckBox)

Dim strSQL, strLogic As String
Dim form1, form2 As Form

Set form1 = Forms![frmFindCourses]
Set form2 = Form_frmCourses

' By some method decide if fields are ANDed or ORed and
set strLogic = &quot; AND &quot; or strLogic = &quot; OR &quot;

strSQL = &quot;(&quot;
strLogic = &quot; AND &quot;

If CLength Then

If Len(strSQL) = 1 Then
strSQL = strSQL & &quot;([Length of Course] = &quot; & Nz(form1![Length of Course Type], &quot;&quot & &quot&quot;
Else
strSQL = strSQL & strLogic & &quot;([Length of Course] = &quot; & Nz(form1![Length of Course], &quot;&quot & &quot&quot;

This bit below I tried instead of the above but it doesnt work either

strSQL = strSQL & &quot;([Length of Course] = 2)&quot;
Else
strSQL = strSQL & strLogic & &quot;([Length of Course] = 2)&quot;

End If
End If

strSQL = strSQL & &quot&quot;

DoCmd.OpenForm (&quot;frmCourses&quot
form2.Filter = strSQL
form2.FilterOn = True

End Function

 

[LLudden]

Instead of using the Filter option, just change the source for the combo box and include the filter as a WHERE clause.

so if the select for the box is:
SELECT Name from tblNames

have your code change it to:

SELECT Name from tblNames WHERE [Number of Kids] = 2

then do
combobox.requery

 

[Petemush]

Cheers everyone,

I may (whistles innocently) have been a complete fool and mumble mumble used mumble wrong mumble field mumble mumble.

So all sorted now, but...........

Is there a wildcard that I can use with filters?

And do dates need single quotes around them?

 

[LLudden]

To use a wildcard in a WHERE clause, use:

WHERE [Fieldname] LIKE 'Bob*'

To use dates, surround the date with #

#6/26/02#

 

[Petemush]

Thanks everyone,

As far as I can tel the filter matter is well and truly closed. Cheers for everyone's advice.

Pete