I have access DB and few queries there.
Recently I have added an option group with two radio buttons(values: 1 and 2) to my form.
What I try to achieve is: when first radio button is checked queries should return data only for countries; when second one is checked queries should return only data for cities.
I created some code:
Private Sub Command54_Click()
Dim dtps As String
If Frame45.Value = 1 Then
dtps = "101,103,1104,1105"
Else
dtps = "105,125,127,129,131,133,145,147,149,151,153,171,173,175,177,179,181,1105,1125,1127,1129,1131,1133,1141,1145,1147,1149,1151,1153,104,124,126,128,130,132,144,146,148,150,152,170,172,172,176,178,180,1104,1124,1126,1128,1130,1132,1144,1146,1146,1148,1150,1152"
End If
DoCmd.OpenQuery "test1", acViewNormal, acEdit
End Sub
Right now query "test1" is very simple:
"Select * from MyTable"
And my idea is to change it to :
"Select * from MyTable Where CountryCodeID IN ( @dtps )"
Does anybody know how to do it?
What I also tried is to use function:
My Query code:
Select * from MyTable Where CountryCodeID IN ( getcountrycode() )
Function code is:
Private Sub Command54_Click()
'MsgBox Frame45.Value
DoCmd.OpenQuery "test1", acViewNormal, acEdit
End Sub
Public Function getcountrycode()
Dim dtps As String
If Frame45.Value = 1 Then
dtps = "101,103,1104,1105"
Else
dtps = "101,103,105,125,127,129,131,133,145,147,149,151,153,171,173,175,177,179,181,1105,1125,1127,1129,1131,1133,1141,1145,1147,1149,1151,1153,104,124,126,128,130,132,144,146,148,150,152,170,172,172,176,178,180,1104,1124,1126,1128,1130,1132,1144,1146,1146,1148,1150,1152"
End If
getcountrycode = dtps
End Function
It returns error: "Undefined function 'getcountrycode' in expression
