Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Beginning VB 6 For coders who are new to Visual Basic, working in VB version 6 (not .NET).
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Beginning VB 6 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 20th, 2008, 03:14 AM
Authorized User
 
Join Date: Jul 2006
Location: dera ismail khan, nwfp, Pakistan.
Posts: 43
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to naveed77 Send a message via Yahoo to naveed77
Default date query problem

dear solvers,

here is the problem i m facing i want to get data between dates in the same field on crystal report. but i dont know wats wrong in da query coz it gives syntax error, here is da coding

Private Sub Form_Load()

Dim Report As New rptrecord

Screen.MousePointer = vbHourglass
cr.ReportSource = Report

If frmsearch.optdate.Value = True Then

Report.RecordSelectionFormula = "{record.crdate} >= Date(" & Format(frmsearch.dtfrom.Value, "yyyy,mm,dd") & " and {record.crdate} <= Date(" & Format(frmsearch.dtto.Value, "yyyy,mm,dd")

Else

If frmsearch.optletter.Value = True And frmsearch.txtltrno.Text <> "" Then
Report.RecordSelectionFormula = "{record.Letter_No}='" & frmsearch.txtltrno.Text & "'"

cr.ViewReport
Screen.MousePointer = vbDefault

End If
End If

End Sub
 
Old February 20th, 2008, 04:35 PM
Friend of Wrox
 
Join Date: Nov 2004
Location: Port Orchard, WA, USA.
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts
Default

Naveed, If you start your listing with [ c o d e ] (but without the spaces), the posting will switch to fixed spacing, literal mode. Then if you indent, the indenting will be preserved, and so on. Finish with [ / c o d e ] (but without the spaces), to end the fixed-pitch font. You can toggle back and forth by alternating the [] and [/] tags.

For example
Code:
Private Sub Form_Load()

    Dim Report As New rptrecord

    Screen.MousePointer = vbHourglass
    cr.ReportSource = Report

    If frmsearch.optdate.Value = True Then

        Report.RecordSelectionFormula = _
                    "{record.crdate} >= Date(" & _
                    Format(frmsearch.dtfrom.Value, "yyyy,mm,dd") & _
                    " AND {record.crdate} <= Date(" & _
                    Format(frmsearch.dtto.Value, "yyyy,mm,dd")

    Else

        If frmsearch.optletter.Value = True _
       And frmsearch.txtltrno.Text <> "" Then
            Report.RecordSelectionFormula = _
                    "{record.Letter_No}='" & _
                    frmsearch.txtltrno.Text & "'"

            cr.ViewReport
            Screen.MousePointer = vbDefault

        End If
    End If

End Sub
Which line has the syntax error?

Also, your setting of the mouse back to a pointer only takes place if
the inner If statement runs—you probably want to put that just before the End Sub, so that the mouse pointer is always retored...

You could make your code more terse with a "with"::
Code:
Private Sub Form_Load()

    Dim Report As New rptrecord

    Screen.MousePointer = vbHourglass
    cr.ReportSource = Report

    With frmsearch
        If .optdate.Value = True Then

            Report.RecordSelectionFormula = _
                        "{record.crdate} >= Date(" & _
                        Format(.dtfrom.Value, "yyyy,mm,dd") & _
                        " AND {record.crdate} <= Date(" & _
                        Format(.dtto.Value, "yyyy,mm,dd")

        Else

            If .optletter.Value = True _
           And .txtltrno.Text <> "" Then
                Report.RecordSelectionFormula = _
                        "{record.Letter_No}='" & _
                        .txtltrno.Text & "'"

                cr.ViewReport

            End If
        End If
    End With

    Screen.MousePointer = vbDefault

End Sub
 
Old February 26th, 2008, 03:11 AM
Authorized User
 
Join Date: Jul 2006
Location: dera ismail khan, nwfp, Pakistan.
Posts: 43
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to naveed77 Send a message via Yahoo to naveed77
Default

thnq dear brianwren

i pasted ur code n den run but error i.e " The ) is missing "
highligting the following lines.

Report.RecordSelectionFormula = _
                        "{record.crdate} >= Date(" & _
                        Format(.dtfrom.Value, "yyyy,mm,dd") & _
                        " AND {record.crdate} <= Date(" & _
                        Format(.dtto.Value, "yyyy,mm,dd")


plz do something urgently
thnq
 
Old February 26th, 2008, 02:29 PM
Friend of Wrox
 
Join Date: Nov 2004
Location: Port Orchard, WA, USA.
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts
Default

If you start your listing with [ c o d e ] (but without the spaces), the posting will switch to fixed spacing, literal mode. Then if you indent, the indenting will be preserved, and so on. Finish with [ / c o d e ] (but without the spaces), to end the fixed-pitch font. You can toggle back and forth by alternating the [] and [/] tags. (As I said before...)

The Date() function has no closing ).

But I wasn't suggesting code (I just posted [u]your</u> code, reformatted).

So fix that missing ), and answer my initial question: Which line has the syntax error?

The following code [u]of yours</u> is missing 2 ")"s (as shown with bold-face, red additions:
Code:
    Report.RecordSelectionFormula = "{record.crdate} >= Date(" & _
    Format(frmsearch.dtfrom.Value, "yyyy,mm,dd") & _
    ") AND {record.crdate} <= Date(" & _
    Format(frmsearch.dtto.Value, "yyyy,mm,dd")" & _
    ")"
 
Old March 4th, 2008, 12:11 AM
Authorized User
 
Join Date: Jul 2006
Location: dera ismail khan, nwfp, Pakistan.
Posts: 43
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to naveed77 Send a message via Yahoo to naveed77
Default

dear brianwarn,

on the following lines i get error of ")" missing.


Report.RecordSelectionFormula = _
                        "{record.crdate} >= Date(" & _
                        Format(.dtfrom.Value, "yyyy,mm,dd") & _
                        " AND {record.crdate} <= Date(" & _
                        Format(.dtto.Value, "yyyy,mm,dd")

vb gives error on the above codeing but its arrow indicates last line
so how can i get if error free please.

thanq v.much for support
 
Old March 4th, 2008, 05:43 PM
Friend of Wrox
 
Join Date: Nov 2004
Location: Port Orchard, WA, USA.
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts
Default

(As I said before...) Will you [u]PLEASE</u> wrap your code posting in code tags? I have sqaid that in every post to you so far, and you are ignoring me. Be considerate. Those trying to help you need to read what is on the screen, and fixed-pitch code is much easier to read.(As I said before... And as I am doing for you)
Code:
Report.RecordSelectionFormula = _
                        "{record.crdate} >= Date(" & _
                        Format(.dtfrom.Value, "yyyy,mm,dd") & _
                        ")" & _
                        " AND {record.crdate} <= Date(" & _
                        Format(.dtto.Value, "yyyy,mm,dd") & ")"
Here's a way to make it easier to see what is what:
Code:
    Report.RecordSelectionFormula = _
        "{record.crdate} >= Date(" & _
                                   Format(.dtfrom.Value, "yyyy,mm,dd") & _
                               ")" & _
        " AND " & _
        "{record.crdate} <= Date(" & _
                                   Format(.dtto.Value, "yyyy,mm,dd") & _ 
                               ")"
                               This way, by lining up the opening and closing parens for the Date function, you can see right away if there is a mismatch. You can go even further, like:
Code:
    Report.RecordSelectionFormula = _
Code:
        "{record.crdate} >= Date(" & _
                                   Format(.dtfrom.Value, "yyyy,mm,dd" _
                                         ) & _
                               ")" & _
        " AND " & _
        "{record.crdate} <= Date(" & _
                                   Format(.dtto.Value, "yyyy,mm,dd" _
                                         ) & _ 
                               ")"
                               (By the way, I could not [u]show</u> you this formatting if I didn't use code tags around the code...)




Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem in Date Format in query imagetvr Pro VB 6 1 February 4th, 2008 06:03 PM
problem with the parameter date in a mdx query olmouy SQL Language 0 May 30th, 2007 05:32 AM
Help! Date query Problem yikchin Access 3 November 22nd, 2005 05:39 PM
Date Query Problem hugh@kmcnetwork.com SQL Server 2000 2 March 18th, 2004 04:31 PM
Convert String Date to Date for a SQL Query tdaustin Classic ASP Basics 4 July 7th, 2003 06:01 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.