We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

What's Wrong in  the code

Medium Priority
180 Views
Last Modified: 2013-11-25
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

Comment
Watch Question

Éric MoreauSenior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016

Commented:
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?

Author

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...
Éric MoreauSenior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016

Commented:
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") & "'"

Author

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

Éric MoreauSenior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016

Commented:
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") & "'"

Author

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?
Senior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016
Commented:
Always format your date using the yyyy/MM/dd format.

Have you tried the previous code?

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Thanks to you! and more power!!
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.