searching tables

  • Thread starter Thread starter Erik P
  • Start date Start date
E

Erik P

I am trying to create a form that well allow users to
input informnation to search the table that i have
created. for example. i have a table with the columns as
follows i have date/creator/window description/window
quanty/transom above/house/.... and i would like to creat
a form that would beable to input information into each
box to search the table i have created and then it to show
in a report or a subdata sheet. I hope this makes sence.
thanks so much.
 
Erik P said:
I am trying to create a form that well allow users to
input informnation to search the table that i have
created. for example. i have a table with the columns as
follows i have date/creator/window description/window
quanty/transom above/house/.... and i would like to creat
a form that would beable to input information into each
box to search the table i have created and then it to show
in a report or a subdata sheet. I hope this makes sence.
thanks so much.
Click to expand...

Create a form and create text boxes on the form. Put as many as you have
fields that you want the user to search on.(i.e. Date, Creator, &
WindowQuantity).

Place a button on the form called searchButton.

In the OnClick event put this:

Dim strCriteria As String
Const strLogicalOperator = "AND"

strCriteria = " "

If IsDate(Me.Date) Then ' a better IsNull
strCriteria = "[Date] = #" & Format$(Me.Date, "mm/dd/yyyy") & "#"
End If

If Len(Trim(Me.creator & vbNullString)) > 0 Then ' a better IsNull
strCriteria = strCriteria & " AND [Creator] = '" & Trim(Me.creator)
& "'"
End If

If IsNumeric(Me.windowquanty) Then ' a better IsNull
strCriteria = strCriteria & " AND [windowquanty] = " &
Me.windowquanty
End If

'etc
'etc
' do this for as many fields you have on your form
'
'

' check to see if the user entered anything

If Not Len(Trim(strCriteria)) > 0 Then
MsgBox "Please enter in a search criteria."
Else

strCriteria = RemovePrefix(strLogicalOperator, strCriteria)
DoCmd.Close ' close this form
DoCmd.OpenForm "YourFormName", , , strCriteria

.........
here's the code for the "RemovePrefix" function:

Public Function RemovePrefix(strPrefix As String, strEntireString As String)
As String
Dim strReturnedString As String, intStringLength As Integer, intPrefixLength
As Integer
' strips off the prefix from the string , if present
intPrefixLength = Len(strPrefix)
strReturnedString = Trim(strEntireString)
intStringLength = Len(strReturnedString)
If intPrefixLength < intStringLength Then
If Left(strReturnedString, intPrefixLength) = strPrefix Then
strReturnedString = Right(strReturnedString, intStringLength -
intPrefixLength)
strReturnedString = Trim(strReturnedString)
End If
End If

RemovePrefix = strReturnedString
End Function
 
Back
Top