How to generate Calculated Field

  • Thread starter Thread starter Cady Steldyn
  • Start date Start date
C

Cady Steldyn

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
 
Hi Cady,

Cady Steldyn said:
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)?
Click to expand...

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 :-)
 
Back
Top