Page 1 of 2 12 LastLast
Results 1 to 15 of 24

Thread: Code Help Again

  1. #1
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397

    Unanswered: Code Help Again

    Hi,

    As a part of Excel automation, I am generating data from 2 related table (one-to-may relationship) to Excel worksheet as below. Tables are ‘FIXEDFIELD ‘LOANTABLE

    Dim objXL As Excel.Application
    Dim objWkb As Excel.Workbook
    Dim objSht As Excel.Worksheet

    Dim rst As Recordset
    Dim rst1 As Recordset
    Dim iRow As Integer

    'Set objXL = New Excel.Application
    objXL.Visible = True
    Set objWkb = objXL.Workbooks.Open("D:\SAYES\SAYESRPT.xls")
    'Set objWkb = objXL.Workbooks.Open("C:\Documents and Settings\ashfaque\My Documents\My Projects\RUNINGPRO\RPO\ExcelReports.xls")
    Set objSht = objWkb.Worksheets("Sheet1")

    THIS IS OK.

    Now I want to pick up only those records which has current month and current year. So I used GeorgeV’s below criteria to get the result. This myCriteria code worked in Access query perfectly. But the same I m trying to use in VBA.

    Dim myCriteria As String
    myCriteria = "Month([PDate]) = " & Month(Now())
    myCriteria = myCriteria & " AND Year([PDate]) = " & Year(Now())

    The FIXEDFIELD hold following fields

    Eno
    Ename

    The LOANTABLE has detail records with below columns.

    PDate
    Eno
    Descripton
    MonthlyInst
    Credit
    Debit

    I am planning is to display each employees record in current month mean Eno, Ename, MonthlyInst from the LOANTABLE.

    Also I would like to display sum of employee’s MonthlyInst in next column. So it would be like this

    Eno Emp MonthlyInst Tilldate Contribution LoanBalance
    461 XYZ 100 2400 1500
    30 PQR 100 2400 1200
    so on..

    But the below code produces syntax error. And when somehow I manages, it says no record found where is the data with this month’date is available in LOANTABLE.PDATE

    Set rst = CurrentDb.OpenRecordset("SELECT LOANTABLE.ENO, FIXEDFIELD.ENAME, " & _
    "Sum(LOANTABLE.MonthlyInst) AS SumOfMonthlyInst, LOANTABLE.PDATE " & _
    "FROM FIXEDFIELD INNER JOIN LOANTABLE ON FIXEDFIELD.ENO = LOANTABLE.Eno " & _
    "GROUP BY LOANTABLE.Eno, FIXEDFIELD.ENAME " & _
    "WHERE LOANTABLE.PDATE = " & myCriteria & ";")


    Dim RowCount As Double
    RowCount = 1

    rst.MoveFirst

    Do While Not rst.EOF

    objSht.Cells(iRow, 1).Value = RowCount
    objSht.Cells(iRow, 1).HorizontalAlignment = xlCenter
    objSht.Cells(iRow, 1).Borders.Color = vbBlack

    objSht.Cells(iRow, 2).Value = rst!ENAME
    objSht.Cells(iRow, 2).HorizontalAlignment = xlCenter
    objSht.Cells(iRow, 2).Borders.Color = vbBlack

    objSht.Cells(iRow, 3).Value = rst!MonthlyInst
    objSht.Cells(iRow, 3).HorizontalAlignment = xlCenter
    objSht.Cells(iRow, 3).Borders.Color = vbBlack

    objSht.Cells(iRow, 4).Value = rst!Credit
    objSht.Cells(iRow, 4).HorizontalAlignment = xlCenter
    objSht.Cells(iRow, 4).Borders.Color = vbBlack

    'Increase all row height
    objSht.Cells(iRow, 6).RowHeight = 39

    iRow = iRow + 1
    RowCount = RowCount + 1
    rst.MoveNext
    Loop

    Need your help please..
    With kind regards,
    Ashfaque

  2. #2
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Just a suggestion as I am not experioence with SQL
    Change the WHERE portion "WHERE LOANTABLE.PDATE = " & myCriteria & ";")

    to "WHERE LOANTABLE.PDATE = myCriteria )

    OR

    Create a query using your criteria then copy the SQL code for the criteria MINUS the ; at the end of the sql into your coding.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'm not surprised its having problems, the SQL doesn't make sense, it won't pass the SQL parser...

    "WHERE LOANTABLE.PDATE = " & myCriteria & ";")

    myCriteria is
    "Month([PDate]) = 1 AND Year([PDate]) = 2008"

    which means effectively you are requesting
    "WHERE LOANTABLE.PDATE = Month([PDate]) = 1 AND Year([PDate]) = 2008

    If I were you I'd strip out the complexity and make it

    "WHERE Month([PDate]) = " & Month(Now()) & " AND Year([PDate]) = " & Year(Now())


    if you hit these sort of problems the first thing I'd look at is what you are actually requesting from SQL engine. Often what you actually request isn't the same as you think you are requesting. assigning the SQL to a variable before using it somewhere allows you the chance to see what is there, and helps debugging.

  4. #4
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Thanks gents,

    In both of style it produces

    Run-time error 3075
    Syntax error (missing operator) in query expression
    'WHERE Month([PDATE]) = 1 And Year([PDATE]) = 2008'

    Any other idea?
    With kind regards,
    Ashfaque

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    since you have a JOIN, try:
    'WHERE Month(loantable.PDATE) = 1 And Year(loantable.PDATE) = 2008'
    i hate hate hate those square brackets


    if still no-go, see healdem's #3
    assigning the SQL to a variable before using it somewhere allows you the chance to see what is there, and helps debugging.

    do that, debug.print it, copy/paste post it with code wrappers

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ok so its down to you to do some basic debugging
    I'd suggest first off trying your queries int he query designer and prove they work there. if they do then its down to how your fields are being marshalled
    so open up the query designer and switch to SQL view paster your sql

    take out the where clause, does your SQL work
    if not take out the group by and where clauses

    try to resolve where the issue is.
    try to identify what the problem is

    try replacing functions with values

    eg where month(pdate)=1 and year(pdate)=2008
    the advantage of assigning the value of the SQL to a variable is that you can see what the SQL you are sending is. you can cut & paste the sql and put it into the query designer...... ok you may have to set a break point or debug.print the sql bit you can get your sticky hands on precisely what you are sending to the SQL engine. You can then debug the problem bit by bit till you find what the fault is.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what actually is the SQL you using... Im not interested in partail bits, or the code, what is the actual SQL you are sending. Bear in mind the numan eye & brain is great at seeing what it wants to see.... to debug your SQL you need to see what you are sending to the SQL engine

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    an idle Sunday morning thought - why not avoid calling the VBA functions Year() and Month() for every row in your table?
    Code:
        Dim dtStart As Date
        Dim dtEnd As Date
        Dim strSQL As String
        dtStart = DateAdd("d", -1 * (Day(Date) - 1), Date)
        dtEnd = DateAdd("m", 1, dtStart)
        dtStart = DateAdd("s", -1, dtStart)
        strSQL = "SELECT a, b FROM loantable WHERE loantable.pdate Between " & ISOdate(dtStart) & " And " & ISOdate(dtEnd)
    and to make pseudo-ISO date-strings
    Code:
    Private Function ISOdate(someDate As Date) As String
        ISOdate = "#" & Format$(someDate, "YYYY-MM-DD HH:NN:SS") & "#"
    End Function
    no VBA calls in the query
    let the SQL engine take the strain!

    izy
    currently using SS 2008R2

  9. #9
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    I tried to put direct value but the outpuit is same...

    Run-time error 3075
    Syntax error (missing operator) in query expression 'Loantable.Eno
    WHERE Month([PDATE]) = 1 And Year([PDATE]) = 2008'
    With kind regards,
    Ashfaque

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what is the actual SQL you are sending to the SQL engine?

    as suggested before lets see the whole SQL statement as being sent to the SQL engine. not the SQL taken form your VB code.. but the actual SQL.

    I suspect that the syntax error may well be elsewhere in the SQL statement

    thats why it was suggested that you paste your SQL code into the query browser and then debug it there.

    learning good debugging techniques is a must for software development

  11. #11
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Here is jpg shows error..
    Attached Thumbnails Attached Thumbnails CodeError.JPG  
    With kind regards,
    Ashfaque

  12. #12
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    I want to just pick up only thsoe data whose month and year is current month and year. This is what I m trying..
    With kind regards,
    Ashfaque

  13. #13
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    duh! i didn't notice the GROUP BY

    try HAVING instead of WHERE
    e.g.
    ...GROUP BY loantable.Eno, fixedfield.ename HAVING loantable.pdate between ...

    it would help if you control your CAPitaLISatiON.
    capitalise the SQL keywords (SELECT FROM WHERE GROUP BY HAVING etc) but not the rest.

    izy

    ...LATER: since you are not testing an aggregated date value, just place the WHERE before the GROUP BY
    SELECT blahblah
    FROM blahblah
    WHERE blahblah
    GROUP BY blahblah
    Last edited by izyrider; 01-06-08 at 08:32.
    currently using SS 2008R2

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so have you tried debugging your SQL in the query browser?
    do you know your SQL is valid?
    what steps have you taken to prove its correct?

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by izyrider
    duh! i didn't notice the GROUP BY
    we all have our off days....

    ...Sorry to say I had..

    but I was trying to persuade Ashfaque to do some elementary debugging to encourage him to think about the problem, analyse the possible cause and see if he could refine/develop his own debugging strategy to reduce the need to revisit here.. but more importantly to improve the development skill set.

    Theres no harm in coming on here and asking questions.. but sometimes peopel are too quick to rush here seeking advice from others, when a bit of time thinking about the problem and trying to work out what what the error is may save a lot of development time

    mebbe I'm just in an ornery mood today

    personaly the actual crux of the problem was masked by all the other extraneous non information.. the full horror of the SQL, the element that was reporting the error was last in the mass of other largely irrelevant stuff.

Posting Permissions

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