Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 164
  • Last Modified:

What's Wrong in the code

What's wrong in my code when i execute it will not follow the desired range?
here is my table

if exists (select * from sysobjects where id = object_id(N'[dbo].[TIMELOG]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TIMELOG]
GO

CREATE TABLE [dbo].[TIMELOG] (
      [LOG_ID] [int] IDENTITY (1, 1) NOT NULL ,
      [EM_ID] [varchar] (50) NULL ,
      [DATE_LOG] [varchar] (50) NULL ,
      [IN_AM] [varchar] (50) NULL ,
      [OUT_AM] [varchar] (50) NULL ,
      [TOTAL_AM] [varchar] (50) NULL ,
      [IN_PM] [varchar] (50) NULL ,
      [OUT_PM] [varchar] (50) NULL ,
      [TOTAL_PM] [varchar] (50) NULL ,
      [GRAND_TOTAL] [varchar] (50) NULL
) ON [PRIMARY]
GO
Private Sub Command1_Click()
Dim Report As CRAXDRT.Report
Dim str As String
    Set Report = New Myreport
    str = "{TIMELOG.EM_ID} in '" & frmCheck.Text & "' and {TIMELOG.DATE_LOG} in '" & eFrom.Value & "' to '" & eTo.Value & "'"
    Report.RecordSelectionFormula = str
    Report.ReadRecords
    Call Report.SelectPrinter(Report.DriverName, Report.PrinterName, Report.PortName)
    Screen.MousePointer = vbHourglass
    Vers.ReportSource = Report
    Vers.ViewReport
    Screen.MousePointer = vbDefault
    Vers.ReportSource = Report
    Vers.Zoom 94
    Set Report = Nothing
End Sub

Open in new window

0
Whing Dela Cruz
Asked:
Whing Dela Cruz
  • 4
  • 4
1 Solution
 
Éric MoreauSenior .Net ConsultantCommented:
have you tried this shorter filter (I have changed the IN for =) :

str = "{TIMELOG.EM_ID} = '" & frmCheck.Text & "' "

what is the value of frmCheck.Text?
0
 
Whing Dela CruzAuthor Commented:
Hello emoreau,
This code will run
str = "{TIMELOG.EM_ID}='" & frmCheck.Text & "'

but if i add
And {TIMELOG.DATE_LOG} in '" & eFrom.Value & "' to '" & eTo.Value & "'"
the range doesn't follow...
0
 
Éric MoreauSenior .Net ConsultantCommented:
Your Date_Log field is a VARCHAR field? Shouldn't it be a date field?

How are the values formatted in that field? Which control type is eFrom and eTo?

Have you tried:

And {TIMELOG.DATE_LOG} in '" & format(eFrom.Value, "yyyy-MM-dd") & "' to '" & format(eTo.Value, "yyyy-MM-dd") & "'"
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Whing Dela CruzAuthor Commented:
I'd  changed it to datetime,  but same problem encountered. My code goes like this

    [LOG_ID] [int] IDENTITY (1, 1) NOT NULL ,
    [EM_ID] [varchar] (50) NULL ,
    [DATE_LOG] [datetime] NULL ,
    [IN_AM] [varchar] (50) NULL ,
    [OUT_AM] [varchar] (50) NULL ,
    [TOTAL_AM] [varchar] (50) NULL ,
    [IN_PM] [varchar] (50) NULL ,
    [OUT_PM] [varchar] (50) NULL ,
    [TOTAL_PM] [varchar] (50) NULL ,
    [GRAND_TOTAL] [varchar] (50) NULL


Private Sub Command3_Click()
Dim Report As CRAXDRT.Report
Dim str As String
    Set Report = New SPMyreport
 
    str = "{TIMELOG.EM_ID}='" & frmCheck.Text & "' And {TIMELOG.DATE_LOG} in '" & Format(eFrom.Value, "yyyy-MM-dd") & "' to '" & Format(eTo.Value, "yyyy-MM-dd") & "'"
    Report.RecordSelectionFormula = str
    Report.ReadRecords
    Call Report.SelectPrinter(Report.DriverName, Report.PrinterName, Report.PortName)
    Screen.MousePointer = vbHourglass
    Vers.ReportSource = Report
    Vers.ViewReport
    Screen.MousePointer = vbDefault
    Vers.ReportSource = Report
    Vers.Zoom 94
    Set Report = Nothing
End Sub

Open in new window

0
 
Éric MoreauSenior .Net ConsultantCommented:
Which control type is eFrom and eTo?

Have you tried:

str ="{TIMELOG.DATE_LOG} in '" & format(eFrom.Value, "yyyy-MM-dd") & "' to '" & format(eTo.Value, "yyyy-MM-dd") & "'"
0
 
Whing Dela CruzAuthor Commented:
When i tried , i need to backward 2 months before the report will display.
my record is only in the month of february. If i start 2/01/2009 no report will display but if i start 12/01/2009 the report will display. The problem is the efrom because i need to backward a certain month before it works. the eTo is okey upon testing... What should i do now?
0
 
Éric MoreauSenior .Net ConsultantCommented:
Always format your date using the yyyy/MM/dd format.

Have you tried the previous code?
0
 
Whing Dela CruzAuthor Commented:
Thanks to you! and more power!!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now