search query based on multiplecomboboxes

[token_remedie]

I can't get this to work and I've tried several methods. ok so basically I'm trying to create a query or report (I don't care which) based on criteria filled out from several comboboxes. The tables fields are:
Asset Number, Location Name, Asset Description, Serial no, Invent no, Location, Manufacturer and deployment schedule.

The search query needs to be able to pull from any or all of these but if some are null that's cool too. The biggest issue I've had so far is the null values. so far I've tried:

* Putting forms![searchform]![cmbassetnum] in the criteria and that works for one, but when I do it to all of them it breaks it.
* tried if else like this:
f Not IsNull(Me![Cmbassetnum]) Then
strWhere = "([searchquery]![asset number] =" & _
[Forms]![searchform]![Cmbassetnum] & ") And "
Else
GoTo continue
End If

If Not IsNull(Me![cmbmanuf]) Then
strWhere = strWhere & "([searchquery]![manufacturer] =" & _
Me![cmbmanuf] & ") And "
Else: GoTo continue
lngLen = Len(strWhere)
If lngLen = 0 Then
MsgBox "Please enter search criteria!"
Else
strWhere = Left$(strWhere, lngLen)
stDocName = "rptsearchquery"
DoCmd.OpenReport stDocName, acPreview, , strWhere
End If

that's not the entire code but you get what I've done....

tried this:
Private Sub Cmbassetnum_AfterUpdate()
stDocName = "searchquery"
DoCmd.OpenQuery stDocName, acNormal, acEdit
End Sub

and that was off track and didn't work anyway

Any help would be greatly appreciated, and I've searched this forum extensively which is how I've gotten this far but I'm just missing something somewhere.

 

[GinaWhipp]

From Allen Browne...
http://allenbrowne.com/ser-62.html

The above should help point you in the right direction.

 

[token_remedie]

this is exactly what I wanted to do I just couldn't work out how to do it and I was on the right track looking at old code. Anyway there's something just not right and I'm sure it's because I've been staring at it too long.
here's what I've done with your code, if you could point me in the right direction that would be awesome:

Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates in a JET query string.

'***********************************************************************
'Look at each search box, and build up the criteria string from the non-blank ones.
'***********************************************************************
'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.cmbinvent) Then
strWhere = strWhere & "([CFT] = " & Me.cmbinvent & ") AND "
End If

'Another text field example. Use Like to find anywhere in the field.
If Not IsNull(Me.txtFilterMainName) Then
strWhere = strWhere & "([Location] Like ""*" & Me.txtFilterMainName & "*"") AND "
End If

'Number field example. Do not add the extra quotes.
If Not IsNull(Me.Cmbassetnum) Then
strWhere = strWhere & "([AssetNum] = " & Me.Cmbassetnum & ") AND "
End If


It sort of works...
it shows the continuous records when the form loads but won't filter properly, when i hit the filter it only works if i toggle filter off first to show all records and then when i search for an asset number it returns nothing.

Also is it possible to capture the returned results and create a printable report from the results returned on this form?

 

[GinaWhipp]

Yes you can get it in a report but a question first, is that all the code you used? Like there is no other portion you didn't post here?

 

[token_remedie]

I think I've worked out the report part of it, I'll just docmd.open,,,Dim args that's cool

but no there's more it's pretty much your code - why reinvent the wheel when I have a superfast low profile racing wheel here right?

Private Sub cmdFilter_Click()
'Purpose: Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both inclusive. _
Start date only = all dates from this one onwards; _
End date only = all dates up to (and including this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates in a JET query string.

'***********************************************************************
'Look at each search box, and build up the criteria string from the non-blank ones.
'***********************************************************************
'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.cmbinvent) Then
strWhere = strWhere & "([CFT] = " & Me.cmbinvent & ") AND "
End If

'Another text field example. Use Like to find anywhere in the field.
If Not IsNull(Me.txtFilterMainName) Then
strWhere = strWhere & "([Location] Like ""*" & Me.txtFilterMainName & "*"") AND "
End If

'Number field example. Do not add the extra quotes.
If Not IsNull(Me.Cmbassetnum) Then
strWhere = strWhere & "([AssetNum] = " & Me.Cmbassetnum & ") AND "
End If


'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's Filter.
'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ") to remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the " AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

 

[GinaWhipp]

Hmmm, not my code Allen Browne's code. Let's try this...

If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the " AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.FilterOn = False
DoEvents
Me.Filter = strWhere
Me.FilterOn = True
End If

I added the bold parts... thinking we need to clear the Filter then apply the Filter. Make sense?

 

[token_remedie]

if i enter the invent no it pops up asking for an asset number now and vice versa
no searches work still. the combo boxes are unbound and the display boxes under results are bound to the deployed table which is what I'm searching.
when the form opens it displays the 44754 records but wont search by any criteria, if i do get it to search (by entering details in the second popup its throwing) then it returns nothing to the form

definitely made sense what you suggested though it's gotta be something really simple right?

 

[token_remedie]

I worked it out, I put table.field:
If Not IsNull(Me.txtFilterMainName) Then
strWhere = strWhere & "(deployed.location Like ""*" & Me.txtFilterMainName & "*"") AND "
End If

this returns a result
thank you so much for your help... although I'm not done you helped me through a massive hurdle, and I think I fell in love with you a little haha

 

[GinaWhipp]

Glad to help! On to the next hurdle...