具有ms访问权限的Crystal Report [英] Crystal Report with ms access

问题描述

我的MS ACCESS后端带有一个类型为datetime的OrderDate字段
我已在Crystal报表中创建了参数以根据订单日期检索数据，为此在前端，我已使用DateAndTimePicer选择日期并将其传递给参数.
当我从控件传递值时，我可以使用一些默认时间值(如00:00:00)传递日期，但它不起作用.

所以我只想从msaccess中检索报告中的订购日期，而不是时间.

创建报告时如何编写查询.

请帮助..

I have MS ACCESS backend with a field OrderDate whose type is datetime
I have created parameter in crystal report to retrive data based on order date, for that in the frontend i have taken DateAndTimePicer to select the date and pass it to parameter.
When i Am passing vaue from ths control i can pass date with some default time value like 00:00:00 , but it is not working.

so i want to retrive only order date in my report from msaccess not the time.

how to write query for that while creating the report.

please help..

推荐答案

在下面尝试以下代码.

基于日期获取列表的方法:

Try this Code below.

Method To Get List based on Date:

Private Sub ListAttDetails()

    If obj.mycon.State = ConnectionState.Open Then
        obj.mycon.Close()
    End If
    Dim adp As New OleDbDataAdapter
    Dim ds As New DataSet

    Try
        obj.mycon.ConnectionString = obj.conString
        obj.mycon.Open()
        cmd = obj.mycon.CreateCommand
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "PrcAttRpt"
        Call PassParam(cmd, "LIST_ATT_DET")
        cmd.ExecuteNonQuery()
        adp.SelectCommand = cmd
        adp.Fill(ds)
        Me.dgv_empdetails.DataSource = ds.Tables(0).DefaultView
        obj.mycon.Close()
        cmd.Parameters.Clear()
    Catch ex As Exception
        obj.mycon.Close()
        MsgBox(ex.Message)
    Finally
        obj.mycon.Close()
    End Try

End Sub

传递参数:

Passing Parameters:

Private Sub PassParam(ByVal CmdObj As OleDbCommand, ByVal QryStr As String)
    CmdObj.Parameters.AddWithValue("@QryId", QryStr)
    CmdObj.Parameters.AddWithValue("@StrXml", DBNull.Value)
    CmdObj.Parameters.AddWithValue("@PkId", DBNull.Value)
    If fromDate <> "" Then
        CmdObj.Parameters.AddWithValue("@FDate", fromDate)
    Else
        CmdObj.Parameters.AddWithValue("@FDate", DBNull.Value)
    End If
    If toDate <> "" Then
        CmdObj.Parameters.AddWithValue("@TDate", toDate)
    Else
        CmdObj.Parameters.AddWithValue("@TDate", DBNull.Value)
    End If
    If curDate <> "" Then
        CmdObj.Parameters.AddWithValue("@Param1", curDate)
    Else
        CmdObj.Parameters.AddWithValue("@Param1", DBNull.Value)
    End If
    CmdObj.Parameters.AddWithValue("@Param2", DBNull.Value)
    CmdObj.Parameters.AddWithValue("@Param3", DBNull.Value)
    CmdObj.Parameters.AddWithValue("@Param4", DBNull.Value)
    CmdObj.Parameters.AddWithValue("@Param5", DBNull.Value)
    CmdObj.Parameters.AddWithValue("@Param6", DBNull.Value)
    CmdObj.Parameters.AddWithValue("@Param7", DBNull.Value)
    CmdObj.Parameters.AddWithValue("@Param8", DBNull.Value)
    CmdObj.Parameters.AddWithValue("@hdnUsr", UsrNm)
End Sub

在程序中

In Procedure

SELECT RTRIM(Emp_Name) AS 'Name', RTRIM(Attendance) AS 'Attendance', dbo.gefgDMY(Att_Date) AS 'Date',
    RTRIM(dbo.FormatDateTime(Att_Time,'HH:MM:SS 12')) AS 'Time',
    RTRIM(dprj.Proj_Name) AS 'Day Site', RTRIM(nprj.Proj_Name) AS 'Noon Site',
    RTRIM(ISNULL(Proj_Status,'::::')) AS 'Status', RTRIM(ISNULL(DA_Alloted,0)) AS 'DA'
    FROM tb_attendance att, tb_employee emp, tb_project_list dprj, tb_project_list nprj
    WHERE att.Att_Date = dbo.gefgChar2Date(@OrderDate)

执行功能之前

Before Execute the Function

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gefgChar2Date]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[gefgChar2Date]
GO

CREATE Function gefgChar2Date(@dt Char(10))
returns	Char(11)
 With Encryption
as
Begin
	Declare @res char(11)
	if (@dt = NULL or Ltrim(Rtrim(@dt)) = '') set @dt = NULL
	Select @res = convert(char(11),convert(datetime,@dt,103),106)
	return (@res)
End
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

已添加代码标签-LOSMAC [/EDIT]

Code tags added - LOSMAC[/EDIT]

这篇关于具有ms访问权限的Crystal Report的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！