Report with Filter Form -> Filter with LIKE statement

[njitter]

Hi,

i have got a report that has a Form before it to set a filter.

In this report is the following code:

Status_Info_3 = ""
Chapter_Filter = ""

If (Forms!ReportSelector.ChapterSelect1.Column(1) <> Empty) Then
 Chapter_Filter = "[Q7A_Chapter] Like '" & Forms!ReportSelector.ChapterSelect1.Column(1) & "*" & "'"
 Status_Info_3 = "Chapter like: " + Forms!ReportSelector.ChapterSelect1.Column(1)
End If

If (Forms!ReportSelector.ChapterSelect2.Column(1) <> Empty) Then
  If Chapter_Filter = "" Then
     Chapter_Filter = Forms!ReportSelector.ChapterSelect1.Column(1) & "*" & "'"
     Status_Info_3 = Forms!ReportSelector.ChapterSelect2.Column(1)
  Else
    Chapter_Filter = Chapter_Filter + " OR " + "[Q7A_Chapter] Like '" & Forms!ReportSelector.ChapterSelect1.Column(1) & "*" & "'"
    Status_Info_3 = Status_Info_3 + " OR " + Forms!ReportSelector.ChapterSelect1.Column(1)
  End If
End If

If (Forms!ReportSelector.ChapterSelect3.Column(1) <> Empty) Then
  If Chapter_Filter = "" Then
     Chapter_Filter = Forms!ReportSelector.ChapterSelect3.Column(1) & "*" & "'"
     Status_Info_3 = "Chapter like: " + Forms!ReportSelector.ChapterSelect3.Column(1)
  Else
    Chapter_Filter = Chapter_Filter + " OR " + "[Q7A_Chapter] Like '" & Forms!ReportSelector.ChapterSelect3.Column(1) & "*" & "'"
    Status_Info_3 = Status_Info_3 + " OR " + Forms!ReportSelector.ChapterSelect3.Column(1)
  End If
End If

I have a problem with the LIKE statement.

I'm getting the values for the ChapterSelect from a lookuptable. This has values like

1
1.1
1.2
12
12.1
13

I would like to have the following functionality:

* When i select 1.1 it should return all records that have a chapternumber like '1.1*'

* When i select 1 it should return all records that have a chapter number like '1*'

Like this:

1
1.1
1.2

BUT, it should NOT return the values:

12
12.1
13

(the statement is performing correctly but this is not the functionality i would like)

---
It's never too late to do the Right thing

 

[KenReay]

Hi

Think you are out of luck unless you can make

1.
1.1
1.2
12.
12.1
13.

then searching for Like 1.* will do what you want


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[njitter]

The Chapter_Number is a Number field. When it's a string field it would not sort well..



---
It's never too late to do the Right thing

 

[KenReay]

Hi

OK how about

Format(ChapterNo,"00.00") Like "1.*"

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[njitter]

KenReay,

i played with this Format command myself.

The problem is, the field which the number is being compared to ([Q7A_Chapter]) has the same format (it's filled from the same lookup table)

I have not found a way to make the second part look like the '1.' format...





---
It's never too late to do the Right thing

 

[KenReay]

Hi

sorry you have lost me

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[njitter]

What i mean is that both the [Q7A_Chapter] and the ChapterSelect fields have values like:

1
1.1
1.2
12
12.1



---
It's never too late to do the Right thing

 

[KenReay]

Hi

What relevant is that?

Are you about to say the user enters chapter number to list into a combo box based on lookup table?,

If yes, I am suggest a dummy text box be used as the criteria, populate this in the after update event to pass thru (say), 1.1, 1.2,..etc but make 1 into 1.

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[njitter]

I did the following thing:

If (Forms!ReportSelector.ChapterSelect1.Column(1) <> Empty) Then
 If Forms!ReportSelector.ChapterSelect1.Column(1) = 1 Then
   Chapter_Filter = "[Q7A_Chapter] between 1 and 2"
 Else
   Chapter_Filter = "[Q7A_Chapter] Like '" & Forms!ReportSelector.ChapterSelect1.Column(1) & "*" & "'"
   Status_Info_3 = Forms!ReportSelector.ChapterSelect1.Column(1)
 End If
End If

This worked fine for this situation...

---
It's never too late to do the Right thing