Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2003
    Posts
    43

    Unanswered: Report Query in VB code

    I know I can do this through a query and then make a report of it. But I would like to do it if possible through VBA coding.

    I have a table of given X contracts that when their ending date come with in 60 days of renewal I want them to show up on the report, and ONLY thoese ones. How would this be done through code though?

    I have tried it with a loop to go through all records in the table and set the visible properties, BUT it sets all of the companys visible properties, so if one of thoese records is below or equal to 60 it shows all companys.

    Private Sub Report_Open(Cancel As Integer)

    On Error GoTo BillyErrors:

    Dim NowDate As Date
    Dim DateEnd As Date
    Dim TimeLeft As Single
    Dim db As Database
    Dim Rec As Recordset
    Set db = CurrentDb()
    Set Rec = db.OpenRecordset("Contacts")


    Do While Not Rec.EOF

    NowDate = DateTime.Date
    DateEnd = Nz(Rec("End_Date"))
    TimeLeft = (DateEnd) - (NowDate)

    If TimeLeft <= 60 And TimeLeft > 0 Then

    Company.Visible = False

    Else

    Company.Visble = True

    End If

    Rec.MoveNext

    Loop


    BillyErrors:

    If Err Then
    MsgBox Err.Description, vbOKOnly, "Error"

    End If

    End Sub


    Thank you for your help.


    Billy

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Why don't you query for those contracts that are within 60 days of renewal? Something like:

    SELECT * FROM MyTableNameHere AS t1 WHERE (DateDiff("d",t1.RenewalDate,t1.ContractDate) < 60);

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Originally posted by M Owen
    Why don't you query for those contracts that are within 60 days of renewal? Something like:

    SELECT * FROM MyTableNameHere AS t1 WHERE (DateDiff("d",t1.RenewalDate,t1.ContractDate) < 60);
    That's a pretty good solution, but it requires a minor tweek. Datediff returns a negative when the first date evaluated is larger then the second, therefore all accounts that are passed the current date will also be returned as they would be negative. Also, it appears that he only has one date being returned from an actual datasource.

    Try:

    SELECT * FROM MyTableNameHere AS t1 WHERE (DateDiff("d",t1.RenewalDate, date()) < 60) AND (RenewalDate =< Date())

  4. #4
    Join Date
    Oct 2003
    Posts
    43

    Ok, now query problem

    I have the query in the Report for when it opens up but it keeps giving me a can not execute select query. See code below maybe I am writing this wrong.

    Sql = "SELECT * FROM Contacts As MeMyselfAndI WHERE (DateDiff('d','MeMyselfAndI(End_Date)','date(Now)' )<60)"
    db.Execute (Sql)

    Thank you,

    Billy

  5. #5
    Join Date
    Oct 2003
    Posts
    43

    Well got that part

    Well figured out the last part......just put the SQL query in the Row Source now I just have a bad query because the report is showing all data and not just the contracts that are coming with in 60 days see below.

    SELECT * FROM Contacts AS MeMyselfAndI WHERE (((DateDiff("d",MeMyselfandI.[End_Date],Now()))))<60

    Thanks,

    Billy

  6. #6
    Join Date
    Oct 2003
    Posts
    43

    Cool Thank you

    Thank you guys, I have figured out this problem on my own.


    Yos,

    Billy

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: Thank you

    Originally posted by bwood415
    Thank you guys, I have figured out this problem on my own.


    Yos,

    Billy
    And your solution???? It is only polite ... May be someone else down the line will be able to use it also ...

  8. #8
    Join Date
    Oct 2003
    Posts
    43

    Ah Yes Sorry

    Forgot to post the solution.

    In SQL view of the properties of the Report for the Row Source put the following code.

    SELECT DateDiff("d",[MeMyselfandI].[End_Date],Now()) AS Expr1, *
    FROM Contacts AS MeMyselfAndI
    WHERE (((DateDiff("d",[MeMyselfandI].[End_Date],Now()))<60) AND ((MeMyselfAndI.End_Date)<=Now()));


    Billy

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1

    Re: Ah Yes Sorry

    Originally posted by bwood415

    WHERE (((DateDiff("d",[MeMyselfandI].[End_Date],Now()))<60) AND ((MeMyselfAndI.End_Date)<=Now()));

    Hmmm... looks vaguely familiar.



    Originally posted by Teddy

    SELECT * FROM MyTableNameHere AS t1 WHERE (DateDiff("d",t1.RenewalDate, date()) < 60) AND (RenewalDate <= Date())


    Must have been some goofy paranthesis issue.
    Last edited by Teddy; 01-20-04 at 10:08.

  10. #10
    Join Date
    Oct 2003
    Posts
    43

    Teddy

    Yes, thank you for your help, just had to tweak it for my data.


    Billy

  11. #11
    Join Date
    Oct 2003
    Posts
    43

    Re: Teddy

    Originally posted by bwood415
    Yes, thank you for your help, just had to tweak it for my data.


    Billy
    Ok, I messed Up

    Here is the right SQL code for getting all contracts that are coming up in 60 days, did not test enough dates before hand.


    SELECT DateDiff("d",Now(),[MeMyselfandI].[End_Date]) AS Expr1, * FROM Contacts AS MeMyselfAndI WHERE (((DateDiff("d",Now(),[MeMyselfandI].[End_Date]))<60) AND ((MeMyselfAndI.End_Date)>=Now()));

    Billy

    Sorry for the mix up folks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •