Help Joining strings

[Guest]

I am trying to join two strins to form the name of a field in a recordset. I
think it would be easier if I showed you the code.

Function TotalFinder(Name As String)

Dim dbs As Database, rst As DAO.Recordset
Dim Column, TaxesCol As String
Dim Income, Taxes as Currency
Column = 0
TaxesCol = 0

Select Case Name
Case Is = Chores
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Chores")
Column = "ChoresIN"
TaxesCol = "ChoresOut"
End Select

Do

Income = Income + "rst!" & "Column" '[1]
Taxes = Taxes+ "rst!" & "TaxesCol"
rst.MoveNext

Loop Until rst.EOF

I am getting a type mismatch at point [1]

Any help would be greatly appreciated

 

[Marshall Barton]

I am trying to join two strins to form the name of a field in a recordset. I
think it would be easier if I showed you the code.

Function TotalFinder(Name As String)

Dim dbs As Database, rst As DAO.Recordset
Dim Column, TaxesCol As String
Dim Income, Taxes as Currency
Column = 0
TaxesCol = 0

Select Case Name
Case Is = Chores
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Chores")
Column = "ChoresIN"
TaxesCol = "ChoresOut"
End Select

Do

Income = Income + "rst!" & "Column" '[1]
Taxes = Taxes+ "rst!" & "TaxesCol"

The correct syntax for that kind of thing would be:

Income = Income + rst(Column)

 

[Bas Cost Budde]

Function TotalFinder(Name As String)

Dim dbs As Database, rst As DAO.Recordset
Dim Column, TaxesCol As String

Beware: Column is NOT dim'd as String (as you might expect). Do
Dim Column as string
dim taxesCol as string
instead.

Dim Income, Taxes as Currency
same

Column = 0
TaxesCol = 0

Select Case Name
Case Is = Chores

should be
Case "Chores"

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Chores")
Column = "ChoresIN"
TaxesCol = "ChoresOut"
End Select

or, since you have only one comparison here, you could use If...Then (or
skip calling the procedure altogether). But maybe you cut out several
other Cases.

Do

Income = Income + "rst!" & "Column" '[1]

Income = Income + rst!ChoresIn'explicit field name
or
Income = Income + rst(Column)'referred field name

Taxes = Taxes+ "rst!" & "TaxesCol" likewise
rst.MoveNext

Loop Until rst.EOF

Should your recordset ever be empty, then the two assignment statements
will fail. I usually put the test above, at Do

 

[Guest]

Thank you both very much for responding.
I never would have believed that the sytax would be such.
I have two more small relating questions:
1 Why wouldn't Column be set as a string?
2 What good does setting a variable as currency? For both of my currency
values I get numbers back as 27806.8946 -1570.3934 and 1598.47
-50.22


Thanks again

Function TotalFinder(Name As String)

Dim dbs As Database, rst As DAO.Recordset
Dim Column, TaxesCol As String

Beware: Column is NOT dim'd as String (as you might expect). Do
Dim Column as string
dim taxesCol as string
instead.

Dim Income, Taxes as Currency
same

Column = 0
TaxesCol = 0

Select Case Name
Case Is = Chores

should be
Case "Chores"

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Chores")
Column = "ChoresIN"
TaxesCol = "ChoresOut"
End Select

or, since you have only one comparison here, you could use If...Then (or
skip calling the procedure altogether). But maybe you cut out several
other Cases.

Do

Income = Income + "rst!" & "Column" '[1]

Income = Income + rst!ChoresIn'explicit field name
or
Income = Income + rst(Column)'referred field name

Taxes = Taxes+ "rst!" & "TaxesCol" likewise
rst.MoveNext

Loop Until rst.EOF

Should your recordset ever be empty, then the two assignment statements
will fail. I usually put the test above, at Do
--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

 

[Bas Cost Budde]

Thank you both very much for responding.
I never would have believed that the sytax would be such.

The Help has examples for this. See Recordset, sample to the Edit method

I have two more small relating questions:
1 Why wouldn't Column be set as a string?

Because.
As I recall it, in VB it works the way you typed: every variable on one
line receives the type that is specified on that line. Much like

Dim (Column, TaxesCol) as String

But VBA puts the brackets in another way:

Dim (Column), (TaxesCol as String)

So for Column you now have the default variable type, which is Variant.

2 What good does setting a variable as currency? For both of my currency
values I get numbers back as 27806.8946 -1570.3934 and 1598.47
-50.22

Dunno. Currency is stored with fixed precision, four decimal digits.

 

[Guest]

I Thank You for your insight and knowledge. This is the beginning of an
Income statement for an accounting program I have written for my business.
The commercial products are so bloated with stuff I don't need, and besides
that, I like writing in access. I'll probabley be back.
Thanks again!

 

[Tim Ferguson]

As I recall it, in VB it works the way you typed: every variable on one
line receives the type that is specified on that line. Much like

Dim (Column, TaxesCol) as String

Don't think it does, Bas. C and Pascal are like that, but all flavours of
VB (vba, vbs, vb; GK what happens in vb.net) take one variable per data
type.

Dim Column as String, TaxesCol as String

works as advertised, of course.

B Wishes


Tim F

 

[Bas Cost Budde]

Don't think it does, Bas. C and Pascal are like that, but all flavours of
VB (vba, vbs, vb; GK what happens in vb.net) take one variable per data
type.

Should I have said VB version 3 or so? I am definately sure I tripped
over this wire in my first Access attempts (talking 2.0) and my previous
experience with GUI programming was in (early as it must have been) VB.

Maybe it is indeed my Pascal background. -- Of course I started out with
Basic on the C64 (as everyone else ;-) ) without any variable
declarations except for arrays. By Jove, that's 26 years ago, I'm aging.

 

[Guest]

I am back again. . . So soon.
Last night I added two more to the case statement and ran it. It worked fine.
Tonight I added three more to the case statement. Just so you will know I
did nothing else to the code, here is the whole thing. In the future I will
remove the additional case parts.

Function TotalFinder(Name As String)

Dim dbs As Database, rst As DAO.Recordset
Dim Column As String
Dim TaxesCol As String
Dim Income As Currency
Dim Taxes As Currency
Income = 0
Taxes = 0

Select Case Name
Case Is = "Chores"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Chores")
Column = "ChoresIN"
Case Is = "Data Processing"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Data Processing")
Column = "DPIN"
Case Is = "Coffee Sales"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Coffee Sales")
Column = "CoffeeIN"
Case Is = "Materials"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Materials")
Column = "IN"
Case Is = "Supplies"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Supplies")
Column = "Out"
Case Is = "Equipment"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Equipment")
Column = "Cost"
End Select

Do
Income = Income + rst(Column) [1]
rst.MoveNext

Loop Until rst.EOF
TotalFinder = Income
'Debug.Print Income, Taxes
End Function


Now I am getting runtime error 91 Object variable or With block variable
not set

?????
I don't understand how this came about overnight

 

[Bas Cost Budde]

I am back again. . . So soon.
Last night I added two more to the case statement and ran it. It worked fine.
Tonight I added three more to the case statement. Just so you will know I
did nothing else to the code, here is the whole thing. In the future I will
remove the additional case parts.

Function TotalFinder(Name As String)

Dim dbs As Database, rst As DAO.Recordset
Dim Column As String
Dim TaxesCol As String
Dim Income As Currency
Dim Taxes As Currency
Income = 0

That's a statement! Mine isn't 0 )

Taxes = 0

Select Case Name
Case Is = "Chores"

If you do a straight compare (to one value), you don't need "Case Is",
the plain 'Case "Chores"' should do.

Set dbs = CurrentDb

I'd take this out of the Case structure because you need it in every
Case (funny). Saves a lot of lines.

Set rst = dbs.OpenRecordset("Chores")
Column = "ChoresIN"
Case Is = "Data Processing"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Data Processing")
Column = "DPIN"
Case Is = "Coffee Sales"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Coffee Sales")
Column = "CoffeeIN"
Case Is = "Materials"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Materials")
Column = "IN"
Case Is = "Supplies"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Supplies")
Column = "Out"
Case Is = "Equipment"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Equipment")
Column = "Cost"
End Select

Do
Income = Income + rst(Column) [1]
rst.MoveNext

Loop Until rst.EOF
TotalFinder = Income
'Debug.Print Income, Taxes
End Function


Now I am getting runtime error 91 Object variable or With block variable
not set

?????
I don't understand how this came about overnight

hmm, you don't say how this was run, but I suspect you supplied a Name
that is not in any Case. Hence, no dbs was set (you will overcome this
with my suggestion of taking the Set out of the Case) and no rst was set.

If you do just want to sum all (fields of) records of a given table, you
could do this in another fashion as well, using the database functionality.

Case "Chores"
TotalFinder = DSum("ChoresIn","Chores")
etc

As far as I can tell there is a problem with your data structure, since
the column you want to sum (obviously of the same data position in the
structure) sits in several tables under different names. Could you
envision all data of this type sitting in one table, possibly with an
extra field indicating its function, so you could just add up the, say,
Income column?

Assuming this table was created as
IncomeTypes(type,income,more-fields-you-have-now), the single statement
for TotalFinder now becomes

TotalFinder=DSum("Income","IncomeTypes","type='"& Name &"'")

 

[Guest]

Well Thank you.
I have cleaned up my code as you can see:

Function TotalFinder(Name As String)

Dim dbs As Database, rst As DAO.Recordset
Dim Column As String
Dim Income As Currency
Income = 0

Set dbs = CurrentDb

Select Case Name
Case "Chores"
TotalFinder = DSum("ChoresIn", "Chores")
Case "Data Processing"
TotalFinder = DSum("DPIn", "Data Processing")
Case "Coffee Sales"
TotalFinder = DSum("CoffeeIn", "Coffee Sales")
Case "Materials"
TotalFinder = DSum("Out", "Cos - Materials")
Case "Supplies"
TotalFinder = DSum("Out", "CoS - Supplies")
Case "Equipment"
TotalFinder = DSum("Cost", "CoS -Equipment")
End Select
End Function

The DSum works very well. i have never used the DFunctions; lack of
understanding I guess.

I want to thank you bery much. If I need any more help, I will start a new
thread.

I am back again. . . So soon.
Last night I added two more to the case statement and ran it. It worked fine.
Tonight I added three more to the case statement. Just so you will know I
did nothing else to the code, here is the whole thing. In the future I will
remove the additional case parts.

Function TotalFinder(Name As String)

Dim dbs As Database, rst As DAO.Recordset
Dim Column As String
Dim TaxesCol As String
Dim Income As Currency
Dim Taxes As Currency
Income = 0

That's a statement! Mine isn't 0 )

Taxes = 0

Select Case Name
Case Is = "Chores"

If you do a straight compare (to one value), you don't need "Case Is",
the plain 'Case "Chores"' should do.

Set dbs = CurrentDb

I'd take this out of the Case structure because you need it in every
Case (funny). Saves a lot of lines.

Set rst = dbs.OpenRecordset("Chores")
Column = "ChoresIN"
Case Is = "Data Processing"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Data Processing")
Column = "DPIN"
Case Is = "Coffee Sales"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Coffee Sales")
Column = "CoffeeIN"
Case Is = "Materials"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Materials")
Column = "IN"
Case Is = "Supplies"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Supplies")
Column = "Out"
Case Is = "Equipment"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Equipment")
Column = "Cost"
End Select

Do
Income = Income + rst(Column) [1]
rst.MoveNext

Loop Until rst.EOF
TotalFinder = Income
'Debug.Print Income, Taxes
End Function


Now I am getting runtime error 91 Object variable or With block variable
not set

?????
I don't understand how this came about overnight

hmm, you don't say how this was run, but I suspect you supplied a Name
that is not in any Case. Hence, no dbs was set (you will overcome this
with my suggestion of taking the Set out of the Case) and no rst was set.

If you do just want to sum all (fields of) records of a given table, you
could do this in another fashion as well, using the database functionality.

Case "Chores"
TotalFinder = DSum("ChoresIn","Chores")
etc

As far as I can tell there is a problem with your data structure, since
the column you want to sum (obviously of the same data position in the
structure) sits in several tables under different names. Could you
envision all data of this type sitting in one table, possibly with an
extra field indicating its function, so you could just add up the, say,
Income column?

Assuming this table was created as
IncomeTypes(type,income,more-fields-you-have-now), the single statement
for TotalFinder now becomes

TotalFinder=DSum("Income","IncomeTypes","type='"& Name &"'")
--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

 

[Bas Cost Budde]

I want to thank you bery much.

Got a cold?

Glad it works. See you.