subform filter (1 Viewer)

[rio]

hi.

Need some help with subform filter.

there is 1 table containing lab result data :

1. SAMPLE_NO
2. SAMPLING_DATE
3. DO
4. BOD
5. COD
6. PH
7. SS

I used combobox to filter the subform. and the code are :

Private Sub cmdCari_Click()
Dim stDocName As String
Dim stLinkCriteria2 As String
Dim stLinkCriteria3 As String
Dim stLinkCriteria4 As String
Dim stLinkCriteria5 As String
stLinkCriteria2 = Me!Text1 & Me!Text3 & "'" & Me![Text2] & "'"
stLinkCriteria3 = Me!Text4 & Me!Text6 & "'" & Me![Text5] & "'"
stLinkCriteria4 = Me!Text7 & Me!Text9 & "'" & Me![Text8] & "'"
stLinkCriteria5 = stLinkCriteria2 & " And " & stLinkCriteria3 & " And " & stLinkCriteria4
Me.frmLabResult.Form.Filter = stLinkCriteria5
Me.frmLabResult.Form.FilterOn = True
End Sub

the problem is when i make a filter :

BOD < 2 === It come out the data (see attachment)

BOD
10
13
100

So hopefully... any one can help me to solve this problem.
Thanks.

 

[Galaxiom]

Less than and greater than are based on alphanumeric ordering when used with strings. 100 is less than 2.

 

[rio]

Thanks Galaxiom.

So... how to write the right code. any suggestion ?.

here i attach the db also.

please help me.

View attachment Lab Test Result.mdb

 

[Galaxiom]

Can you change the field data type to a suitable numeric type? Otherwise you could convert the value to an integer using CInt() function.

However I get the impression your filter is also meant to work on other fields too. You will need to change the way the filter is constructed to account for different data types.

This could be done by testing the field name entered and hard coding to append a suitable line for the data type corresponding to that name. A better more general solution would check the Type property of the selected field and adjust the filter string to suit.

I haven't come across a Type enumeration list but this is the form of the expression you need to retrieve the Type (as an integer) from a field by name.

 CurrentDb.TableDefs("tablename").Fields("fieldname").Properties("Type")

 

[rio]

thanks..Galaxiom

sorry for making this more complicated.

for your information.. my English not so good and ms access is new for me.

may be a few word I don't understand.

Thanks again... I will try do it as your suggestion.

 

[Galaxiom]

my English not so good and ms access is new for me.
may be a few word I don't understand.

No worries. Access takes a long time to really understand and even the most experienced developers are still learning. We have all been through the challenge of gathering enough knowledge to even ask the right questions and do appreciate the extra hurdle for those who have to overcome the language difference as well.

Just keep trying to work with the code and don't be afraid to ask questions if something doesn't make sense to you.

Fortunately we have a diverse range of experience here so someone can usually help on most problems. We like to help those like you who are clearly making an effort rather than looking for someone to do the work for them.

 

[rio]

hi.

Galaxiom.

I already try a few way as your suggestion... then I lost in my own track.

1. I try to used
CurrentDb.TableDefs("tablename").Fields("fieldname").Properties("Type")
but I don't know what should I write...
CurrentDb.TableDefs("tblLabResult").Fields("COD,BOD,SS,PH,DO").Properties("?????")

2. I try used Cint() then I create a query. and name it query1. but it still doesn't work. see attachment.

View attachment Copy of Lab Test Result.mdb

please need help..

give next step or another step or any example db.

 

[Galaxiom]

Only one object and property can be referred to at a time.
Use a set of nested loops to obtain them all.

Some things can be done with a For Each loop without needing to know the number of aspects involved. Others are done by first retrieving the Count property what is being processed and then running a For n = 0 To (Count -1) loop.

Rather than remember which things support Each I usually try a For Each first and if this returns an error then use the Count.

There are many exaples of seting up these kinds of loops on this forum.