Please help in Form to Report

D

DudeXRule

New member
Local time
Today, 21:24
Joined
Mar 12, 2009
Messages
2
I am having this problem with my coding that i have created a form that will have a combo box with two values of intialing a report,,

the thing is when i ever want to do the docmd.openreport

it gives empty report , and i cant see where is the error ???!!!

this is the code if could someone look at it
Code: 
Private Sub btnPreview_Click()

     

    'Purpose:       Filter a report to a date range.
   
    
    Dim strReport_1 As String
    Dim strReport_2 As String
    Dim strDateField As String
    Dim strWhere As String
    Dim lngView As Long
    Dim str As String
    
    
 
    
 
    strReport_1 = "Report_Supply_Log_StaffDeptByDate"     
    strReport_2 = "Report_SupplyLog_SupplierByDate"
    
    strDateField = "[Supply_Log.Date]" 
    
    lngView = acViewPreview    
    
   
    If (Me.Report_type.value = "Supplier Report") Then
    
            If IsDate(Me.txtStartDate) Then
                str = "Supplier"
                strWhere = "( [Supply_Log.Supply_Type]   = " & str & "   And   " & Format(strDateField, "mm/dd/yyyy") & " >= " & Format(Me.txtStartDate, "mm/dd/yyyy") & " )"
           
            End If
            If IsDate(Me.txtEndDate) Then
                If strWhere <> vbNullString Then
                    strWhere = strWhere & " AND "
                End If
                strWhere = strWhere & "( [Supply_Log.Supply_Type] = " & str & "  And  " & Format(strDateField, "mm/dd/yyyy") & " < " & Format(Me.txtEndDate + 1, "mm/dd/yyyy") & ")"
            
            End If
            
         
            
            'Close the report if already open

            If CurrentProject.AllReports(strReport_2).IsLoaded Then
                DoCmd.Close acReport, strReport_2
            End If
            
            'Open the report.
            
            Debug.Print strWhere
            
            DoCmd.OpenReport strReport_2, lngView, , strWhere
            

        
            
    ElseIf (Me.Report_type = "Staff/Dept Report") Then
    
            If IsDate(Me.txtStartDate) Then
            
                strWhere = "( Supply_Log.Supply_Type   = Staff/Dept   And   " & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & " )"
            End If
            If IsDate(Me.txtEndDate) Then
                If strWhere <> vbNullString Then
                    strWhere = strWhere & " AND "
                End If
                strWhere = strWhere & "( Supply_Log.Supply_Type = Staff/Dept  And  " & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
            End If
            
            'Close the report if already open
            If CurrentProject.AllReports(strReport_2).IsLoaded Then
                DoCmd.Close acReport, strReport_2
            End If
            
         
            
            Debug.Print strWhere
            DoCmd.OpenReport strReport_2, lngView, , strWhere

        

    End If
    

End Sub
thanx in Advance
 
the sql filter isnt correct

dates in sql statements need to have hash characters around them ##

so it looks like, in the finished form.

where mydate > #12 Mar 2009#

------
also, are you in UK or US, because a date in a SQL will always be treated as US, unless you stop it happening - just a catch to watch out for

ie 12/3/09 will be 3rd December

--------
i think if you can sort these issues you will be OK
 
Hi,

note a view things.

A string in sql must be set in quotation marks (simple ones ' not ")
second Supply_Log.Date, i.e. strDateField was set in the first else clause als date rather than string.
third dates must be set into #30/05/2009# this format.

Hope this helps. Regards Mary.

Private Sub btnPreview_Click()



'Purpose: Filter a report to a date range.


Dim strReport_1 As String
Dim strReport_2 As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Dim str As String





strReport_1 = "Report_Supply_Log_StaffDeptByDate"
strReport_2 = "Report_SupplyLog_SupplierByDate"

strDateField = "Supply_Log.Date" 'or [Supply_Log].[Date] '"[Supply_Log.Date]"

lngView = acViewPreview


If (Me.Report_type.value = "Supplier Report") Then

If IsDate(Me.txtStartDate) Then
str = "Supplier"
strWhere = "( [Supply_Log.Supply_Type] =' " & str & "' And " & strDateField & " >= #" & Format(Me.txtStartDate, "mm/dd/yyyy") & "# )"

End If
If IsDate(Me.txtEndDate) Then
If strWhere <> vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "( [Supply_Log.Supply_Type] = '" & str & "' And " & strDateField & " < # Format(Me.txtEndDate + 1, "mm/dd/yyyy") & ")"

End If



'Close the report if already open

If CurrentProject.AllReports(strReport_2).IsLoaded Then
DoCmd.Close acReport, strReport_2
End If

'Open the report.

Debug.Print strWhere

DoCmd.OpenReport strReport_2, lngView, , strWhere




ElseIf (Me.Report_type = "Staff/Dept Report") Then

If IsDate(Me.txtStartDate) Then

strWhere = "( Supply_Log.Supply_Type = 'Staff/Dept' And " & strDateField & " >= #" & Format(Me.txtStartDate, strcJetDate) & "# )"
End If
If IsDate(Me.txtEndDate) Then
If strWhere <> vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "( Supply_Log.Supply_Type = 'Staff/Dept' And " & strDateField & " < #" & Format(Me.txtEndDate + 1, strcJetDate) & "#)"
End If

'Close the report if already open
If CurrentProject.AllReports(strReport_2).IsLoaded Then
DoCmd.Close acReport, strReport_2
End If



Debug.Print strWhere
DoCmd.OpenReport strReport_2, lngView, , strWhere



End If


End Sub
Click to expand...
 
Hi,

note a view things.

A string in sql must be set in quotation marks (simple ones ' not ")
second Supply_Log.Date, i.e. strDateField was set in the first else clause als date rather than string.
third dates must be set into #30/05/2009# this format.

Hope this helps. Regards Mary.

Private Sub btnPreview_Click()



'Purpose: Filter a report to a date range.


Dim strReport_1 As String
Dim strReport_2 As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Dim str As String





strReport_1 = "Report_Supply_Log_StaffDeptByDate"
strReport_2 = "Report_SupplyLog_SupplierByDate"

strDateField = "Supply_Log.Date" 'or [Supply_Log].[Date] '"[Supply_Log.Date]"

lngView = acViewPreview


If (Me.Report_type.value = "Supplier Report") Then

If IsDate(Me.txtStartDate) Then
str = "Supplier"
strWhere = "( [Supply_Log.Supply_Type] =' " & str & "' And " & strDateField & " >= #" & Format(Me.txtStartDate, "mm/dd/yyyy") & "# )"

End If
If IsDate(Me.txtEndDate) Then
If strWhere <> vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "( [Supply_Log.Supply_Type] = '" & str & "' And " & strDateField & " < # Format(Me.txtEndDate + 1, "mm/dd/yyyy") & ")"

End If



'Close the report if already open

If CurrentProject.AllReports(strReport_2).IsLoaded Then
DoCmd.Close acReport, strReport_2
End If

'Open the report.

Debug.Print strWhere

DoCmd.OpenReport strReport_2, lngView, , strWhere




ElseIf (Me.Report_type = "Staff/Dept Report") Then

If IsDate(Me.txtStartDate) Then

strWhere = "( Supply_Log.Supply_Type = 'Staff/Dept' And " & strDateField & " >= #" & Format(Me.txtStartDate, strcJetDate) & "# )"
End If
If IsDate(Me.txtEndDate) Then
If strWhere <> vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "( Supply_Log.Supply_Type = 'Staff/Dept' And " & strDateField & " < #" & Format(Me.txtEndDate + 1, strcJetDate) & "#)"
End If

'Close the report if already open
If CurrentProject.AllReports(strReport_2).IsLoaded Then
DoCmd.Close acReport, strReport_2
End If



Debug.Print strWhere
DoCmd.OpenReport strReport_2, lngView, , strWhere



End If


End Sub
Click to expand...
 
thank you very much mary
i am new to these things with the strings
but i am learning now
thank again;)
 
You must log in or register to reply here.

Similar threads

mloucel
Solved SQL Help..
Replies
10
Views
592
mloucel
mloucel
A
FILTER PRINT REPORT
Replies
2
Views
278
arnelgp
arnelgp
Gasman
Solved What is going on here?
Replies
19
Views
604
Gasman
Gasman
A
Solved VBA code for deleting Outlook 'Appointments' lying within a specified date range
Replies
8
Views
622
Gasman
Gasman
mloucel
Solved Little Help with VBA..
Replies
7
Views
389
theDBguy
theDBguy

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom