Evaluate string expression in access query 1

[cpmasesa]

Hi,

This might be a long read, but I need you to understand my problem properly so that you can help on how to EVALAUTE A STRING EXPRESSION IN AN ACCESS QUERY.

I am writing a data cleaning application for my office, I work in a medical research organisation, using Microsoft Access 2003.

I have almost finished, just need to perfect the part that creates STATA, a statistical package, code to actually perform the updates to the STATA dataset.

At the moment I have one table with the fields 'ProbVar', 'ProbVal', 'Correction', batch_a and sticker_a. (Well a few more fields but these are the relevant fields for my problem)

The syntax of the STATA command I need to create is(note:[FieldName] --> Square brackets mean field name):
replace [ProbVar] =[Correction] if batch_numbera == [batch_a] & sticker_numbera == [sticker_a] & [probVar] == [probval]

The code that I need produced is such as:
replace q0108b=02 if batch_numbera=="A0043" & sticker_numbera=="FS001862P" & q0108b==70

OR

replace q0117f="A" if batch_numbera=="A0233" & sticker_numbera=="XFS01231G" & q0117f==.

OR

replace q0117f=. if batch_numbera=="A0230" & sticker_numbera=="FS008870X" & q0117f=="2"

At the moment my code to do so is of the form(generated using the visual query builder in MS Access):
StataSyntax: 'replace '+[probvar]+'='+IIf([correction]='BLANK','.',IIf([probvar]='q0117f','"'+[correction],[correction]))+IIf([probvar]='q0117f' And [correction]<>'BLANK','"','')+' if batch_numbera=="'+[batch_a]+'" & sticker_numbera=="'+[sticker_a]+'" & '+[probvar]+'=='+IIf(IsNull([probval]),'.',IIf([probvar]='q0117f','"'+[probval],[probval]))+IIf([probvar]='q0117f' And Not IsNull([probval]),'"','')

This is so long because I have to enclose [ProbVal] in double quotes if [probvar] is a character field. This works fine now since for the current data set there is only one field, q0117f, that is a charatcer field! In the other datasets there are several character fields and this approach will definetly not work.

So in trying to make this solution 'scalable' I created two additional tables, DataDict and CorrRules.

DataDict has the fields varName, vartype, varsize, formname and seqno.

CorrRules has the fields vartype, probvar_corr and probvar_val.
The field probvar_corr holds the 'template' of how the correction part of the final statement should be for each data type. eg q0108b=2 or q0117f="A"
The field probvar_probval holds the 'template' of how the selection part of the final statement should be for each data type. (t=The very last bit in the three examples above) eg q0108b==70 or q0117f=="2"

The contents of the CorrRules table are as follows:
vartype-->probvar_corr-------------------------->probvar_probval
byte---->[probvar]+'='+[correction]------------->[probvar]+'='+[probval]
int----->[probvar]+'='+[correction]------------->[probvar]+'='+[probval]
long---->[probvar]+'='+[correction]------------->[probvar]+'='+[probval]
str----->"[probvar]+'=""'+[correction]+'""'"---->"[probvar]+'=""'+[probval]+'""'"

My thinking that if I joined these three tables together I would then be able to get the STATA code I need more easily no matter how many character fields there are in a dataset.

So I went ahead and did an inner join on the three tables using the visual query builder in MS Access. The join, INNER, is essentially DataDict.varname = Checks.ProbVar and DataDict.vartype = CorrRules.vartype (I hope you get the picture!)


I then created the field statasyn:'replace '+[probvar_corr]+' if batch_numbera == "'+[batch_a]+'" & sticker_numbera == "'+[sticker_a]+'" & '+[probvar_probval] hoping to get the STATA code i needed

This did NOT give me the expected results and I got the following instead:
replace [probvar]+'='+[correction] if batch_numbera == "A0046" & sticker_numbera == "FS003220D" & [probvar]+'='+[probval]

So I thought I need to include a method of evaluating the strings "[probvar]+'='+[correction]" and "[probvar]+'='+[probval]" for each row. I could not find anything in the help file, searched online and came across the EVAL() function but this produced an error when I ried it in my code.

So now I am stuck and asking someone to please help me out. Once I get it working it would greatly simplify this particular application and some others that I can think of where I could use the same reasoning

TIA for your help

Kind regards,

Clemens

 

[genomon]

Sorry I haven't time to read the whole volume, but for starters, never concatenate strings with the + character, always use &.

"Time flies like an arrow; fruit flies like a banana."

 

[cpmasesa]

genomom: Thanks for the suggestion

 

[MajP]

Build yourself a public function. You have a lot of mistakes in your code so I can not tell what is a literal and what is part of the string you want returned. But this is the general idea.

[/code]
Public Function getStata(probVar As Variant, correction As String, probVal As Variant, batch_a As Variant, sticker_a As Variant) As String
If correction = "BLANK" Then
correction = "."
ElseIf probVar = "q0117f" Then
correction = wrapStr(correction)
End If

If IsNull(probVal) Then
probVal = "."
ElseIf probVar = "q0117f" Then
probVal = wrapStr(probVal)
End If

getStata = "replace " & probVar & "=" & correction
getStata = getStata & " if batchNumer_a ==" & wrapStr(batch_a) & "& stricker_Numbera == " & wrapStr(sticker_a)
getStata = getStata & " & probvar==" & probVal

End Function

Public Function wrapStr(stringVal As Variant) As String
wrapStr = "'" & stringVal & "'"
End Function

[/code]

test
Public Sub testStata()
Debug.Print getStata("q0117f", "BLANK", "A", "A0230", "FS008870X")
End Sub

Results:
replace q0117f=. if batchNumer_a =='A0230'& stricker_Numbera == 'FS008870X' & probvar=='A'


Use this in a query or anywhere else and pass in the fieldvalues as an argument

 

[cpmasesa]

MajP,

Thanks for this. Very useful, especially the wrapStr() function, had not thought of doing it like that.

I still need to find a way to evaluate a formula template, the original question.

Tried EVAL() and it does not work.

Clemens

 

[MajP]

Not sure what you are asking here
"still need to find a way to evaluate a formula template"

eval is used to take a string and run the code that the string represents.

I think you are asking how to return the string for each row in a query. If that is the case it would look something like.

SELECT probvar, correction, probval, batch_a, sticker_a, getStata(probVar, correction, probVal, batch_a, sticker_a) as STATAString FROM tblSomeTable

If you then want to actually use that string to run code in STATA, I have no idea what STATA exposes to vb. You may be able to do this with sendkeys or DDL, but I assume STATA does not expose any automation. You will have to explain that more. Is STATA an Access Add-in.

 

[cpmasesa]

MajP,

STATA is a statistical software.

Since I have the problem in this thread solved, I started another thread asking about using the eval() function with formulae templates.

Could we please pick up this discussion at

http://www.tek-tips.com/viewthread.cfm?qid=1479374&page=1

to avoid me doing a double post?

TIA

Clemens