Search form with Passthrough query

  • Thread starter Thread starter Island_Dude
  • Start date Start date
I

Island_Dude

Hello All,

I'm trying to build a search form I'm having problem with concatenate AND...

I want to know where i should concatenate AND. the Input form or the result
form.

Any help will be greatly appreciated.

Thanks.
 
Island_Dude,
Please find a better way to explain your needs. This question only raises
questions.
 
I am building a search form. The form has 3 textbox controls (txtProject,
txtFiling, txtReport) into which users can enter criteria, and a click on a
'Search' button. I figured out how to make it work for a single criteria.
However, I'm having difficulty to concatenate all 3 criteria strings.

How should I properly concatenate all 3 criteria?

Also, if a user leaves one or more textbox controls empty, can this create
any problems when I concatenate the strings?

*Please note the data is not stored in Access table, I use passthrough query
to access data stored in Oracle.*

Thanks
 
It would help to see your current code, so that we don't have to make up
field and table names...

Generically, you'd build the Where clause along the lines of:

Dim strWhere As String

strWhere = vbNullString
If Len(Me.txtProject & "") > 0 Then
strWhere = strWhere & "ProjectId = " & Me.txtProject & " AND "
End If
If Len(Me.txtFiling & "") > 0 Then
strWhere = strWhere & "FileId = " & Me.txtFiling & " AND "
End If
If Len(Me.txtReport & "") > 0 Then
strWhere = strWhere & "ReportId = " & Me.txtReport & " AND "
End If

If Len(strWhere) > 0 Then
strWhere = "WHERE " & Left$(strWhere, Len(strWhere) - 5)
End If

This assumes that all of the fields are numeric. If they're text, you need
to include quotes:

strWhere = strWhere & "ProjectId = " & Chr$(34) & Me.txtProject &
Chr$(34) & " AND "
 
Hello Doug,

thanks for the suggestion, I finally got it to work a little bit. The only
problem now, when I search with a single criteria the other 2 are also
showing in the query.

For example if I'm searching using only ProjectID criteria,.... FileID and
ReportID are also showing in the query:

"Select...FROM ...WHERE... AND ProjectID = 555555 AND FileID =
AND ReportID = "


Any suggestion!

Thanks for your help
 
Sounds as though you didn't use the code I suggested.

If Len(Me.txtFiling & "") > 0 prevents the FiledId part of the criteria
from being added if there's nothing in the textbox.
 
Hello Doug,

It's making more sense to me now...

the strWhere string is dynamically built behind the Search_Input form, do you
have any suggestion on how to pass the strWhere string to the Search_Result
form.

Thanks for helping!
 
Since you're dealing with a pass-through query, you have to rewrite the SQL
of the query anyhow: there's no other way to have it know about variables or
form controls.

You can easily rewrite the SQL of a query using:

CurrentDb().QueryDefs("MyPassthroughQuery").SQL = strSQL
 
Hello Doug,

Thank you for helping, I finally got it to work.

I use the framework you suggested to write the SQL passthrough query, now
everything works like a charm.

This is what I did for the strWhere behind the frmSearch_Result:
..........
Dim strWhere As string

If Len(Forms!frmSearch_Input!txtProject & "") > 0 Then
strWhere = strWhere & "ProjectID = '" & Forms!frmSearch_Input!txtProject.
Value & "' AND "
End If

If Len(Forms!frmSearch_Input!txtFiling & "") > 0 Then
strWhere = strWhere & "FileID = '" & Forms!frmSearch_Input!txtFiling.
Value & "' AND "
End If

If Len(Forms!frmSearch_Input!txtReport & "") > 0 Then
strWhere = strWhere & "ReportID = '" & Forms!frmSearch_Input!txtReport.
Value & "' AND "
End If

If Len(strWhere) > 0 Then
strWhere = " AND " & Left$(strWhere, Len(strWhere) - 5)
End If

...........

Thank you so much, I could not have figured it out without your help!
 
Back
Top