Re: [ms_access] ANY TIPS PLS

[L Tumbulu]

Hi Faisal

I suggest you build a form and put all the search parameters on it as unbound 
textboxes. Below them put a listbox whose rowsource you are going to build 
depending on the parameters. Name the textboxes accordingly. Construct your 
query in VBA.

Test each textbox if it contains a valid string or number and then construct 
the search criteria as follows

If Len(Nz(Me.txtAge)) > 0 Then    'You could also use Val(Me.txtAge)
    stCriteria = "Age = " & Me.txtAge 
    stOrderBy = "Age"
Else
    stCriteria = ""
End If

If Len(Nz(Me.txtFirstName)) > 0 Then
    If stCriteria = "" Then
        stCriteria = stCriteria & "[FirstName] Like '" & Me.txtFirstName & "*'"
        stOrderBy = "[FirstName], [Surname]"
    Else
        stCriteria = stCriteria & " And [FirstName] Like '" & Me.txtFirstName & 
"*'"
    End If
End If

etc, etc

Then build your query like  

stRwoSource = SELECT * FROM YourTable "
    If stCriteria <> "" Then
        stRowSource = stRowSource & " WHERE " & stCriteria
    End If
    
    If stOrderBy <> "" Then
        stRowSource = stRowSource & " ORDER BY " & stOrderBy
    End If
stRowSource = stRowSource & ";"

Me.YourListBox.RowSource = stRowSource

Notes: stOrderBy is used for sorting the results on the given field
           I hope you know the function of "Like" in the criteria

There might be a simpler method but this is what I use

Hope that helps you in some way

Liveson

  ----- Original Message ----- 
  From: Mohammed Faisal 
  To: ms_access@yahoogroups.com 
  Sent: Monday, 11 December, 2006 16:50
  Subject: RE: [ms_access] ANY TIPS PLS


  Thank you Liveson for your reply

  Actually I want to make a query in that query some time I
  want to search first two factors like name and age

  In next time I want to search for the entire field, in next time any
  three field like that........ Depend on situation

  Now I want to know whether it is possible to make a single query for
  entire my need.

  Thanks & Regards,

  Muhamed Faisal.

  Riyadh

  _____ 

  From: L Tumbulu [mailto:[EMAIL PROTECTED] 
  Sent: December 11, 2006 5:13 PM
  To: ms_access@yahoogroups.com
  Subject: Re: [ms_access] ANY TIPS PLS

  Hi Mohammed

  What I know is that you can concatenate the WHERE clause of your query
  with "AND". For example you can write
  "SELECT * FROM YourTable WHERE FullName = "XXX" And Age = X And Sex =
  "X" etc etc. In this case the result will be only records that meet all
  the conditions.

  I'm not very sure if this is what you are looking for

  Thanks

  Liveson

  ----- Original Message ----- 
  From: Mohammed Faisal 
  To: ms_access@yahoogroups.com <mailto:ms_access%40yahoogroups.com> 
  Sent: Monday, 11 December, 2006 07:29
  Subject: [ms_access] ANY TIPS PLS

  DEARS FRIENDS,

  I HAVE A DOUBT WHEN WORKING THROUG THE MS ACESS, HOPE ANY BODY
  MAY HELPS ME .THANKS IN ADVANCE

  I WANT TO MAKE A QUERY FOR FIVE FIELD FOR EXAMPLE A HAVE FIELD LIKE
  NAME, AGE, SEX, QUALIFICATION IN ONE TABLE

  NOW FIRST I WANT TO MAKE A QUERY FOR 

  1. WHERE NAME="X" AND AGE="X"

  2. WHERE SEX="X" AND NAME="X"

  3. WHERE NAME ="X" AND QUALIFICATION="X"

  4. WHERE AGE="X" AND SEX="X"

  5. WHERE AGE="X" AND QUALIFICATION="X"

  6. WHERE SEX="X" AND QUALIFIATION="X"

  WHERE "X" IS ANY DATA IN ANY FIELD

  NOW I WANT TO KNOW THAT WHETHER THIS 6 QUERY CAN INTERGRATED TOGETHER TO
  FROM A SINGLE QUERY

  Thanks & Regards,

  Muhamed Faisal.

  [Non-text portions of this message have been removed]

  [Non-text portions of this message have been removed]

  [Non-text portions of this message have been removed]



   

[Non-text portions of this message have been removed]