issues with filtering same field twice with 2 combo box's (1 Viewer)

sspreyer

Registered User.
Local time
Today, 05:19
Joined
Nov 18, 2013
Messages
251
To be honest I not very good with vba and now I so confused I don't fully understand the code but I doing my best


If Len(Trim(Me.cboclient)) & (Trim(Me.cboclient2)) > 0 Then
End If


so are you say I should remove the if statement


I'm still getting error Microsoft access cant find the field '1 in your expression


im at the point of putting my pc through the wall lol


cheers
shane
 

pr2-eugin

Super Moderator
Local time
Today, 12:19
Joined
Nov 30, 2011
Messages
8,494
Say what, Upload a Stripped DB.

To create a Sample DB (to be uploaded for other users to examine); please follow the steps..

1. Create a backup of the file, before you proceed..
2. Delete all Forms/Queries/Reports that are not in Question (except the ones that are inter-related)
3. Delete auxiliary tables (that are hanging loose with no relationships).
4. If your table has 100,000 records, delete 99,990 records.
5. Replace the sensitive information like Telephone numbers/email with simple UPDATE queries.
6. Perform a 'Compact & Repair' it would have brought the Size down to measly KBs..
7. (If your Post count is less than 10 ZIP the file and) Upload it..

Finally, please include instructions of which Form/Query/Code we need to look at. The preferred Access version would be A2003-A2007 (.mdb files)
 

sspreyer

Registered User.
Local time
Today, 05:19
Joined
Nov 18, 2013
Messages
251
hi paul

here goes here a sample db

you will be looking at form frmwheredates

so what im trying to achieve

to be able to enter a date range be able to add one client name or two depend on the user wants then generate a report also be able to filter records if no date range is entered by client name or if all txt box's and combo box's empty return all record's

thanks for your time

cheers

shane
 

Attachments

  • test database.zip
    244 KB · Views: 144
Last edited:

pr2-eugin

Super Moderator
Local time
Today, 12:19
Joined
Nov 30, 2011
Messages
8,494
Here is the modified CODE.
Code:
Option Compare Database
Option Explicit

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
    Dim strReport As String
    Dim strDateField As String
    Dim strWhere As String
    Dim lngView As Long
    Const strcJetDate = "\#mm\/dd\/yyyy\#"     [COLOR=Green] 'Do NOT change it to match your local settings.[/COLOR]
    
    [COLOR=Green]'DO set the values in the next 3 lines.
    'Put your report name in these quotes.[/COLOR]
    strReport = "Input Report"

    strDateField = "[Date raised]"              [COLOR=Green]'Put your field name in the square brackets in these [/COLOR]quotes

    lngView = acViewReport                     [COLOR=Green] 'Use acViewNormal to print instead of preview.[/COLOR]
    
    [COLOR=Green]'Build the filter string.
    'This will check if you have chosen a Date, to construct the Date part filter[/COLOR]
    If (IsDate(Me.txtStartDate) And IsDate(Me.txtEndDate)) And _
        (Len(Me.txtStartDate & vbNullString) > 0 Or Len(Me.txtEndDate & vbNullString) > 0) Then
        strWhere = "(" & strDateField & " BETWEEN " & Format(Me.txtStartDate, strcJetDate) & " AND " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
    Else
       [COLOR=Green] 'An error message is returned if Invalid dates are given[/COLOR]
        MsgBox "Your have entered an invalid date range (Or) Missed one of the Dates to be filtered for.", vbCritical, "Please check the dates"
        Exit Sub
    End If
        
    [COLOR=Green]'This will check if you have chosen a Client, to construct the Client filter[/COLOR]
    If Me.cboclient.ListIndex <> -1 Or Me.cboclient2.ListIndex <> -1 Then
        If strWhere <> vbNullString Then strWhere = strWhere & " AND "
        strWhere = strWhere & "(Client = '" & Me.cboclient & "' OR Client = '" & Me.cboclient2 & "')"
    End If

    [COLOR=Green]'This is to check if the Filter returns any data,
    'if it does not have any records. A message is displayed and
    'the procedure is exited.[/COLOR]
    If DCount("*", "Assets", strWhere) = 0 Then
        MsgBox "No data falls between the criteria. Please try again.", vbInformation, "No records"
        Exit Sub
    End If
    
    If CurrentProject.AllReports(strReport).IsLoaded Then
        DoCmd.Close acReport, strReport
    End If
    
[COLOR=Green]'    Open the report.[/COLOR]
    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
 

sspreyer

Registered User.
Local time
Today, 05:19
Joined
Nov 18, 2013
Messages
251
thanks Paul see some nice touches you add
thanks again you
*******Legend****


shane
 

Users who are viewing this thread

Top Bottom