find data in a table and put it another table?

D

dls25

I am getting this Syntax error (Missing Opertor), but i'm not sure what it
is, can you help? Should I post in another location? Is this enough code or
should I enter all of it?

strFilter = "[testnumonic]" & strtestnumonic & "[tissuetype]" &
strtissuetype & "[Deficiency]" & strDeficiency & " And [dDiagnosisName]" &
strdDiagnosisName & " "

DLS
 
J

John Vinson

I am getting this Syntax error (Missing Opertor), but i'm not sure what it
is, can you help? Should I post in another location? Is this enough code or
should I enter all of it?

strFilter = "[testnumonic]" & strtestnumonic & "[tissuetype]" &
strtissuetype & "[Deficiency]" & strDeficiency & " And [dDiagnosisName]" &
strdDiagnosisName & " "

DLS
Click to expand...

The Filter ahould be a valid SQL WHERE clause without the word WHERE,
but with all the needed operators (=, >, <, LIKE, etc.)

You don't say what's in the values named strtestnumonic,
strtissuetype, etc. but if they contain XYZ, Liver, Ascorbate and
Scurvy, you'll build a string

[testnumonic]XYZ[tissuetype]Liver[Deficiency]Ascorbate AND
[dDIagnosisname]Scurvy

which doesn't make sense either medically or in database terms.

Since I don't know what operators you want, or what the variables
contain, I can't be sure this is correct - but I'd guess you want
something more like

strFilter = "[testnumonic] = '" & strtestnumonic &
"' AND [tissuetype] = '" & strtissuetype &
"' AND [Deficiency] = '" & strDeficiency &
"' And [dDiagnosisName] = '" & strdDiagnosisName & "'"

Note that for filtering a Text datatype field must be delimited by '
or "; to get a " delimiter inside a string itself delimited by ", you
need to use two consecutive ", so to handle a diagnosisname like
Crohn's Disease you should use

"' And [dDiagnosisName] = """ & strdDiagnosisName & """"


John W. Vinson[MVP]
 
D

dls25

This is my full code and yes they are text fields. I know it's something
simple put I don't see it. Thank you for your help.
dls

Private Sub cmdApplyFilter_Click()
Dim strtestnumonic As String
Dim strtissuetype As String
Dim strDeficiency As String
Dim strdDiagnosisName As String
Dim strFilter As String
If IsNull(Me.cboTestName.Value) Then
strtestnumonic = "Like '*'"
Else
strtestnumonic = "='" & Me.cboTestName.Value & "'"
End If
If IsNull(Me.cboTissueType.Value) Then
strtissuetype = "Like '*'"
Else
strtissuetype = "='" & Me.cboTissueType.Value & "'"
End If
If IsNull(Me.cboDeficiency.Value) Then
strDeficiency = "Like '*'"
Else
strDeficiency = "='" & Me.cboDeficiency.Value & "'"
End If
If IsNull(Me.cboDiagnosis.Value) Then
strdDiagnosisName = "Like '*'"
Else
strdDiagnosisName = "='" & Me.cboDiagnosis.Value & "'"
End If
strFilter = "[testnumonic] = '" & strtestnumonic & "' AND
[tissuetype] = '" & strtissuetype & "' AND [Deficiency] = '" & strDeficiency
& "' And [dDiagnosisName] = '" & strdDiagnosisName & "'"
With Reports![rptDynamic-Report]
.Filter = strFilter
.FilterOn = True
End With
End Sub




John Vinson said:
I am getting this Syntax error (Missing Opertor), but i'm not sure what it
is, can you help? Should I post in another location? Is this enough code or
should I enter all of it?

strFilter = "[testnumonic]" & strtestnumonic & "[tissuetype]" &
strtissuetype & "[Deficiency]" & strDeficiency & " And [dDiagnosisName]" &
strdDiagnosisName & " "

DLS
Click to expand...

The Filter ahould be a valid SQL WHERE clause without the word WHERE,
but with all the needed operators (=, >, <, LIKE, etc.)

You don't say what's in the values named strtestnumonic,
strtissuetype, etc. but if they contain XYZ, Liver, Ascorbate and
Scurvy, you'll build a string

[testnumonic]XYZ[tissuetype]Liver[Deficiency]Ascorbate AND
[dDIagnosisname]Scurvy

which doesn't make sense either medically or in database terms.

Since I don't know what operators you want, or what the variables
contain, I can't be sure this is correct - but I'd guess you want
something more like

strFilter = "[testnumonic] = '" & strtestnumonic &
"' AND [tissuetype] = '" & strtissuetype &
"' AND [Deficiency] = '" & strDeficiency &
"' And [dDiagnosisName] = '" & strdDiagnosisName & "'"

Note that for filtering a Text datatype field must be delimited by '
or "; to get a " delimiter inside a string itself delimited by ", you
need to use two consecutive ", so to handle a diagnosisname like
Crohn's Disease you should use

"' And [dDiagnosisName] = """ & strdDiagnosisName & """"


John W. Vinson[MVP]
Click to expand...
 
Top