[RESOLVED] SQL [search form] (MS Access 03)

Hello there,

i'm working on a lil search application using VBA 03 and MySQL.

the search criterias are name, age, gender, telephone, kids age and what not.

with regards to the single SELECT statement, a friend commented that the most important criteria should be added right at the end (right) and least important critera would have to be at the start (left)

VB Code:

rst.Open "SELECT * FROM [Contact 2] WHERE [Name] " & sName & "  AND [Telephone] " & sTel & " AND [18 Kids] " & sKids & " AND [13 Gender] " & sGender & " AND [ActAge] " & Combo8.ItemData(Combo8.ListIndex) & " " & sAge & "", CurrentProject.AccessConnection, adOpenDynamic, adLockOptimistic

even after having made arrangements in the order, the search still doesn't work as it is intended.

perhaps someone could advice me on how to create a proper search application or write a proper select statement to do searching.

thank you

Astro

Shouldn't it be:

WHERE Name = " & sName & " AND ... etc.

"WHERE" implies a conditional operator to relate the field with the value.

Hey Webtest,

Appreciate that you took the time to look through.

The operators are along with the string (sName), i left it out in the SQL statement because there could be conditions such as IS NULL/IS NOT NULL.

If i'm not mistaken i can't do a = NULL/NOT NULL =)

Thank You

Astro

The only obvious error is the use of ItemData, which only contains numbers - as such you will get something like "AND [ActAge] 37", which is not valid.

Rather than running the SQL directly, put it into a string variable first so that you can see what your errors are. If you can't see the error, post it here.


I disagree with the method you have used, as it allows too much room for error. I would use separate controls for the operators (= etc), so that you can validate the input appropriately. I would also build the SQL in stages, so that fields can be ignored if you don't care what the values are.

Hey geek,

itemdata contains operators such as =, >, between... etc.

=)

Thank You

Astro

Really? That's a surprise, as ItemData in list controls is normally restricted to numbers only. So what does the List contain? (or does ItemData = List in an Access combo?)

Anyhoo, this is the kind of code that I would use:

VB Code:

Dim sSQL as String
  sSQL = ""
  If sName <> "" Then
    sSQL = sSQL & "AND [Name] " & sName
  End If
  If sTel <> "" Then
    sSQL = sSQL & "AND [Telephone] " & sTel
  End If   
...
  sSQL = "SELECT * FROM [Contact 2] WHERE " & Mid(sSQL, 5)
  rst.Open sSQL, CurrentProject.AccessConnection, adOpenDynamic, adLockOptimistic

If you are using criteria with strings then you will need to place single quotes around it, therefore your item data if using it in your method will need to be..

= 'NameOf'

Hey si_the_geek

On combobox single click event (where operators are selected).

"combo.itemdata(combo.listindex)" reads the exact selected operator,

its similar to .selected (which i've seen and also not too familiar with)...

the list contains operators like "=, <, >" and what not.

i picked this up by experimenting. Thanks for posting btw.

=)

Astro

Hey dannymking,

yea i've actually got em in the string, ill put up some codes in the next post for everyone. Thanks!

Astro

Hello guys,

Here's a portion of my search application.

The application user is pretty fussy and has very inconsistent data in her tables....hmm.. its either that or i'm totally noob.

I'm doing this as part of my internship, a full application which allows user to prepare her questionnaires (research surveys) through an interface.

Search applys to searching for prospective participants in her records which the questionnaires would be sent through e-mail, upon having received the filled questionnaires, data goes right into the tables.

No allowance, salary... nothing =P -slavery-, they were also expecting an online version in JSP all in a good 14 weeks along with this. (I didn't even know any of these languages! + VBA) I still don't see how my lecturers fit me in?

End with the rants =)

shown below; are the general conditions such as

VB Code:

Dim sN() As String
Dim sName As String
 
If Me.CheckAge = False Then
 
Rem Age IS NOT NULL
If Combo8.ItemData(Combo8.ListIndex) = "between" Then
sAge = Me.TextAge
sN = Split(sAge, " ")
sAge = "AND [ActAge] Between '" & sN(0) & "'" & "And" & "'" & sN(2) & "' AND [ActAge] IS NOT NULL"
MsgBox "Age " & sAge
Else
If Me.TextAge <> "" Then
sAge = "AND [ActAge] " & Combo8.ItemData(Combo8.ListIndex) & " '" & Me.TextAge & "'"
MsgBox "Age " & sAge
Else
sAge = ""
MsgBox "Age " & sAge
End If
End If
 
Else
 
Rem Age IS NULL
If Combo8.ItemData(Combo8.ListIndex) = "between" Then
sAge = Me.TextAge
sN = Split(sAge, " ")
sAge = "AND [ActAge] Between '" & sN(0) & "'" & "And" & "'" & sN(2) & "' OR [ActAge] IS NULL"
MsgBox "Age " & sAge
Else
If Me.TextAge <> "" Then
sAge = "AND [ActAge] " & Combo8.ItemData(Combo8.ListIndex) & " '" & Me.TextAge & "' OR [ActAge] IS NULL"
MsgBox "Age " & sAge
Else
sAge = "AND [ActAge] IS NOT NULL AND [ActAge] IS NULL"
MsgBox "Age " & sAge
End If
End If
End If

the entire string (sAge) is pretty self contained with tablename & conditions (pretty well hardcode), all of it goes right into the sql.

shown below; where the entire string applys in the select statement.

VB Code:

rst.Open "SELECT * FROM [Contact 2] WHERE [Name] LIKE '%" & sName & "%' And [Name] NOT LIKE '%DELETE%' " & sTel & " " & sKids & " " & sKidsAge & " " & sRace & " " & sGender & " " & sAge & "", CurrentProject.AccessConnection, adOpenDynamic, adLockOptimistic

i'm all ears on advices, it's all by instinct and im not programming inclined at all.

if it is possible perhaps you guys could advice me on how to write up a good search application.

Thanks Man!

Astro

Ah yes, you are basically doing my suggestion, so that looks ok... except for this:

sAge = "AND [ActAge] IS NOT NULL AND [ActAge] IS NULL"

ActAge cannot be Null and Not Null at the same time, so you will get no data.

..and your OR's need to be in brackets (see below).

Your code could also do with a bit of re-arranging, like this:

VB Code:

Dim sN() As String
Dim sName As String
 
If Me.CheckAge = False Then
  Rem Age IS NOT NULL
  If Combo8.ItemData(Combo8.ListIndex) = "between" Then
    sAge = Me.TextAge
    sN = Split(sAge, " ")
    sAge = "AND [ActAge] Between '" & sN(0) & "'" & "And" & "'" & sN(2) & "' AND [ActAge] IS NOT NULL"
  Else
    If Me.TextAge <> "" Then
      sAge = "AND [ActAge] " & Combo8.ItemData(Combo8.ListIndex) & " '" & Me.TextAge & "'"
    Else
      sAge = ""
    End If
  End If
 
Else
  Rem Age IS NULL
  If Combo8.ItemData(Combo8.ListIndex) = "between" Then
    sAge = Me.TextAge
    sN = Split(sAge, " ")
    sAge = "AND ([ActAge] Between '" & sN(0) & "'" & "And" & "'" & sN(2) & "' OR [ActAge] IS NULL)"
  Else
    If Me.TextAge <> "" Then
      sAge = "AND ([ActAge] " & Combo8.ItemData(Combo8.ListIndex) & " '" & Me.TextAge & "' OR [ActAge] IS NULL)"
    Else
** this should be changed!:  (blank maybe?)
      sAge = "AND [ActAge] IS NOT NULL AND [ActAge] IS NULL"
    End If
  End If
End If
MsgBox "Age " & sAge

MIGHTY COOL si_the_geek! MIGHTY MIGHTY COOL!!! the 'OR', the brackets ().

Took away bunch of problems i've been staring at!

THANKS! hehe a couple more fields to go!

=)

Astro