Search a job reference

[Sunnylei]

If you have a Text field then it should be in this format:

strWhere = "[TheTextField] =[COLOR=red] '[/COLOR]" & Me.txtControlName & "[COLOR=red]'[/COLOR]"

You're using Not IsNull, but in the code I wrote it's Len(). Your code is fine if the Allow Zero Length property of the Text field is set to No.

I have extracted your code. However, the filter is not working. For example, I enter Start Cm Code: 246, End Cm Code: 246. It shows 246 and other Codes as well. I attach my entire codes below, can you have a look what is missing.

Private Sub cmdPreview_Click()
'On Error GoTo Err_Handler      'Remove the single quote from start of this line once you have it working.
    'Purpose:       Filter a report to a date range.
    'Documentation: [URL]http://allenbrowne.com/casu-08.html[/URL]
    'Note:          Filter uses "less than the next day" in case the field has a time component.
    Dim strReport As String
    Dim strCmCodeField As String
    Dim strWhere As String
    Dim lngView As Long
  
    
    'DO set the values in the next 3 lines.
    strReport = "rptMainContractLabourCosting"      'Put your report name in these quotes.
    strCmCodeField = "[CM Code]" 'Put your field name in the square brackets in these quotes.
    lngView = acViewPreview     'Use acViewNormal to print instead of preview.
    
    'Build the filter string.
    If Len(Me.txtStartCmCode & vbNullString) = 0 Then
    
        strWhere = "(" & strCmCodeField & " = ' " & (Me.txtStartCmCode & " ' ")
        
    End If
    If Len(Me.txtEndCmCode) Then
         If strWhere <> vbNullString Then
         strWhere = strWhere & " AND "
         
         End If
        If (Me.txtEndCmCode & vbNullString) = 0 Then
    
        strWhere = "(" & strCmCodeField & " = ' " & (Me.txtEndCmCode & " ' ")
     
         
     End If
      'Close the report if already open: otherwise it won't filter properly.
       If CurrentProject.AllReports(strReport).IsLoaded Then
        DoCmd.Close acReport, strReport
       End If
       
       End If
     
       
    
    'Open the report.
    'Debug.Print strWhere        'Remove the single quote from the start of this line for debugging purposes.
    DoCmd.OpenReport strReport, lngView, , strWhere
Exit_Handler:
    Exit Sub
Err_Handler:
    If Err.Number <> 2501 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
    End If
    Resume Exit_Handler
End Sub

Many thanks

 

[vbaInet]

Sunny, please pay close attention to the way the code is written in posts #14 and #20. What you've written is NOT the same.

 

[Sunnylei]

Sunny, please pay close attention to the way the code is written in posts #14 and #20. What you've written is NOT the same.

Thanks. I notice that. I have updated it. However, it still has an error: missing),], or It Item in query expression '([CM Code] = '326'.'
Debug: DoCmd.OpenReport strReport, lngView, , strWhere

Can you help me to spot error below. Many thanks

 If Len(Me.txtStartCmCode & vbNullString) <> 0 Then
    
       strWhere = "(" & strCmCodeField & " = ' " & (Me.txtStartCmCode & " ' ")
        
    End If
    
        
       If Len(Me.txtEndCmCode & vbNullString) <> 0 Then
            If strWhere <> vbNullString Then
            strWhere = strWhere & "AND"
            
        strWhere = "(" & strCmCodeField & " = ' " & (Me.txtEndCmCode & " ' ")
        
    End If

 

[vbaInet]

We've already established that [CM Code] is not Text, but it's a Number field. You only wrap the value in single quotes when you're dealing with Text. The other thing is both criteria is based on one field so instead of using AND you use OR. Finally, no need for the parentheses if you're using ORs or ANDs alone, if it's mixed then you will need parens:

    Dim strWhere As String
    
    ' Build the criteria
    If Len(Me.txtStartCmCode & vbNullString) <> 0 Then
       strWhere = "[CM Code] = " & Me.txtStartCmCode
    End If
        
    If Len(Me.txtEndCmCode & vbNullString) <> 0 Then
        If Len(strWhere) <> 0 Then
            strWhere = strWhere & " OR [CM CODE] = " & Me.txtEndCmCode
        Else
            strWhere = "[CM CODE] = " & Me.txtEndCmCode
        End If
    End If

    'Close the report if already open: otherwise it won't filter properly.
    If CurrentProject.AllReports(strReport).IsLoaded Then
        DoCmd.Close acReport, strReport, acSaveNo
    End If

    'Open the report.
    DoCmd.OpenReport "rptMainContractLabourCosting", acViewPreview, , strWhere

I've added the parts directly.

 

[Sunnylei]

We've already established that [CM Code] is not Text, but it's a Number field. You only wrap the value in single quotes when you're dealing with Text. The other thing is both criteria is based on one field so instead of using AND you use OR. Finally, no need for the parentheses if you're using ORs or ANDs alone, if it's mixed then you will need parens:

    Dim strWhere As String
 
    ' Build the criteria
    If Len(Me.txtStartCmCode & vbNullString) <> 0 Then
       strWhere = "[CM Code] = " & Me.txtStartCmCode
    End If
 
    If Len(Me.txtEndCmCode & vbNullString) <> 0 Then
        If Len(strWhere) <> 0 Then
            strWhere = strWhere & " OR [CM CODE] = " & Me.txtEndCmCode
        Else
            strWhere = "[CM CODE] = " & Me.txtEndCmCode
        End If
    End If
 
    'Close the report if already open: otherwise it won't filter properly.
    If CurrentProject.AllReports(strReport).IsLoaded Then
        DoCmd.Close acReport, strReport, acSaveNo
    End If
 
    'Open the report.
    DoCmd.OpenReport "rptMainContractLabourCosting", acViewPreview, , strWhere

I've added the parts directly.

I updated the codes based on yours. It still has a problem: Syntax error in string in query expression '[Cm Code]='0245 OR [CM Code] = 0245'
Debug: DoCmd.OpenReport "rptMainContractLabourCosting", acViewPreview, , strWhere

I think the following codes may be referred to it

 If Len(strWhere) <> 0 Then
         [B] strWhere = strWhere & " OR [CM Code] = " & Me.txtEndCmCode[/B]
          
          Else
           strWhere = "[CM Code]= '" & Me.txtEndCmCode
              
    End If

 

[vbaInet]

What is the data type of CM Code?

 

[Sunnylei]

What is the data type of CM Code?

It was number. Now I changed to text.

 

[JANR]

No that you have change it (why??) to text, then the single qoutes has to come back.

Just to repeat:

Textfields:

...[SomeField] = '" & Me.MyControl & "'

Number fields:

...[Somefield] = " & Me.Mycontrol

JR

 

[vbaInet]

It was number. Now I changed to text.

Change it back to Number and use the code given.

 

[Sunnylei]

Change it back to Number and use the code given.

May I ask if txet field can be filtered?

 

[vbaInet]

It can be but naturally you should be filtering using the Primary keys or Foreign keys if possible. PKs and FKs are normally Numeric.

 

[Sunnylei]

It can be but naturally you should be filtering using the Primary keys or Foreign keys if possible. PKs and FKs are normally Numeric.

If any possible set a criteria in Query as: Like " ' "?

 

[vbaInet]

Absolument!

http://www.techonthenet.com/access/queries/like.php
http://www.techonthenet.com/access/queries/like2007.php
http://www.techonthenet.com/sql/like.php

Try it out.

 

[Sunnylei]

Absolument!

http://www.techonthenet.com/access/queries/like.php
http://www.techonthenet.com/access/queries/like2007.php
http://www.techonthenet.com/sql/like.php

Try it out.

Thanks!!! I will let you know it.

 

[Sunnylei]

Absolument!

http://www.techonthenet.com/access/queries/like.php
http://www.techonthenet.com/access/queries/like2007.php
http://www.techonthenet.com/sql/like.php

Try it out.

Thanks for your help! I made it!

 

[vbaInet]

Good job sunny!