Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Adding & Subtracting Recordsets using string

Posted on 2007-04-11
5
357 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 51

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 51

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

860 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