Report from form input

[Guest]

I am trying to build a report based on the input from a form (1 input field
only) where the users input could be say Rolling Stones or The Rolling Stones
or Stones. The report needs to be able to display all records with all or any
of these words.

I have the following code that I have used to generate other reports with
exact record matches but am unable to understand visual basic enough to be
able to alter it to ensure I get all the records as suggested above. The code
is:

Private Sub FindRecords_AfterUpdate()
Dim strCriteria As String
Dim stDocName As String


stDocName = "Report Table"
strCriteria = "[Description] Like '" & FindRecords & "'"
DoCmd.OpenReport stDocName, acPreview, , strCriteria
DoCmd.Maximize
End Sub

What changes do I need to make to get all the records?

Thanks in advance.


Mabeline.

 

[Guest]

Try this:

strCriteria = "[Description] Like '" & "*" & FindRecords & "*" & "'"

That should do the trick...

hth

 

[John Spencer]

If you want the report to find any of the words you need something like the
following UNTESTED modification to your code

Private Sub FindRecords_AfterUpdate()
Dim strCriteria As String
Dim stDocName As String
Dim iLoop as Long
Dim arStr as Variant

If Len(Me.FindRecords & "")>0 then
'Get list of records
arStr = Split(Me.FindRecords," ")

'Build strCriteria for each word with OR as the conjunction
For iLoop = LBound(arStr) to UBound(arStr)
strCriteria = StrCriteria & " OR [Description] Like '*" & FindRecords &
"*'"
next iLoop

'Strip off the OR at the beginning of strCriteria
strCriteria = Mid(StrCriteria,5)
End If

stDocName = "Report Table"

DoCmd.OpenReport stDocName, acPreview, , strCriteria
DoCmd.Maximize
End Sub

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Hi Maurice,

This worked a treat. Thanks very much.

I have another challenge that I am working on at the moment with another
report retrieving data from 2 tables where the search criteria will come from
a field on a form that the user enters. I want to have the report display all
the fields from both tables. Only 1 field is the same in both tables. The
first part of the report will be from the first table and the bottom section
of the report will be from the second table. The relationship is one (table
1) to many (table 2). Would it be ok if I was to respond through this link if
I can't get the report working to see if you could help me out again?

Thanks in advance.


Mabeline.

Try this:

strCriteria = "[Description] Like '" & "*" & FindRecords & "*" & "'"

That should do the trick...

hth


--
Maurice Ausum

I am trying to build a report based on the input from a form (1 input field
only) where the users input could be say Rolling Stones or The Rolling Stones
or Stones. The report needs to be able to display all records with all or any
of these words.

I have the following code that I have used to generate other reports with
exact record matches but am unable to understand visual basic enough to be
able to alter it to ensure I get all the records as suggested above. The code
is:

Private Sub FindRecords_AfterUpdate()
Dim strCriteria As String
Dim stDocName As String


stDocName = "Report Table"
strCriteria = "[Description] Like '" & FindRecords & "'"
DoCmd.OpenReport stDocName, acPreview, , strCriteria
DoCmd.Maximize
End Sub

What changes do I need to make to get all the records?

Thanks in advance.


Mabeline.

 

[Guest]

Sure, but just start a new 'thread' because chances are that more people are
reading it again with a better chance of solving your question... ;-)
--
Maurice Ausum

Hi Maurice,

This worked a treat. Thanks very much.

I have another challenge that I am working on at the moment with another
report retrieving data from 2 tables where the search criteria will come from
a field on a form that the user enters. I want to have the report display all
the fields from both tables. Only 1 field is the same in both tables. The
first part of the report will be from the first table and the bottom section
of the report will be from the second table. The relationship is one (table
1) to many (table 2). Would it be ok if I was to respond through this link if
I can't get the report working to see if you could help me out again?

Thanks in advance.


Mabeline.

Try this:

strCriteria = "[Description] Like '" & "*" & FindRecords & "*" & "'"

That should do the trick...

hth


--
Maurice Ausum

I am trying to build a report based on the input from a form (1 input field
only) where the users input could be say Rolling Stones or The Rolling Stones
or Stones. The report needs to be able to display all records with all or any
of these words.

I have the following code that I have used to generate other reports with
exact record matches but am unable to understand visual basic enough to be
able to alter it to ensure I get all the records as suggested above. The code
is:

Private Sub FindRecords_AfterUpdate()
Dim strCriteria As String
Dim stDocName As String


stDocName = "Report Table"
strCriteria = "[Description] Like '" & FindRecords & "'"
DoCmd.OpenReport stDocName, acPreview, , strCriteria
DoCmd.Maximize
End Sub

What changes do I need to make to get all the records?

Thanks in advance.


Mabeline.