function help

[aerosmith]

I have the following code, which is not working for me,
What im trying to do is, retreive values from two tables, which are identical.
The reason why im doing this is because each table represents a months worth of data which is given to me at the begining of each month, Normalization is not really an option at this point, but might be in the future.

Function YTDINDBOARDED()
Dim db As DAO.Database


Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Set db = CurrentDb()

Set rst1 = db.OpenRecordset("SELECT Ind_Bact_Act FROM Sales_ReportResult")
Set rst2 = db.OpenRecordset("SELECT Ind_Bact_Act FROM Sales_ReportResult1")

boarded = rst1(0) + rst2(0)
End Function

The function does not give an error, It simply does not return anything,
When i create a query and call the function using an expression
test: YTDINDBOARDED() the query pops up which is empty,

Anyone able to point out some assistance, id be greatful

Thanks,

 

[pbaldy]

You have to set the name of the function to the value you want it to return.

 

[aerosmith]

You have to set the name of the function to the value you want it to return.

so change the boarded to the function name?

Function YTDINDBOARDED()
Dim db As DAO.Database


Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Set db = CurrentDb()

Set rst1 = db.OpenRecordset("SELECT Ind_Bact_Act FROM Sales_ReportResult")
Set rst2 = db.OpenRecordset("SELECT Ind_Bact_Act FROM Sales_ReportResult1")

YTDINDBOARDED = rst1(0) + rst2(0)
End Function

 

[DCrake]

The correct syntax should be

Function YTDINDBOARDED() As Long
Dim db As DAO.Database


Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
[B]Dim Boarded As Long[/B]
Set db = CurrentDb()

Set rst1 = db.OpenRecordset("SELECT Ind_Bact_Act FROM Sales_ReportResult")
Set rst2 = db.OpenRecordset("SELECT Ind_Bact_Act FROM Sales_ReportResult1")

[B]If Not rst1.EOF And Not rst1.BOF Then
   boarded = Nz(st1(0),0)
   rst1.Close
End IF

If Not rst2.EOF And Not rst2.BOF Then
   boarded = boarded + Nz(rst2(0),0)
   rst2.Close
End IF

Set rst1 = Nothing
Set rst2 = Nothing
set db = Nothing

YTDINDBOARDED = boarded[/B]
End Function

Using the above you have trapped all possible errors and released the recordsets from cache.

David

 

[aerosmith]

Many thanks!!! i can finally see a calculation being done,

When i add this to the query test: YTDINDBOARDED()

Every line has the same value. its adding the rst1(49) and rst2(72) but only the first set of values.


so every 18 members in my db have the same calculation now. so is it possible its not reading past the first set of values?

The correct syntax should be

Function YTDINDBOARDED() As Long
Dim db As DAO.Database


Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
[B]Dim Boarded As Long[/B]
Set db = CurrentDb()

Set rst1 = db.OpenRecordset("SELECT Ind_Bact_Act FROM Sales_ReportResult")
Set rst2 = db.OpenRecordset("SELECT Ind_Bact_Act FROM Sales_ReportResult1")

[B]If Not rst1.EOF And Not rst1.BOF Then
   boarded = Nz(st1(0),0)
   rst1.Close
End IF

If Not rst2.EOF And Not rst2.BOF Then
   boarded = boarded + Nz(rst2(0),0)
   rst2.Close
End IF

Set rst1 = Nothing
Set rst2 = Nothing
set db = Nothing

YTDINDBOARDED = boarded[/B]
End Function

Using the above you have trapped all possible errors and released the recordsets from cache.

David

 

[DCrake]

Your initial question was not clear enough if you want to see the figures for all records you need a query which includes both tables joined by the PK and simply add the values of the fields together.