Solved

Adding & Subtracting Recordsets using string

Posted on 2007-04-11
5
347 Views
Last Modified: 2012-05-05
I have created some recordsets that hold information.
I now need to add (or subtract) values in the recordsets dependent on values in a particular field.
I am getting a runtime error.
The following is my code:

strGLMTH = "rst2e.Fields('GLmth').Value"

If rst2d.Fields("SourceSystem").Value = "SYS" Then
strGLMTH = strGLMTH & " + rst2d.Fields('GLmth').Value"
Else
strGLMTH = strGLMTH & " - rst2d.Fields('GLmth').Value"
End If

If rst2c.Fields("SourceSystem").Value = "SYS" Then
strGLMTH = strGLMTH & " + rst2c.Fields('GLmth').Value"
Else
strGLMTH = strGLMTH & " - rst2c.Fields('GLmth').Value"
End If

If rst2b.Fields("SourceSystem").Value = "SYS" Then
strGLMTH = strGLMTH & " + rst2b.Fields('GLmth').Value"
Else
strGLMTH = strGLMTH & " - rst2b.Fields('GLmth').Value"
End If

If rst2a.Fields("SourceSystem").Value = "SYS" Then
strGLMTH = strGLMTH & " + rst2a.Fields('GLmth').Value"
Else
strGLMTH = strGLMTH & " - rst2a.Fields('GLmth').Value"
End If
MsgBox strGLMTH

rstZFPR.Fields("GLmth").Value = strGLMTH

Is there a way I can get Access (VB) to do the resultant equation that the string (strGLMTH) compiles?
FYI The message box gives the correct string.

Any help would be great
:-)
0
Comment
Question by:martywal
  • 2
  • 2
5 Comments
 
LVL 49

Accepted Solution

by:
Ryan Chong earned 350 total points
ID: 18888350
try this..


strGLMTH = val(rst2e.Fields('GLmth').Value)

If rst2d.Fields("SourceSystem").Value = "SYS" Then
strGLMTH = strGLMTH  + val(rst2d.Fields('GLmth').Value)
Else
strGLMTH = strGLMTH - val(rst2d.Fields('GLmth').Value)
End If

If rst2c.Fields("SourceSystem").Value = "SYS" Then
strGLMTH = strGLMTH + val(rst2c.Fields('GLmth').Value)
Else
strGLMTH = strGLMTH  - val(rst2c.Fields('GLmth').Value)
End If

If rst2b.Fields("SourceSystem").Value = "SYS" Then
strGLMTH = strGLMTH + val(rst2b.Fields('GLmth').Value)
Else
strGLMTH = strGLMTH  - val(rst2b.Fields('GLmth').Value)
End If

If rst2a.Fields("SourceSystem").Value = "SYS" Then
strGLMTH = strGLMTH + val(rst2a.Fields('GLmth').Value)
Else
strGLMTH = strGLMTH  - val(rst2a.Fields('GLmth').Value)
End If
MsgBox strGLMTH

rstZFPR.Fields("GLmth").Value = strGLMTH
0
 
LVL 49

Assisted Solution

by:Ryan Chong
Ryan Chong earned 350 total points
ID: 18888456
you may try use " instead of ' , like:


strGLMTH = val(rst2e.Fields("GLmth").Value)

If rst2d.Fields("SourceSystem").Value = "SYS" Then
strGLMTH = strGLMTH  + val(rst2d.Fields("GLmth").Value)
Else
strGLMTH = strGLMTH - val(rst2d.Fields("GLmth").Value)
End If

If rst2c.Fields("SourceSystem").Value = "SYS" Then
strGLMTH = strGLMTH + val(rst2c.Fields("GLmth").Value)
Else
strGLMTH = strGLMTH  - val(rst2c.Fields("GLmth").Value)
End If

If rst2b.Fields("SourceSystem").Value = "SYS" Then
strGLMTH = strGLMTH + val(rst2b.Fields("GLmth").Value)
Else
strGLMTH = strGLMTH  - val(rst2b.Fields("GLmth").Value)
End If

If rst2a.Fields("SourceSystem").Value = "SYS" Then
strGLMTH = strGLMTH + val(rst2a.Fields("GLmth").Value)
Else
strGLMTH = strGLMTH  - val(rst2a.Fields("GLmth").Value)
End If
MsgBox strGLMTH

rstZFPR.Fields("GLmth").Value = strGLMTH
0
 
LVL 14

Assisted Solution

by:bluelizard
bluelizard earned 150 total points
ID: 18888516
use the eval function: for example:

  Eval("1 + 1")

returns 2.


--bluelizard
0
 

Author Comment

by:martywal
ID: 18897665
Sorry Guys,
Forgot I'd posted this.
I ended up not using a string as I couldn't get it to work.
It looked like this:

rstZFPR.Fields("GLmth").Value = rst2e.Fields("GLmth").Value

If rst2d.Fields("SourceSystem").Value = "SYS" Then
rstZFPR.Fields("GLmth").Value = rstZFPR.Fields("GLmth").Value + rst2d.Fields("GLmth").Value
Else
rstZFPR.Fields("GLmth").Value = rstZFPR.Fields("GLmth").Value - rst2d.Fields("GLmth").Value
End If

If rst2c.Fields("SourceSystem").Value = "SYS" Then
rstZFPR.Fields("GLmth").Value = rstZFPR.Fields("GLmth").Value + rst2c.Fields("GLmth").Value
Else
rstZFPR.Fields("GLmth").Value = rstZFPR.Fields("GLmth").Value - rst2c.Fields("GLmth").Value
End If

If rst2b.Fields("SourceSystem").Value = "SYS" Then
rstZFPR.Fields("GLmth").Value = rstZFPR.Fields("GLmth").Value + rst2b.Fields("GLmth").Value
Else
rstZFPR.Fields("GLmth").Value = rstZFPR.Fields("GLmth").Value - rst2b.Fields("GLmth").Value
End If

If rst2a.Fields("SourceSystem").Value = "SYS" Then
rstZFPR.Fields("GLmth").Value = rstZFPR.Fields("GLmth").Value + rst2a.Fields("GLmth").Value
Else
rstZFPR.Fields("GLmth").Value = rstZFPR.Fields("GLmth").Value - rst2a.Fields("GLmth").Value
End If

Will split points
0
 

Author Comment

by:martywal
ID: 18897678
I ended up finding that could add recordsets using a string such as this.
THanks for help
Martywal
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now