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





*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hi Cady,

"Cady Steldyn" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
>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)?

This is .net newsgroup :-)
Aynway, I would check for any typo.
Then I would check at debug time, the fieldnames of the recordset returned.
Just for the beginning :-)
--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com