Run a report based upon different conditions

[aman]

Hi guys

I am designing a search from to search for a customer. based upon following input:

I have in total 5 textboxes.

1. Textbox for "Polno".
2. Textbox for "Firstname"
3. Textbox for "Surname".
4. Textbox for "All or part of Address/City"
5. Textbox for "Postcode"

Now, when the user just enter Polno and press Enter then it displays a report as follows:

If txtPolno <> Null Or txtPolno <> "" Then
 mysCriteria = "Polno=" & txtPolno & ""
doCmd.OpenReport stDocName1, acPreview, , mysCriteria, acDialog      End If

But now when somebody input data in rest of the four textboxes then I have to join the conditions.e.g if a user enters Firstname and some part of Address then to run a report condition will be different.

How to do that part?

Any help would be much appreciated.

Thanks

 

[spikepl]

mysCriteria=""
If Len(Me.MyTextBox & "")> 0 then
mysCriteria = mysCriteria & " MeFieldName=" & MyTextBox & " AND "
End if

repeat above for each textbox

at the end, if mysCriteria contains anything (use Len function to check), strip the last 4 characters from mysCriteria (because they contain "AND ")

 

[jdraw]

There is a free, sample video tutorial here that deals with multiple Possible text boxes.

I think it would help you. It's for search, but the idea is similar in my view.

http://www.datapigtechnologies.com/flashfiles/searchform.html

 

[aman]

ACtually I want to run a report based upon the data inputted in the textboxes similar to what I have done using Polno condition.

Its not too difficult but just doesn't ring a bell how to do that.

Thanks

 

[aman]

Can anyone please help me to solve my problem?

Many Thanks

 

[aman]

Hi All

I have written the following code but it doesn't work and gives me type mismatch error.

Can anyone please modify the code?

Dim stDocName1 As String
    Dim stDocName2 As String
    Dim mysCriteria As String
    Dim topLabelOnReport As String
 
 
    stDocName1 = "Report1"
 
    If txtPolno <> Null Or txtPolno <> "" Then
    mysCriteria = "Polno=" & txtPolno & ""
 
    DoCmd.OpenReport stDocName1, acPreview, , mysCriteria, acDialog
 
  Else
mysCriteria = ""
If Len(Me.Text4 & "") > 0 Then
mysCriteria = mysCriteria & " Forenames='" & Text4 & "'" & " And "
End If
If Len(Me.Text6 & "") > 0 Then
mysCriteria = mysCriteria & " Surname='" & Text6 & "'"& " And "
End If
If Len(Me.Text11 & "") > 0 Then
mysCriteria = mysCriteria & " Postcode='" & Text11 & "'" & " And "
End If
 
    i = Len(mysCriteria)
    MsgBox i
    mysCriteria = InStr(mysCriteria, 1, i - 4)
 
    DoCmd.OpenReport stDocName1, acPreview, , mysCriteria, acDialog
 
    End If

 

[aman]

Can anyone please help me to resolve my problem?

Its very urgent to resolve so please help me All Access Gurus.

 

[JANR]

i = Len(mysCriteria)
    MsgBox i
    mysCriteria = InStr(mysCriteria, 1, i - 4)

You have not dimmed i and Instr is the wrong function to use, you use Left() to trim off the last 5 chars ( " AND ")

If Len(mysCriteria) > 0 Then
      mysCriteria = Left(mysCriteria, Len(mysCriteria)-5)
      DoCmd.OpenReport stDocName1, acPreview, , mysCriteria, acDialog
End If

JR

 

[JANR]

Also this snippet:

 If txtPolno <> Null Or txtPolno <> "" Then
    mysCriteria = "Polno=" & txtPolno & ""
 
    DoCmd.OpenReport stDocName1, acPreview, , mysCriteria, acDialog

Should be re-written to:

If Len(Me.txtPolno & "") > 0 Then
      mysCriteria = "Polno='" & Me.txtPolno & "'"
      DoCmd.OpenReport stDocName1, acPreview, , mysCriteria, acDialog
Else
....

assuming txtpolno is text, if number then

mysCriteria = "polno=" & Me.txtPolno

JR

 

[aman]

Thanks a lot JANR. Its working fine.

But I want to add some new conditions in the below code:

first of all can we use like operator in the conditions because sometimes suppose the Firstname is "Carol" then it should display all customers with "Carol" in it . e.g Carol Ann, Carol Smith etc. But at present it just display the customers with "Carol" name in it.
And the below changes:
a. If the code returns one record with the value "Sent" in "Field1" then it should display the message "This has been sent".
b. if it returns "Received" then it should display "It has been received".
c.There may be instances where one customer returns both the Sent and Received results so your search will need to scroll through all the entries and display following text ‘please scan and make a copy and send it to this dept.'

stDocName1 = "Report1"
   If txtPolno <> Null Or txtPolno <> "" Then
    
    mysCriteria = "Polno=" & txtPolno & ""
     DoCmd.OpenReport stDocName1, acPreview, , mysCriteria, acDialog
     
  Else
  
  mysCriteria = ""
  If Len(Me.Text4 & "") > 0 Then
  mysCriteria = mysCriteria & "Forenames='" & Text4 & "'" & " And "
End If
If Len(Me.Text6 & "") > 0 Then
mysCriteria = mysCriteria & " Surname='" & Text6 & "'" & " And "
End If
If Len(Me.Text11 & "") > 0 Then
mysCriteria = mysCriteria & " Postcode='" & Text11 & "'" & " And "
End If
If Len(mysCriteria) > 0 Then
      mysCriteria = Left(mysCriteria, Len(mysCriteria) - 5)
      DoCmd.OpenReport stDocName1, acPreview, , mysCriteria, acDialog
End If
End If

 

[JANR]

first of all can we use like operator in the conditions because sometimes suppose the Firstname is "Carol" then it should display all customers with "Carol" in it . e.g Carol Ann, Carol Smith etc. But at present it just display the customers with "Carol" name in it.

You have been aorund the block long so you should know this, replace = with Like and add the wildcard(s)

mysCriteria = mysCriteria & "Forenames Like '" & Text4 & "*'" & " And "

a and b is relates to you report just use an undbound control and use an IIF to test [Field1]

=IIF([Field1] = "Sent", "This has been sent", Null)

or multible choices you can use Switch() function:

=Switch([Field1]= "Something","Display this", [Field1]="something else","Display this"...etc)

JR

 

[aman]

Hi JANR

Please see below the code: Now I want to specify the 3 conditions in the code below as in the last post. I have applied the condition using dlookup in the policy number condition as the policy number is always unique so it will retreive just one row always. The condition works fine and display me the message sent or received .
But how to specify the same condition in second part which is a combination of conditions like forename,surname,address,postcode .

 stDocName1 = "Report1"
   If Len(Me.txtPolno & "") > 0 Then
    
    mysCriteria = "Polno=" & txtPolno & ""
    a = DLookup("[Post To]", "tblmain", "Polno=" & txtPolno & "")
    If a = "Sent" Then
    MsgBox "It has been sent", vbInformation
else
MsgBox "It has been received", vbInformation
    End If
    DoCmd.OpenReport stDocName1, acPreview, , mysCriteria, acDialog
    Else
  
  mysCriteria = ""
  If Len(Me.Text4 & "") > 0 Then
  mysCriteria = mysCriteria & "Forenames Like '" & Text4 & "*'" & " And "
  End If
If Len(Me.Text6 & "") > 0 Then
mysCriteria = mysCriteria & "Surname like '" & Text6 & "*'" & " And "
End If
If Len(Me.Text8 & "") > 0 Then
mysCriteria = mysCriteria & "Address3 like '" & Text8 & "*'" & " And "
End If
If Len(Me.Text11 & "") > 0 Then
mysCriteria = mysCriteria & " Postcode='" & Text11 & "'" & " And "
End If
If Len(mysCriteria) > 0 Then
      mysCriteria = Left(mysCriteria, Len(mysCriteria) - 5)
  
      DoCmd.OpenReport stDocName1, acPreview, , mysCriteria, acDialog
End If
End If

Thanks for your help so far.

 

[JANR]

But how to specify the same condition in second part which is a combination of conditions like forename,surname,address,postcode

do you mean
If a="sent" Then.... etc

If so just repeat it in the else part. Just remember that the Else part will not execute if you enter something in txtPolno

JR

 

[aman]

Ok JANR. Just one more thing:

Can you please see my 1st post. In that i specified the 5 textboxes. No my code is working fine if we enter polno,forname,surname,postcode.

But for address it doesn't. As in the backing database there are 3 fields : Address1,Address2,Address3.

Now I want to compare data in Text8 with the data in Address1,2,3 fields.

How to do that?

Many Thanks

 

[JANR]

In regards to my last post if you use Dlookup in your else part when there is possible more than 1 match Access will pick the first it finds regardless on how many ther might be, so you current methode will not be accurate.

Now I want to compare data in Text8 with the data in Address1,2,3 fields.

Jeez this is going to take a performance hit, but you'll need to use the OR operator me thinks, perhaps somthing like this:

If Len(Me.Text8 & "") > 0 Then
            mysCriteria = mysCriteria & "(Address1 like '" & Text8 & "*'" & " Or " & _
                                        "Address2 Like '" & Text8 & "*'" & " Or " & _
                                        "Address3 Like '" & Text8 & "*')" & " AND "
        End If

Issue a Debug.Print mysCriteria when the string is completed and examine it in the immediate window for faults.

JR