Dynamically Build Query

[Deleted member 73419]

Hello guys,
What I want to do is build an SQL query from a form, but so far I am having problems where there is no data. Let me elaborate a little.

I have a form with three textboxes and I want to build an SQL query from the data within these textboxes. So far I have this:

str = "SELECT tblNumberData.EntryDate, tblNumberData.Author, tblNumberData.Description FROM tblData, tblNumberData WHERE (((tblNumberData.EntryDate) Like '" & txtEntryDate & "') AND ((tblNumberData.Author) Like '" & txtAuthor & "') AND ((tblNumberData.Description) Like '" & txtDescription & "'));"

The problem is that if I fill in the date and author fields the SQL query that is produced searches where the date is 'xyz' AND the author is 'AN Other' AND the description is blank. What I really need is to ignore the description if nothing is filled in and only search on the date and author fields.

Can anyone please advise where I am going wrong?

Thanks

 

[pbaldy]

Test the description control (If/Then), and only add it to the SQL if filled in.

 

[odin1701]

There is a way to do this, it's a bit involved. I am working on something.

 

[raskew]

Hi -

Checkout this post.

Bob

 

[odin1701]

I would do something like this.

Need an array to store strings, and a counter to store how many strings we are putting in the array.

Dim strWHERE() As String
Dim intCount As Integer
Dim sqlWHERE As String

Name the text boxes identically, but change a number.

So like this:

txtBox1
txtBox2
txtBox3

You need to know what text box is for what test.

Then do this:

For i = 1 to 3

If Not Nz(Me("txtBox" & i).Value, 0) = 0 Then

	Select Case i

	Case 1:

		intCount = intCount + 1
		ReDim Preserve strWHERE(1 to intCount)
		strWHERE(intCount) = "(tblNumberData.EntryDate) Like '" & Me("txtBox" & i).Value & "'"
	
	Case 2:

		intCount = intCount + 1
		ReDim Preserve strWHERE(1 to intCount)
		strWHERE(intCount) = "(tblNumberData.Author) Like '" & Me("txtBox" & i).Value & "'"

	Case 3:

		intCount = intCount + 1
		ReDim Preserve strWHERE(1 to intCount)
		strWHERE(intCount) = "(tblNumberData.Description) Like '" & Me("txtBox" & i).Value & "'"

	End Select

End If

Next i

Once that is complete, you will have an array that has each item stored in it, and that array is only going to be as big as the number of items they entered. So if they didn't put anything, it would skip that text box and move to the next. You can do more text boxes by adding more, and adding to the For...Next statement to increase the iterations. Or you could have it go from 1 to a count of how many text boxes, but not really necessary.

Okay so from here you need to get the actual WHERE clause in your SQL statement. You can do something like this right after the code above:

Select Case intCount

    Case 1:

        sqlWHERE = "WHERE " & strWHERE(1) & ";"
        
    Case 2:
    
        sqlWHERE = "WHERE " & strWHERE(1) & " AND " & strWHERE(2) & ";"
        
    Case 3:
    
        sqlWHERE = "WHERE " & strWHERE(1) & " AND " & strWHERE(2) & " AND " & strWHERE(3) & ";"
    
    Case Else
    
        MsgBox "You must enter at least one criteria to search for!"
        Exit Sub
    
End Select
    
MsgBox sqlWHERE

So if nothing is entered, you should see the message that they have to enter one criteria. If you enter 1, 2 or 3 items, you will see the SQL WHERE clause pop up in a message box.

Now, this may not be the best way of doing it, but it works. You just have your regular SQL string and add the sqlWHERE string onto the end of it, or if the sqlWHERE is null you can omit it if you want.

 

[Thinh]

Check this code out. It should take care of all the situation that may rise.

Dim strsql as string
dim status as integer

If len(me.txtEntryDate.value & "") <> 0 then status = status +1
If len(me.txtAuthor.value & "") <> 0 then status = status +2
If len(me.txtDescription.value & "") <> 0 then status = status +4

StrSql = "SELECT tblNumberData.EntryDate, tblNumberData.Author, tblNumberData.Description FROM tblData, tblNumberData"

Select case status
case 1

StrSql = StrSql & " WHERE tblNumberData.EntryDate Like '" & txtEntryDate & "'"​

case 2

StrSql = StrSql & " WHERE tblNumberData.Author Like '" & txtAuthor & "'"​

case 3

StrSql = StrSql & " WHERE tblNumberData.EntryDate Like '" & txtEntryDate & "' AND tblNumberData.Author Like '" & txtAuthor & "'"​

case 4

StrSql = StrSql & " WHERE tblNumberData.Description Like '" & txtDescription & "'"​

case 5

StrSql = StrSql & " WHERE tblNumberData.EntryDate Like '" & txtEntryDate & "' AND tblNumberData.Description Like '" & txtDescription & "'"​

case 6

StrSql = StrSql & " WHERE tblNumberData.Author Like '" & txtAuthor & "' AND tblNumberData.Description Like '" & txtDescription & "'"​

case 7

StrSql = StrSql & " WHERE tblNumberData.EntryDate Like '" & txtEntryDate & "' AND tblNumberData.Author Like '" & txtAuthor & "' AND tblNumberData.Description Like '" & txtDescription & "'"​

case else
msgbox "Please select atleast one field"
End select

I may look complex but it should work like a charm. try it.

 

[vbaInet]

odin1701: This can be done in far less lines of codes and even more understood with less. You don't even need an array. It would depend on whether the poster wants to build the string for later use (such as forming the string after a command button is pressed) or for changing an object's record source as data is being entered. I presume is the former.

 

[odin1701]

odin1701: This can be done in far less lines of codes and even more understood with less. You don't even need an array. It would depend on whether the poster wants to build the string for later use (such as forming the string after a command button is pressed) or for changing an object's record source as data is being entered. I presume is the former.

I'm sure it can. This is what I could come up with on short notice though, and it would work.

I would certainly be interested in other methods.

 

[Thinh]

odin71 review my method and let me know

 

[pbaldy]

I'm with vbaInet; the arrays are overkill. I'd do something like this air code:

Dim strWhere As String

If Len(Me.txtResNum & vbNullString) > 0 Then
  strWhere = strWhere & "ResNum = " & Me.txtResNum & " And "
End If

'Similar test for each optional control

If Len(strWhere) = 0 Then
  'nothing was filled in, do whatever
Else
  strWhere = Left(strWhere, Len(strWhere) - 5)
  'add to SQL string or whatever
End If

 

[pbaldy]

odin71 review my method and let me know

Again, in my opinion unnecessarily complicated and not dynamic. If they add a 4th field, you'll have to add every possible combination to the code. I'll have to add one more If/Then block.

 

[vbaInet]

Alright odin, it's always good to share ideas. Maybe not quite so short afterall:
---------------------------------------
Declare the SELECT part of your sql string as a constant (at the declarations section)

const strSelect = "SELECT tblNumberData.EntryDate, tblNumberData.Author, tblNumberData.Description FROM tblData, tblNumberData "

Notice the space at the end, don't remove it.

Function:

Dim strSql as String, strWhere as String, isFiltered as Boolean

strSql = strSelect
isFiltered = False

' Build your WHERE string
If txtEntryDate.value & "" <> "" Then
     strWhere = "WHERE tblNumberData.EntryDate Like '" & txtEntryDate.value & "' "
     isFiltered = True
End If

If txtAuthor.value & "" <> "" Then 
    If isFiltered = True Then
        strWhere = strWhere & "AND tblNumberData.Author Like '" & txtAuthor.value & "' "
        isFiltered = True
    Else
        strWhere = "WHERE tblNumberData.Author Like '" & txtAuthor.value & "' "
    End If
End If

 If txtDescription.value & "" <> "" Then 
     If isFiltered = True Then
         strWhere = strWhere & "AND tblNumberData.Description Like '" & txtDescription.value & "' "
     Else
         strWhere = "WHERE tblNumberData.Description Like '" & txtDescription.value & "' "
     End If
 End If

' Finalise the build
strSql = strSql & strWhere & ";"

NB: There may be one or two typos as I wrote this directly on here.

 

[odin1701]

I see...I think I often times overcomplicate things.

Am I correct in assuming that adding the vbNullString will eliminate errors with the Len() function should it actually be a null value?

 

[vbaInet]

I see...I think I often times overcomplicate things.

Am I correct in assuming that adding the vbNullString will eliminate errors with the Len() function should it actually be a null value?

You can say it initialises your control's value, so yes in retrospect it would eliminate errors related to null when used with Len or checking against empty string. I think pbaldy has something on his site explaining this in detail.

 

[pbaldy]

Am I correct in assuming that adding the vbNullString will eliminate errors with the Len() function should it actually be a null value?

Yes; concatenating a zero length string (ZLS) with a Null will result in a ZLS. There's a slight performance advantage to using vbNullString in code rather than "", but either would work.

 

[vbaInet]

Ah yes, always advisable to use vb constants right pbaldy?

 

[SOS]

Ah yes, always advisable to use vb constants right pbaldy?

Well, only if you are using VBA. In queries and control sources you would still need the "" over vbNullString because it isn't VBA.

 

[vbaInet]

Well, only if you are using VBA. In queries and control sources you would still need the "" over vbNullString because it isn't VBA.

Lol yeah. I used to use that when I was doing Java stuff.

 

[pbaldy]

Ah yes, always advisable to use vb constants right pbaldy?

A millisecond here, a millisecond there, pretty soon we're talking measurable speed increases.

BTW, I think you want this changed in your middle If/Then block:

isFiltered = False

which I think messes up the next test. One reason I prefer my method is not having to test...I'm lazy.

 

[vbaInet]

Haha absolutely. I always try to optimise my code whereever possible.

Oh, good spot. Redundant. Thanks