Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Adding & Subtracting Recordsets using string

Posted on 2007-04-11
5
Medium Priority
?
368 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
[X]
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
  • 2
  • 2
5 Comments
 
LVL 53

Accepted Solution

by:
Ryan Chong earned 700 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 53

Assisted Solution

by:Ryan Chong
Ryan Chong earned 700 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 300 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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

636 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