Datareport-How to query from MS Access

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Datareport-How to query from MS Access

Author

Topic

fdtoo
Starting Member

28 Posts

Posted - 2004-09-19 : 05:37:55

I need to run a data report that will query an Access_Table that has the following Project info:

My Form has a command_button with the following code:

Private Sub Command1_Click()

Set db = New Connection
db.CursorLocation = adUseClient
db.Open "PROVIDER=MSDataShape;Data PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Access_Table.mdb;"

Set adoAge = New Recordset

adoAge.Open "SELECT CustomerName, OrderNo, Invoice_Date, IIf([Invoice_Date]<Now()-30 And [Invoice_DATE]>Now()-60,[Invoice_Amount],"") AS Greaterthan30, IIf([Invoice_Date]<Now()-60,[Invoice_Amount],"") AS Greaterthan60 FROM Cust WHERE [Invoice_Date]< Now()-30", db, adOpenStatic, adLockOptimistic

Set DataReport1.DataSource = adoAge
DataReport1.Show

End Sub

My DataReport1 has the following RptTextbox:

RptTextbox Datafield
1 CustomerName
2 OrderNo
3 Invoice_Date
4 Greaterthan30
5 Greaterthan60

Problem arise when trying to display the above report that has an error message that says "Datafield Greaterthan30 not found"
Is this an SQL Query limitation where Invoice_Date Datafield can only be queried once & if we have more than one Datafield
with the same fieldname (Invoice_Date) within the same Data report,the report will fail to run? Should there be one more
New Recordset to hold a second Datafield (Invoice_Date)?

can anyone help? Thanks
From:Cady Steldyn

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-09-19 : 19:11:14

Have you tried embedding the query into the report itself? This means hard-coding the query in the RecordSource parameter.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources