|
|||||||||
|
|||||||||
|
|||||||||
 |
|||||||||
 |
||
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
ASP Free Forums Sponsor:
|
|
Ajax Application Generator Generate database and reporting .NET Web apps in minutes. Quickly create visually stunning, feature-rich apps that are easy to customize and ready to deploy. Download Now!
|
|
#1
April 24th, 2008, 12:58 AM
|
|||
|
|||
|
How to fill a form with a recordset with a "complicated" where clause ?
dear colleagues !
i fill a form with a recordset using this simple code: Private Sub Form_Open(Cancel As Integer) Dim rs As ADODB.Recordset Set rs = New ADODB.Recordset rs.ActiveConnection = CurrentProject.AccessConnection rs.Source = "select logon_name, name_last_lat, name_first_lat, id from tbl_user where (id > 100)" rs.LockType = adLockOptimistic rs.CursorType = adOpenKeyset rs.Open Set Me.Recordset = rs Set rs = Nothing End Sub how could i build a more sophisticated "where" clause than "id > 100" ? i would be interested in using of something like charindex tsql function. thank you in advance !
|
|
#2
May 7th, 2008, 04:28 PM
|
|||
|
|||
|
How do you mean? Such as:
strSQL1 = "select * from TABLE where X = 1 AND Y = 2 or Z =3" If it is very long put: Dim vString vString = "select * from TABLE where" vString = vString & "X = 1 AND Y = 2 or Z =3" strSQL1 = vString Quote:
|
|
#3
May 8th, 2008, 01:10 AM
|
|||
|
|||
|
Quote:
hello, phoenix riser ! thank you for your assistance ! i mean that i want to have the following where clause: select ... from ... where ((int_field_1 is 100) and (char_field_2 contains "abcd") and (date_field_3 belongs to may-2008) etc.) any idea ?
|
|
#4
May 8th, 2008, 08:34 AM
|
|||
|
|||
|
Good day,
Firstly this first part is ripped from a project you have and I’ve not checked that is works outside of it. I do my open database etc… slightly differently so I would do… First thing is that actual dates are a pain in VB but if you literally have the words May-2008 then this is different. Set db1 = DBEngine.OpenDatabase(frmMain..DB.Text) ‘I keep my databases in text files locally at this stage as we are moving servers but hardcode yours in here Code:
strSQL1= "Select select logon_name, name_last_lat, name_first_lat, id From tbl_user WHERE int_field_1 = ‘100’ AND char_field_2 = “abcd” AND date_field_3 = “may-2008”
'
Set rs1 = db1.OpenRecordset(strSQL1)
If rs1.RecordCount > 0 Then
rs1.MoveLast
lngRecordcount1 = rs1.RecordCount
rs1.MoveFirst
Else
lngRecordcount1 = 0
End If
For Counter1 = 1 To lngRecordcount1
Etc…. For a date you might be best splitting it down as I have problems with dates, to do this I do the following. In your case change the below from less than to equals to. I wonder if this code might be useful in general? Code:
vDate1 = rs1.Fields("date_field_3 ")
vDate = Len(rs1.Fields("order_created"))
vYear = Mid(vDate1, 7, 4) 'year
vMonth = Mid(vDate1, 4, 2) 'month
vDate = Mid(vDate1, 1, 2) 'date
If vYear > vSYear Then 'if the date year selected is less that above ignore
vOkay = "1"
Else
If vYear = vSYear Then 'if year is same need to check mont
If vMonth > vSMonth Then 'if month is more than then it is okay
vOkay = "1"
Else
If vMonth = vSMonth Then
If vDate >= vSDate Then
vOkay = "1"
Else
'day in the month is less so ignore
End If
End If
End If
Else
End If
End If
'if the day is after or the same as the one selected then check it's the same as or before the end day
If vOkay = "1" Then
If vYear < vFYear Then 'if the date year selected is less that above ignore
vOkay = "2"
Else
If vYear = vFYear Then 'if year is same need to check month
If vMonth < vFMonth Then 'if month is more than then it is okay
vOkay = "2"
Else
If vMonth = vFMonth Then
If vDate <= vFDate Then
vOkay = "2"
Else
'day in the month is more so ignore
End If
End If
End If
Else
End If
End If
Else
End If
|
|
#5
May 10th, 2008, 08:11 AM
|
|||
|
|||
|
hello phoenix !
thanks for your time ! i think i explained not clearly what i want. i would like to to have criteria applied to fields of diffrent types in "where" clause: -- i want an int field to be greater than 100 -- i want a date field to be earlier that 1/1/1999 -- i want a character field to contain a specific substring etc. av
|
|
#6
May 10th, 2008, 02:07 PM
|
|||
|
|||
|
For the 100 you need
> 100 (I get my < and > wrong way round sometimes!) so fieldX => '100' For dates, split them like I do to be sure on the loop around. Either that or you could have trouble with the dates. It's more programming but write it once as a module and it will be fine. for a substring such as vWhatever you need to put field = "& vWhatever &" or whatever you need it to be such as greater than or less than (it might be '"& - I'm away from a PC with any programs on at the moment!!) Am I on the right wave lenght now? Quote:
|
|
#7
May 11th, 2008, 03:11 AM
|
|||
|
|||
|
hello, phoenix riser !
thank you very much ! you are tuned now. i am interested in string operations mostly. say i am looking for all the cities whose names contain "LL" and it is not "lloret de mar" only (starting with "LL") but "versailles" also (containing "LL" at some - generally speaking unknown - position). i guess i can fill a form with such a list using vba code. my question is whether i can fill that form with the list using "where" clause only: rs.source = "select city_code, city_name from tbl_city where (city_name contains "ll")". thank you in advance ! av
|
|
#8
May 12th, 2008, 03:47 AM
|
|||
|
|||
|
Try...
Code:
strSQL = "SELECT * FROM TABLE WHERE Member_code LIKE '*" & vVariable & "*' " Take away the * from before or after if you only want to search start/end of string. Quote:
Last edited by Phoenix_riser : May 12th, 2008 at 04:22 AM.
|
|
| Viewing: ASP Free Forums > Programming > Visual Basic Programming > How to fill a form with a recordset with a "complicated" where clause ? |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
 |
|
|
|
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
