Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
 
  HOME    TRACKER    Visual Basic




Crystal - Pick Record With Most Recent Date


I have designed a crystal report that reads data from MS Access via ODBC. The tables I'm concerned with are
1. Customer Table - CustCode, Name, Address, etc ...
2. Sales Header Table - SaleCode, CustCode, DateOfSale, etc ....

I need a report to show the Customer Name, Address and the date of the most recent purchase made.

I have tried creating a formula Maximum (DateOfSale) and placing it beside the customer name but this picks up the maximum date through out rather than the maximum date for a customer.

Can anyone help?




View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Select Record With Most Recent Date
Hi. I have an Access query that selects the top 4 records per lane (origin and destination) based on the lowest to highest rate (dollar) total. For example, if I have the following records in a table:

ORIGIN DESTINATION RATE CARRIER EFFECT DATE
Atlanta Cleveland $250 ABC 1/11/03
Atlanta Cleveland $300 123 1/11/03
Atlanta Cleveland $350 XYZ 1/11/03
Atlanta Cleveland $400 456 1/11/03
Atlanta Cleveland $500 456 8/30/03
Atlanta Cleveland $600 DDD 1/11/03
Atlanta Boston $315 ABC 1/11/03
Atlanta Boston $385 123 1/11/03
Atlanta Boston $420 456 1/11/03
Atlanta Boston $505 XYZ 1/11/03
Atlanta Boston $625 XYZ 9/15/03

My SQL for is:

Code:
SELECT q.ORIGIN, q.DESTINATION, q.CARRIER, q.RATE, q.[EFFECT DATE]
FROM TL AS q
GROUP BY q.ORIGIN, q.DESTINATION, q.CARRIER, q.RATE
HAVING (((q.RATE) In (Select Top 4 [RATE] From TL Where
[ORIGIN]=[q].[ORIGIN] AND [DESTINATION]=[q].[DESTINATION]Order By [RATE])))
ORDER BY q.[ORIGIN], q.[DESTINATION], q.[RATE], q.[EFFECT DATE] DESC;
In this case the query will return 8 records. The 4 records with rates of $250, $300, $350 and $400 for Atlanta to Cleveland and 4 records with rates of $315, $385, $420 and $505 for Atlanta to Boston.

However, now I'd like the query to return the top 4 rates per lane and carrier based on the later effect date. So, Atlanta to Cleveland would return records with rates of $250, $300, $350 and $500 because carrier 456 has 2 records but the $500 rate has a more recent effect date than the $400 rate. And Atlanta to Boston would return $315, $385, $420 and $625

Will all that said, can anybody help me with this? Thanks in advance

Go To The Most Recent Record
Using the following code:
Code:
If rsDelHdr.RecordCount > 0 Then ' See note
rsDelDets.Filter = ""
rsDelDets.Filter = "DelNumber = " & rsDelHdr!DelNumber
If rsDelDets.RecordCount > 0 Then
For i = 0 To rsDelDets.RecordCount - 1
.TextMatrix(i, 6) = rsDelDets!Delivered
.TextMatrix(i, 7) = JobBag.grd.TextMatrix(i + 1, 5) - grdDel.TextMatrix(i, 6)
rsDelDets.MoveNext
Next
End If
End If

Note:
If rsDelHdr.RecordCount is greater than 1 then I want to use the most recent record, i.e. the greater rsDelHdr!DelNumber. BUt how do I do this?

Filter Most Recent Record
Hello again,
In Access reports, I have a child report that has a date on it. And instead of viewing all the records (continuous), I would like to capture the most recent record. (I wish the reports has the ability to turn off continuous view in reports like child forms). I've tried max([date]) in the expression builder, but that doesn't appear to work. Any ideas?

Christian

Date/Time Pick
I am using the DT Picker. I have the date ones setup correctly, and know how to setup the Time one. When I get data from the Date Pickers it works using DTPicker2.Value, but when I use the same code on the DT Time, I get both date and time. All I want is the time.

Most Recent Date
How would I select a record with the most recent date,
regardless if the value is before or after the current date?

How Do I Display Date AND Time To Pick?
Hi there,
I am trying to display a control where the user can pick both date AND time.
How can I get this done?
Thanks
vbee

Checking For Most Recent Date In Array?
This is my situation. I have an array that gets populated with dates from a text file, in the format 22-Sep-02

How can I compare all of the values in the array to extract the most recent date?

The only thing I can really think of right now is something like this

is index 0 > index 1
if yes then is index0 > index2
if yes then is index0 > index3
if no then is index3 > index4

this isnt the most effective way, i dont think

also, i need to do the same thing for times (havent chosen the format yet but probably hh:mm:ss 24h time)

what direction might i go?

Select Most Recent Date Statement?
I want to select customer information from a table based on the most recent date...i.e there is customer info such as balance based on customer number in the table but the customer's balance is updated often so therefore there are a number of records based on a customer number and its balance. The newest balance being that with the most recent record but when i select this information and bring it back to a datagrid it brings back all records. I just want the most recent one for that customer nmber. I have tried usin MAX(date) but this doesnt work for some reason. Any ideas, Please help. important project

Finding Most Recent Date In Access 97
Good Morning Everyone!

Here is my problem. I have a table of PO's that have been received. It is cumulative from about 1995 or so. As you can imagine, the same part number has been received numerous times since then. I need to query the table for the most recent date that the part was received and get the price for that date. I tried to do a query using the Group By, changing the date to Max, but I still get each instance of the part, not just the most recent. Is there a VB function that I can call for this?

Thanks in advance!

Select Most Recent Date In Database File
I have a table in Access that shows dates when a change has been made for an entry (see partial list below - there is more data associated with each row). From VB, I want to select data in a row associated with the most recent date. How can I identify which date is the most recent?

Code:
Grade IDDate approved
69694204050HI25-Feb-03
69694204050HI16-Aug-02
69694204050HI17-Dec-01
69684208100HI01-Sep-03
69684208100HI25-Feb-03
69684208100HI16-Aug-02
69684206800HI29-Aug-95
69684206750HI01-Sep-03
69684206750HI25-Feb-03
69684206750HI16-Aug-02

Every Record Has A Date Field. I Want To Find A Date And From This Date, I Want To Read Records To E
Every record has a date field. I want to find a date and from this date, I want to read records to eof. What is the code? - Thanks

Find Record With Date Closest To Date Entered
I have a table in Access with weekly prices with Monday's dates.

I have a user entering invoices in a VB form, and when the user clicks Calculate I need my application to find the previous week's price for the invoice date entered.

Also, I have this in a function for my form called Private Function Calculations(). Would that work okay or should I put it in a module? Do I need a class? I am still learning.

thanks.



What To Pick What To Pick :confused:
Can anyone give reasons why one should pick either an:
out of process activeX exe,
or in process activeX dll?

Is there any good resources also that you may have run across on the web?

Get The Record Of Current Date!!!
Table Name = Calendar

CalendarDate Year Workweek Workday
............... ....... ...... ......
30/12/2004 2004 12 4
31/12/2004 2004 12 5
01/01/2005 2005 12 6
02/01/2005 2005 1 0
03/01/2005 2005 1 1
04/01/2005 2005 1 2
05/01/2005 2005 1 3
06/01/2005 2005 1 4
07/01/2005 2005 1 5
08/01/2005 2005 1 6
09/01/2005 2005 2 0
10/01/2005 2005 2 1
11/01/2005 2005 2 2
12/01/2005 2005 2 3
13/01/2005 2005 2 4
14/01/2005 2005 2 5
and so on......
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''
Public Function myFunction ()
Dim rsCalendar As Recordset

Set rsCalendar = CurrentDb.OpenRecordset ("Calendar" , db_open_dynaset)

With rsCalendar

End With

rsCalendar.Close

End Function

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''

I want to get the record of Date() which point to Today date . I want to get the row of record of the Maximum value in "Workweek" which is the last Workweek value. I want to move to the next 20 record. If EOF then take the last record based on the Workweek. If not EOF then get the current record. What should i do?
Thanks in advance


Regards,
bananatree

Inserting Date Into Record Set
I'm using Access 2000....


Code:
Dim dbs As Database
rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("MyTable")

rst.Fields("MyField") = "X"
How come this code doesn't let me insert "X" into the MyField field?

Can't Add Date Record To Table
Hi,

I have a form which is adding a number and two date fields to a db table called Bookings.

Problem is I get a 3421 run-time error which is a data type conversion error. This is happening on line 2 in my code (shown below).

My date format i am trying to add is DD/MM/YYYY and my input mask in Access is set as Short Date; 00/00/0000;0;_

So why I am still getting an error??

Private Sub cmdAddBooking_Click()
frmBookPort.Data1.Recordset.AddNew
frmBookPort.Data1.Recordset.Fields("Booking Reference") = frmBookPort.txtRef.Text
frmBookPort.Data1.Recordset.Fields("Arrival Date") = frmBookPort.txtStart.Text
frmBookPort.Data1.Recordset.Fields("Departure Date") = frmBookPort.txtEnd.Text
If ((txtRef.Text <> "") And (txtStart.Text <> "") And (txtEnd.Text <> "")) Then
frmBookPort.Data1.Recordset.Update
MsgBox ("This entry was sucessfully updated")
Else: MsgBox ("You must enter some data!")
txtRef.Text = ""
txtStart.Text = ""
txtEnd.Text = ""
End If
End Sub

Geting Date From Record
Hi,

I Really need to get the code to do this..

1. Open C:ClientAlertClients.mdb
2. Find out how many rows there are in a DB Field
3. ReDim Array to the records in the field
4. Copy ALL Records from one field to a array

can someone help me out????

Thankyou...

Add Months To Date Record
Hi,

In VB I extract customers due dates from a table of records into a temp
tabel.
Now I want to know which customers payment is due in 3 Months from the
current date.

Is it possible to put this in a Access query ie. get customers whose payment
is due in 3 months time.

Regards,
Feroz

HELP ME IN SELETING RECORD BETWEEN TWO DATE
plz help me.....seleting record between two date......


sqlQry = "select * from Invoice where & ucase(Inv_Company_Name)='" & UCase(cmb_Company.Text) & "'" & "BETWWEN val((Inv_Date>=Format('" & dtp_Fdate.Value & "', 'DD/MM/YYYY') and Inv_Date<=Format('" & dtp_Tdate.Value & "', 'DD/MM/YYYY'))'"""
rs.Open sqlQry, cConnection, adOpenKeyset, adLockOptimistic

i used this code but its not working ....plz help me

PLZ HELP ANY ONE SELETING RECORD BETWEEN TWO DATE
plz help me any one....i m having problem to seleting record between two date........

i have used this code but not working plz any one help me..

sqlQry = "select * from Invoice where & ucase(Inv_Company_Name)='" & UCase(cmb_Company.Text) & "'" & "BETWWEN val((Inv_Date>=Format('" & dtp_Fdate.Value & "', 'DD/MM/YYYY') and Inv_Date<=Format('" & dtp_Tdate.Value & "', 'DD/MM/YYYY'))'"""
rs.Open sqlQry, cConnection, adOpenKeyset, adLockOptimistic

How To Search Record By Date
i have access database but i have problem to search record by date.
example i want to seach people who born in 01 january 1974 to 01 September 1974. i use dtpicker to define date please help me

Deleting A Record With Date In WHERE Clause
Hi,
I'm trying to delete records from an access database, via excel VB code.
the code I have at current is:

Code:
Sub del_today()

Dim tday As String
tday = range("B1").value

Dim cn As ADODB.Connection, sql As String, r As Long
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=K:filepathDBAsIOS.mdb;"

sql = "DELETE * from test WHERE [thedate]=" & tday & """

cn.Execute sql
cn.Close

End Sub

when I run this, no records are deleted. I've tryed deleting based on other columns, like

Code:
sql = "DELETE * from test WHERE [1]=aaa"
and it works just fine.
I suspect the reason it's not working has to do with the colunm format, which is date/time, rather then text (as column "1" is)

Please help me here...

-Ethan

Counting Record With Date Field
Hello,
I am trying to return the number of records that match a given year.
User enters year in input box and then I should be able to return number of records that match that year but I cannot seem to get the syntax right.
I keep getting systax error or No value for parameter
So far I have this and it may all be wrong. I believe my problem lies in the fact
that respon is a string and cpltDate is a date but not sure how to correct it.
If anyone has a better solution or can help me fix this I would appreciate it.

Dim db As Connection
Set db = New Connection
db.CursorLocation = adUseClient
db.Open "PROVIDER=MSDataShape;Data PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=R:RoadDepartment.mdb;"

Dim rsTemp As ADODB.Recordset
Set rsTemp = New ADODB.Recordset
Dim respon As String
Dim reccount As String
respon = InputBox("Enter Year")

With rsTemp
.CursorLocation = adUseClient
.Open "Select COUNT(tblcomplaint.cpltDate)as Result from tblComplaint where cpltDate = respon " , db, adOpenStatic,adLockReadOnly,adCmdText
reccount = rsTemp.Fields("Result").Value
Debug.Print reccount
.Close
End With

Thanks,
Alexus

Thanks for the help Pasvorto. That solved the problem. Sorry about code tag
will remember next time

About Date Record Is 1 Week Older.
Hi,

How can i delete a record using timer in a specific date like a record is 1 week older.

ex:

delete from table where date=""

Can any one help me.

Populating Record Set Using Date Range
Dear All,

I have a SQL statement like this:

SQL_ACTY = "SELECT * FROM S_EVT_ACT AS ACT " & _
'"WHERE (ACT.CREATED>='" & CDate(Fromdate) & "') ANDACT.CREATED<='" & CDate(Todate) & "'".

FromDate and Todate are obtaining from a (access) Table which is in theformat DD-MM-YY.



In SQL server date is in the format Created= 2005-12-17 08:26:12.000which is Year-Month-Date (YYYY-MM-DD) format.

Using this above condition the record set always showing empty eventhere are many records. I tried several ways but not able to obtaincorrect results by trying the format statement etc. Is any one has anyidea what I am doing wrong here or the correct syntax using date rangevalues, how I can populate recordset?

I know this is simple, but simply I am not understanding what I am doingwrong here?

Waiting for your reply.

Regards
Arun

HELP ME ARGENT !!! SELECTING RECORD BETWEEN TWO DATE...
hi ...i have did this code but its not working.....i don't know where it is problem.....its telling that missing oprator(syntex error) plz help me......its argent...!!!!


If cmb_Company.CausesValidation = True Then
Set rs = New ADODB.Recordset


sqlQry = "select * from Invoice where & ucase(Inv_Company_Name)='" & UCase(cmb_Company.Text) & "' AND BETWEEN (Inv_Date>=Format('" & dtp_Fdate.Value & "', 'DD/MM/YYYY') Inv_Date<=Format('" & dtp_Tdate.Value & "', 'DD/MM/YYYY'))'"""
rs.Open sqlQry, cConnection, adOpenKeyset, adLockOptimistic




 

PROBLEM FOR SELECTING RECORD BETWEEN TWO DATE....PLZ HELP ME
b




hello any one can help me.....i got the problem for seleting between two date......

sqlQry = "select * from Invoice where & ucase(Inv_Company_Name)='" & UCase(cmb_Company.Text) & "'" & "BETWWEN val((Inv_Date>=Format('" & dtp_Fdate.Value & "', 'DD/MM/YYYY') and Inv_Date<=Format('" & dtp_Tdate.Value & "', 'DD/MM/YYYY'))'"""
rs.Open sqlQry, cConnection, adOpenKeyset, adLockOptimistic



any one can help me.....plz

Crystal Report Record Selection
Hi there,

I am using VB6.0. My version of Crystal Report is 8.5. I have developed a label print report in CR first which had some prompt variable for user selection(Area). The report working fine stand alone in CR.
I had excluded the prompt before taking it in VB. Added the SQL selectin to limit the record selection. But it's bypassing the condition and selecting all the record. I can see the value of gsDivision is available.

Any help regarding this will be highly appreciated.

SQL Syntext in form :

rs.Open "SELECT * FROM tblLabel WHERE Division = " & "'" & gsDivision & "'", "ASLBLDEV"
Report.Database.SetDataSource rs


Regards.

Jay

Crystal Reports 10 + VB6 --&gt; Invalid TLV Record
Hi everybody!

I'm converting a bunch of 8.5 Crystal Report's files to the 10 version.
After doing all the VB's code modifications, I tried to put it running under a Windows XP OS without Crystal Reports X Installed.

I got two sequentials errors that forced my app to close:

"Failed to open document" and the hellish "Invalid TLV Record".

I've spent 2 days on the web searching for answers but as I get more info, more lost I become. (lol)

I got the RDC 10 Deployment Documentation and tried all the examples for an client setup, using Visual Studio Installer, Package Wizard and Visual Basic Installer but none of them worked fine.

I tried a more simple solution, just unregistering the old DLL's, replacing them by the newest ones and then registering then again, but most part of them didn't register due to OS use.

I've tracked down the dependency files for each one of the CR10 DLL's that are needed to be registered :
craxdrt.dll
craxddrt.dll
crviewer.dll
CRDesignerCtrl.DLL
cdo32.dll

But then I've lost myself.
I need to convert these 8.5 RPT's to version 10 as fast as I can and I'm out of ideas.

Can anyone (pleeeeeeeeeeease) tell me what I have to do to make a "client install" of crystal reports 10 so the app can work?

I'm really tired of trying and getting no solution. I'm almost in the mouth of despair... lol.

Thanks a Lot!!!

Crystal Report - Record Count
Hi, anybody know how to make crystal report show no.of record that I want. Eg. I want show 20 records in my report. So can you tell me how to do it if you know the solution. Thanks you.

Crystal Record Selection Problem
hi all, i'm doing record selection on 3 variables report-wide thusly:


Code:
({@RECEIVED} in {?Start Date} to {?Ending Date}) or
({@RETURNED} in {?Start Date} to {?Ending Date}) or
({@SERVED} in {?Start Date} to {?Ending Date})
it seems that crystal doesn't like having 2 OR operators...if i remove one, they work properly but with all, it's treating one as an AND (rather served within a date range, it's showing only those served and received in that range.)

the first 2 groups are fine but it's just that last one that gets treated as an AND...what am i missing? am i retarded? help me mommy!!

I Want A Specific Record In My Crystal Report
hello Friends.

any body knows how to retrive a selected data in your report.i m using crystal reports 9 this is a db of a college i want to give a roll no at runtime . and report open with data against that roll no .

help me plz

Current Record : Crystal Reports
Hello friends

I have Crystal Reports 8.0 installed and i use Crystal Report Viewer (CR Viewer)
control to view reports.

Assume one situation where report is grouped (or indexed) with a field 'InvoiceNo'.
'InvoiceNo' is a primery key in the table on which the report is built. Clicking
NextPage button will display next record, clicking PreviousPage will display previous
record.

Now how do i come to know which record i am viewing. I mean to say, which 'InvoiceNo' i am viewing.

Any help will be greatly appreciated.


- Deepak

Record Seletion In Crystal Reports
Hello:

I want to thank all that reply.

I have created a report using Crystal Report Version 7. What I want to do is allow the end user to select from several drop down list and build an SQL statement that will select the records that my report can use. Like I said I have already created this report, and I know how to create an SQL statement using the above method. What I don't know is how to pass the SQL statement to my report and make it generate the report from the SQL statement.

I hope this makes sense.

Thank again.

Crystal Report Of A Selected Record
Dear Friends,

Long time no see. I have a q in hand and wish to seek yr advice. I have a VB program that has an ADO control containing the client ID on a form. When the user presses a "print" button, a report of the details (like name, age, sex, address, etc) of the client with an ID currently shown in the ADO control.

How can I control the crystal report control to report only that client's information? How should the report file be designed?

Thanks. I appreciate any coding / advice.

Yours truly,
Cantene

Move Next Record In Crystal Reports
Hi,
I'm using crystal reports 8.5 and Here is my question.

I need to create a formula fields which looks like this "Dates going to get approval are 1/2/04, 1/5/04,1/6/04 and 1/7/04" I'm picking these dates from Database.

My problem is I can able to get the fist record. How do move to next records. Is there any specifiec way to do this.

Someone please help me.

Thank you.

Regards
Rechen

Crystal Report View All Record !
I have create a project in Visual Basic 6,
and i have insert in a form the object CRViewer1.

The code under this form is this:

'----------
Private Sub Form_Load()
 Set Appl = New Application 'Creates a new Application object.
 Set report = Appl.OpenReport(App.Path & "
eportsstte.Rpt") 'Open the Events.rpt file to preview
 Set CrSec = report.Sections("PH") 'Set the Section object to the Page Header.

 Dim rst As ADODB.Recordset
 Set rst = New ADODB.Recordset
 rst.Open "SELECT lam_stte.unico , lam_stte.numdoc, lam_stte.dtadoc, lam_stte.centlav " & _
          "FROM lam_stte " & _
          "WHERE lam_stte.numdoc=404056 " & _
          "ORDER BY lam_stte.numdoc Asc", cnnODBC, , , adCmdText

 report.DiscardSavedData
 report.Database.SetDataSource rst
  
 CRViewer1.ReportSource = report 'Sets the Report source of the CrViewer to the Report object we created.
 CRViewer1.ViewReport 'Views the Report
 CRViewer1.Zoom 75
End Sub
'----------

WHEN I SHOW THE FORM, THE REPORT WILL APPEARE WITH ALL THE DATA.

HOW CAN I PROCEED TO SEE ONLY ONE RECORD?

HOW CAN I SOLVE THE PROBLEM ?


-- Matteo Tosi --

Invalid TLV Record- Crystal Report + VB
Q. : When i am trying to execute (using CRViewer) the following code, i am getting an error message :

    Error: Invalid TLV Record.

what does it mean? But when i execute the same report (john.rpt) from CrystalReport1.PrintReport, it is showing perfectly.

The Code :
        Dim myApp As New CRAXDRT.Application
        Dim myRpt As New CRAXDRT.Report

        Private Sub Form_Load()

            mpath = "c:john.rpt"
            Set myRpt = myApp.OpenReport(mpath, OpenMethod)
            CRViewer1.ReportSource = myRpt
            CRViewer1.ViewReport

        End Sub

Extract Just The Month Or Year From A Record With Date 4/5/00
Can anyone help me with the code using VB6 and using SQL code to:

the user selects from a drop down box the month from one drop down box and the year from another drop down box

cboMonth
cboYear

cboMonth leaves you with text, example "January". I think I would then need to turn this into "1" somehow. the Year is formatted as 2000 but I need it to query the database as "00"

How do I take what the user selects in a form and query the database date for just the month and year; doing nothing with the day.

The report for example is run once a month. If they select March. They need to get a count of how many records were inputted for the month of March. I use the field Date_Opened to query this as it is a required field. So when they select March for the month and 2000 for the year or 00 I need it to go to the database select Date_Opened and look for all records beginning in that field with "3" and then query the year portion and find only records that are from the cboYear field. So although I have a day, I need to do nothing with that field.

Anybody have any good ideas on how I could do this with code or the SQL code to do this,

thanks in advance

VB Crash When Date Field Is Deleted From Record?
I'm currently working on a database using a VB application. I have many fields on a form, but the one thing that screws me up is the date fields. When I add a record and I don't enter dates, everything is fine. When I edit a record and insert a date, everything is fine. But when I delete a date and save it, the program crashes when I move to the next record. For
some reason it doesn't want to delete a date, and after it crashed, the edit of that record was cancel, so it returned to the old values.

BTW - am using Access database.

Cheers,
Gerbill

Filter Record On Sql Db Based On Date Variable
I have a form in vb on which when i send query to sql db based on date variable like
Set rst4 = New Recordset
rst4.Open " Select * from msdmaster where msddate>=convert(datetime,'"& m_startdate & "',103)and msddate <= convert(datetime,'"& m_enddate & "',103)order by msdno ", CON, adOpenForwardOnly, adLockReadOnly
it runs ok on the server machine . but when runs on client it gives error message "date value out of range unable to convert"

HELP ME SELETING RECORD BETWEEN TWO DATE ..i Have Given What I Did Full Code...
HI.....i got problem for Seleting record Between Two date.......i did like this ..but its not working...its telling that Syntex error(missing oprator)....any one can help me.....here i have given full code what i did.....and also i want to insert all the record in to another table.......any one can help me......(with MSFLEXGRID data).....plz help me....


Code:

Option Explicit
Dim rscmbLoad As ADODB.Recordset
Dim rs As ADODB.Recordset
Dim c As Integer
Dim r As Integer
Dim orno As String

Dim SalesTotal As Double
Dim SalesNetAmount As Double
Dim SalesDiscount As Double
Dim I As Integer


Private Sub cmdShow_Click()

If cmb_Company.CausesValidation = True Then
Set rs = New ADODB.Recordset

orno = cmb_Company.Text

sqlQry = "select * from Invoice where Inv_Company_Name='" & (cmb_Company.Text) & "'And Between (Inv_Date>=Format('" & dtp_Fdate.Value & "', 'DD/MM/YYYY') Inv_Date<=Format('" & dtp_Tdate.Value & "', 'DD/MM/YYYY'))'"

rs.Open sqlQry, cConnection, adOpenKeyset, adLockOptimistic





 FlxGrdDemo.Rows = 1
    FlxGrdDemo.Cols = rs.Fields.Count
    
    ReDim col_wid(0 To rs.Fields.Count - 1)
    For c = 0 To rs.Fields.Count - 1
        FlxGrdDemo.TextMatrix(0, c) = rs.Fields(c).Name
        col_wid(c) = TextWidth(rs.Fields(c).Name)
    Next c
    
    
    
    SalesTotal = 0

FlxGrdDemo.Rows = 1

With FlxGrdDemo
If Not (rs.BOF And rs.EOF) Then
            .Rows = 1
            Do Until (rs.EOF)
                .AddItem rs("Invoice_No") & Chr(9) _
                        & rs("Inv_Date") & Chr(9) _
                        & rs("Inv_Do_Number") & Chr(9) _
                        & rs("Inv_Vehicle_Number") & Chr(9) _
                        & rs("Inv_Company_Name") & Chr(9) _
                        & rs("Inv_Place") & Chr(9) _
                        & rs("Inv_Charges") & Chr(9) _
                        & rs("Inv_Others") & Chr(9) _
                        & rs("Inv_Others_Charges") & Chr(9) _
                        & rs("Inv_Quantity") & Chr(9) _
                        & rs("Inv_PerPrice") & Chr(9) _
                        & rs("Inv_Goods") & Chr(9) _
                        & Format(rs("Inv_Total_Amount")) & Chr(9)
                SalesTotal = SalesTotal + rs("Inv_Total_Amount")
                rs.MoveNext
                .ColAlignment(1) = flexAlignLeftCenter
                
                
            Loop
        End If
        
        End With
        End If
        
        
        
        
    
    ' Display the values for each row.
    r = 1
    Do While Not rs.EOF
        FlxGrdDemo.Rows = FlxGrdDemo.Rows + 1
        For c = 0 To rs.Fields.Count - 1
            FlxGrdDemo.TextMatrix(r, c) = _
                rs.Fields(c).Value


        Next c

        rs.MoveNext
        r = r + 1
    Loop

    ' Close the recordset and connection.
    rs.Close
 

    ' Set the column widths.
    For c = 0 To FlxGrdDemo.Cols - 1
        FlxGrdDemo.ColWidth(c) = col_wid(c) + 1000
        
        
        
        
        
        
        Next c
    Text1.Text = Format(SalesTotal, "0.00")

'End If
End Sub




Private Sub FlxGrdDemo_KeyPress(KeyAscii As Integer)


    Select Case KeyAscii
        Case vbKeyReturn
            ' When the user hits the return key
            ' this code'll move the next cell or row.
            With FlxGrdDemo
                If .Col + 1 <= .Cols - 1 Then
                    .Col = .Col + 1
                ElseIf .Row + 1 <= .Rows - 1 Then
                    .Row = .Row + 1
                    .Col = 0
                Else
                    .Row = 1
                    .Col = 0
                End If
            End With
        Case vbKeyBack
        ' Delete the previous character when the
        ' backspace key is used.
            With FlxGrdDemo
                If Trim(.Text) <> "" Then _
                    .Text = Mid(.Text, 1, Len(.Text) - 1)
            End With
        Case Is < 32
        ' Avoid unprintable characters.
        Case Else 'Else print everything
            With FlxGrdDemo
                .Text = .Text & Chr(KeyAscii)
            End With
    End Select

End Sub



Private Sub FlxGrdDemo_KeyUp(KeyCode As _
    Integer, Shift As Integer)


    Select Case KeyCode
        ' Copy
        Case vbKeyC And Shift = 2 ' Control + C
            Clipboard.Clear
            Clipboard.SetText FlxGrdDemo.Text
            KeyCode = 0
        ' Paste
        Case vbKeyV And Shift = 2 'Control + V
            FlxGrdDemo.Text = Clipboard.GetText
            KeyCode = 0
        ' Cut
        Case vbKeyX And Shift = 2 'Control + X
            Clipboard.Clear
            Clipboard.SetText FlxGrdDemo.Text
            FlxGrdDemo.Text = ""
            KeyCode = 0
        ' Delete
        Case vbKeyDelete
            FlxGrdDemo.Text = ""
    End Select
End Sub


Private Sub Form_Load()

With FlxGrdDemo
.ColWidth(0) = 1000
.ColWidth(1) = 1000
.ColWidth(2) = 1000
End With

'Call fillgrid


Set rscmbLoad = New ADODB.Recordset

sqlQry = "SELECT * FROM Invoice"
rscmbLoad.Open sqlQry, cConnection, adOpenKeyset, adLockOptimistic
cmb_Company.Clear
ComboFill cmb_Company, rscmbLoad, "Inv_Company_Name", "Invoice_No"


Inv_Bill_Date.Value = Format(Now, "DD/mm/yyyy")
dtp_Fdate.Value = Format(Now, "DD/mm/yyyy")
dtp_Tdate.Value = Format(Now, "DD/mm/yyyy")

End Sub


Public Sub fillgrid()


FlxGrdDemo.Row = 0
FlxGrdDemo.Col = 0
FlxGrdDemo.ColWidth(1) = 500
FlxGrdDemo.Text = "Invoice No"
FlxGrdDemo.Col = 1
FlxGrdDemo.Text = "Date"
FlxGrdDemo.Col = 2
FlxGrdDemo.Text = "Vehicle No"
FlxGrdDemo.Col = 3
FlxGrdDemo.Text = "Place "
FlxGrdDemo.Col = 4
FlxGrdDemo.Text = "Charge"
FlxGrdDemo.Col = 5
FlxGrdDemo.Text = "Others"
FlxGrdDemo.Col = 6
FlxGrdDemo.Text = "O.Charges"
FlxGrdDemo.Col = 7
FlxGrdDemo.Text = "Quantity"
FlxGrdDemo.Col = 8
FlxGrdDemo.Text = "Unit Price"
FlxGrdDemo.Col = 9
FlxGrdDemo.Text = "Goods Type"
FlxGrdDemo.Col = 10
FlxGrdDemo.Text = "Total Amount"

End Sub

Private Sub Text2_Change()


Text3.Text = (Val(Text1.Text)) - ((Val(Text2.Text) * Val(Text1.Text)) / 100)

End Sub










plz help me.....

PROBLEM IN DATAREPORT(how To Print All Record In A Certain Date?)
hi,

    now, i'm using datareport and its working fine by using this query:

Code:ssql = "Select * From Sales where date between '" & datefrom & '" and '" & dateto & "'"

this query is very usable if i just want the daily report. Let say i have "datefrom" is "1/1/2005" and "dateto" is "1/31/2005", how can i print this in datareport per daily record and it will total all my sales per daily. I mean to say is in just 1 click the datareport will generate report per daily not summing all records between datefrom and dateto.

thanks in advance,


dcarreon

Access Record Date/time Stamp
Hi,
Is there a way (without adding a field, that's to easy) to check the last time a SPECIFIC record from an access Table or Dynaset was modified ?
I'm sure access keeps that info somewhere internally, so how to get it out ?
Thanks,
Steven

Draw Line Every 3rd Record In Crystal Report
I have a report that displays thousands of records and uses Arial Narrow 8. My boss requested that every after 3 or 5 records a line will be drawn under to give a separator in between group of 3 or 5 records. I am using Crystal Report 8.5?

How To Limit A Record In Each Page Of Crystal Report
Hello Everyone! I have an inventory program that could print records through crystal report. What I would like to do is limit each page with a particular number of records and then it will continue to the next page of crystal report. Is there a way to do this. Much Thanks for those who could give me a tip and examples on how to do it. Thanks and God Bless.

Crystal Report - Find Last Record In A Page
How do I find a last record in a page

I want to suppress a section in all places except last record in a page and last record in the same report. (ie. I will be showing double line in the last record of all pages). Other records will display single line.

How do I do that? Any other suggestions also welcome.

Thanks

Change Record Sorted In Crystal Report
Hay
If i have table and i i want view records in crystal report it's comin in each record comes in new line like
Mike
Jack
Bon

but i want it comes in one line and when it reach end of the line go to new line like
Mike Jack Bon ....

How can i do that ?

Passing Record Sets To Crystal Reports 4.0
Hey,
This program i've been working on is still being a pain.  What i hate most is how the SQL syntax is different for VB and CR.

How would I go about setting a recordset in VB and passing that recordset to CR4.0?  Thus making the report generated display only the information in the recordset.

Thanks

Copyright 2005-08 www.BigResource.com, All rights reserved