Solved

Adding & Subtracting Recordsets using string

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

910 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

21 Experts available now in Live!

Get 1:1 Help Now