Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How do I select a register between dates in crystal Report

Posted on 1999-08-02
4
Medium Priority
?
1,179 Views
Last Modified: 2013-12-25
I need to find a register or do a report between dates.ei:
from date1 to date2.. all the register among this dates. what formula do i need or put in the SelectionFormula en crystal report in visual basic 5
0
Comment
Question by:sllanes
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 4

Accepted Solution

by:
CraigLazar earned 300 total points
ID: 1507413
Hi r u looking for a select statement to populate a recoprdset with records between 2 dates ?
then display them in Crystal reports.

ok this i used in a project were the user entered in 2 dates and then i displayed the records in a report. I Created a recordset then copied those records to a temptable and then showed the report using crystal 6 (Free from this site www.seagatesoftware.com) and displayed the report. worked well
here is the code

On Error GoTo eh:
Dim db As Database
Dim rsInvoices As Recordset
Dim rsWrite As Recordset
Dim sqldatecheck As String
Dim message, message1, title, myvalue, MYVALUE2 As String
Dim StartDate, EndDATE As Date
Dim rf1, rf2, rf3, rf4, rf5 As Field

Set db = OpenDatabase("C:\tuscany\tuscany.mdb")
    'These 2 Input boxes fetch the 2 report dates needed to generate records for the SummaryReport table
    message = "Please Enter The Start Date  in this format DD\MM\YY "
    title = "Date Range"
    myvalue = InputBox(message, title)
    If myvalue = "" Then
       Exit Sub
    End If
    StartDate = CDate(myvalue)
    message1 = "Please Enter The End Date  in this format DD\MM\YY "
    title = "Date Range"
    MYVALUE2 = InputBox(message1, title)
    If MYVALUE2 = "" Then
       Exit Sub
    End If
    EndDATE = CDate(MYVALUE2)

    'Deleting existing records in the Summary Report table before new report is created
    Screen.MousePointer = vbHourglass
    db.Execute "DELETE * FROM tempmonthinv"

    Screen.MousePointer = vbDefault
    '####################################################################################################

    sqldatecheck = "Select * from LTInvoice where Date1 >=  #" & StartDate & "# and Date1 <=#" & EndDATE & "#;"
    Set rsInvoices = db.OpenRecordset(sqldatecheck, dbOpenDynaset)
    Set rsWrite = db.OpenRecordset("Select * from TempmonthInv", dbOpenDynaset)
   
    rsInvoices.MoveFirst
    Set rf1 = rsInvoices("Company")
    Set rf2 = rsInvoices("Date1")
    Set rf3 = rsInvoices("SubTotal")
    Set rf4 = rsInvoices("VAT")
    Set rf5 = rsInvoices("MainTotal")
   
    Do Until rsInvoices.EOF
        rsWrite.AddNew
        rsWrite("Company") = rf1
        rsWrite("Date1") = rf2
        rsWrite("SubTotal") = rf3
        rsWrite("VAT") = rf4
        rsWrite("MainTotal") = rf5
        rsWrite.Update
        rsInvoices.MoveNext
    Loop
    rsInvoices.Close
    rsWrite.Close
    db.Close

Load frmMonthTurn
frmMonthTurn.Show

eh:
If Err.Number = 13 Then
    MsgBox "Please enter the Correct Starting and Ending Dates in this Format- DD/MM/YYYY", vbCritical
    Exit Sub
ElseIf Err.Number = 3021 Then
    MsgBox "Sorry there are NO Invoices for the Dtae you Have Entered", vbInformation
    Exit Sub
End If


hope this helps
:)
Craig
0
 

Author Comment

by:sllanes
ID: 1507414
i would like to know also if there is a way to pass variables to a sql statement as:
Select * from prueba where date between #month/day/year# and #month/day/year#
....
where month day and year coul be variables where a user can modefied from the aplications like inputboxes o a textbox..
and change the month and day and year,,, values..is it possible? is it possible, how is it?
0
 
LVL 4

Expert Comment

by:CraigLazar
ID: 1507415
hi
ok here is some sample sql strings that hold date and string values
SQL Strings used to set up record sets

1) Standard select statement bringing back one vale
   SQL = "select [Commission] from employee where name = '" & txtEngineer & "'  ;"

2) String to select 2 columns from 1 table and bring back values according to criteria
   SQL = "select [Commission],[HourlyRate] from employee where name =   '" & txtEngineer & "'  ;"

3) String used to return set of records according to 2 date values given
   SQL = "Select * from capture where DateCap >= #" & StartDate & "# and DateCap<=#" & EndDate & "#;"

hope it helps

Craig
0
 

Author Comment

by:sllanes
ID: 1507416
thanks craig it help a lot.. thanks work good.. but another thing.. have you get an error like this before.. after instaling the aplication...and when you click to get the report.. appears this::::
run time error 20504 report not found... i check the source and the code and all appears good.. what do you think?
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

661 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question