Checkboxes Need Null Value? Search Doesn't Work As Intended (Clearly User Error LOL)

[rawlstonb]

The goal here is to be able to check any amount of checkboxes and return only values that match the sum of the strings and the -1 checked values... unchecked values on this form have no meaning to the query. I hypothesize that they're polluting the query with 0 values that won't return a record unless ALL the checkboxes that apply to said record are checked. I tried putting "Null" in the default of the checkboxes, but then it returns nothing at all regardless what is checked or typed...

Open to any solutions on how to fix this, tho I imagine it's pretty simple code I screwed up... I've spent an inordinate amount of time beating my head against the same the wall here and I can't find any examples of excluding the zero on Google...

Here's the SQL of the query... (it's pretty much just two repeated strings of the same statement... one for each text box and one for each check box)

SELECT tblRouterData.*, tblRouterData.[SO#], tblRouterData.LineItem, tblRouterData.Customer, tblRouterData.Plant, tblRouterData.[PN#], tblRouterData.[Drawing#], tblRouterData.PartType, tblRouterData.Class, tblRouterData.Description, tblRouterData.Quantity, tblRouterData.StartDate, tblRouterData.FinishDate, tblRouterData.SubmitDate, tblRouterData.Author, tblRouterData.Machine, tblRouterData.Drill, tblRouterData.Weld, tblRouterData.Install, tblRouterData.Lapping, tblRouterData.Backfile, tblRouterData.Polish, tblRouterData.Balance, tblRouterData.SubMachine, tblRouterData.SubCoat, tblRouterData.SubGrind, tblRouterData.SubStress, tblRouterData.SubHT, tblRouterData.SubBalance, tblRouterData.SubPerfTest, tblRouterData.SubMatlTest, tblRouterData.PT, tblRouterData.UT, tblRouterData.RT, tblRouterData.Hydro, tblRouterData.FlowCal, tblRouterData.ANI, tblRouterData.Stamp, tblRouterData.Comments
FROM tblRouterData
WHERE (((tblRouterData.[SO#]) Like "*" & [Forms]![frmRouterSearch]![txtSO#Search] & "*")
AND ((tblRouterData.LineItem) Like "*" & [Forms]![frmRouterSearch]![txtLineItemSearch] & "*")
AND ((tblRouterData.Customer) Like "*" & [Forms]![frmRouterSearch]![cboCustomerSearch] & "*")
AND ((tblRouterData.Plant) Like "*" & [Forms]![frmRouterSearch]![cboPlantSearch] & "*")
AND ((tblRouterData.[PN#]) Like "*" & [Forms]![frmRouterSearch]![txtPart#Search] & "*")
AND ((tblRouterData.[Drawing#]) Like "*" & [Forms]![frmRouterSearch]![txtDrawing#Search] & "*")
AND ((tblRouterData.PartType) Like "*" & [Forms]![frmRouterSearch]![cboPartTypeSearch] & "*")
AND ((tblRouterData.Class) Like "*" & [Forms]![frmRouterSearch]![txtClassSearch] & "*")
AND ((tblRouterData.Description) Like "*" & [Forms]![frmRouterSearch]![txtDescriptionSearch] & "*")
AND ((tblRouterData.Machine)=IIf([Forms]![frmRouterSearch]![chkMachineSearch]=-1,([tblRouterData].[Machine]),IIf([Forms]![frmRouterSearch]![chkMachineSearch]=0,(Not ([tblRouterData].[Machine])=[tblRouterData].[Machine]),True)))
AND ((tblRouterData.Drill)=IIf([Forms]![frmRouterSearch]![chkDrillSearch]=-1,([tblRouterData].[Drill]),IIf([Forms]![frmRouterSearch]![chkDrillSearch]=0,(Not ([tblRouterData].[Drill])=[tblRouterData].[Drill]),True)))
AND ((tblRouterData.Weld)=IIf([Forms]![frmRouterSearch]![chkWeldSearch]=-1,([tblRouterData].[Weld]),IIf([Forms]![frmRouterSearch]![chkWeldSearch]=0,(Not ([tblRouterData].[Weld])=[tblRouterData].[Weld]),True)))
AND ((tblRouterData.Install)=IIf([Forms]![frmRouterSearch]![chkInstallSearch]=-1,([tblRouterData].[Install]),IIf([Forms]![frmRouterSearch]![chkInstallSearch]=0,(Not ([tblRouterData].[Install])=[tblRouterData].[Install]),True)))
AND ((tblRouterData.Lapping)=IIf([Forms]![frmRouterSearch]![chkLappingSearch]=-1,([tblRouterData].[Lapping]),IIf([Forms]![frmRouterSearch]![chkLappingSearch]=0,(Not ([tblRouterData].[Lapping])=[tblRouterData].[Lapping]),True)))
AND ((tblRouterData.Backfile)=IIf([Forms]![frmRouterSearch]![chkBackfileSearch]=-1,([tblRouterData].[Backfile]),IIf([Forms]![frmRouterSearch]![chkBackfileSearch]=0,(Not ([tblRouterData].[Backfile])=[tblRouterData].[Backfile]),True)))
AND ((tblRouterData.Polish)=IIf([Forms]![frmRouterSearch]![chkPolishSearch]=-1,([tblRouterData].[Polish]),IIf([Forms]![frmRouterSearch]![chkPolishSearch]=0,(Not ([tblRouterData].[Polish])=[tblRouterData].[Polish]),True)))
AND ((tblRouterData.Balance)=IIf([Forms]![frmRouterSearch]![chkBalanceSearch]=-1,([tblRouterData].[Balance]),IIf([Forms]![frmRouterSearch]![chkBalanceSearch]=0,(Not ([tblRouterData].[Balance])=[tblRouterData].[Balance]),True)))
AND ((tblRouterData.SubMachine)=IIf([Forms]![frmRouterSearch]![chkSubMachineSearch]=-1,([tblRouterData].[SubMachine]),IIf([Forms]![frmRouterSearch]![chkSubMachineSearch]=0,(Not ([tblRouterData].[SubMachine])=[tblRouterData].[SubMachine]),True)))
AND ((tblRouterData.SubCoat)=IIf([Forms]![frmRouterSearch]![chkSubCoatSearch]=-1,([tblRouterData].[SubCoat]),IIf([Forms]![frmRouterSearch]![chkSubCoatSearch]=0,(Not ([tblRouterData].[SubCoat])=[tblRouterData].[SubCoat]),True)))
AND ((tblRouterData.SubGrind)=IIf([Forms]![frmRouterSearch]![chkSubGrindSearch]=-1,([tblRouterData].[SubGrind]),IIf([Forms]![frmRouterSearch]![chkSubGrindSearch]=0,(Not ([tblRouterData].[SubGrind])=[tblRouterData].[SubGrind]),True)))
AND ((tblRouterData.SubStress)=IIf([Forms]![frmRouterSearch]![chkSubStressSearch]=-1,([tblRouterData].[SubStress]),IIf([Forms]![frmRouterSearch]![chkSubStressSearch]=0,(Not ([tblRouterData].[SubStress])=[tblRouterData].[SubStress]),True)))
AND ((tblRouterData.SubHT)=IIf([Forms]![frmRouterSearch]![chkSubHTSearch]=-1,([tblRouterData].[SubHT]),IIf([Forms]![frmRouterSearch]![chkSubHTSearch]=0,(Not ([tblRouterData].[SubHT])=[tblRouterData].[SubHT]),True)))
AND ((tblRouterData.SubBalance)=IIf([Forms]![frmRouterSearch]![chkSubBalanceSearch]=-1,([tblRouterData].[SubBalance]),IIf([Forms]![frmRouterSearch]![chkSubBalanceSearch]=0,(Not ([tblRouterData].[SubBalance])=[tblRouterData].[SubBalance]),True)))
AND ((tblRouterData.SubPerfTest)=IIf([Forms]![frmRouterSearch]![chkSubPerfTestSearch]=-1,([tblRouterData].[SubPerfTest]),IIf([Forms]![frmRouterSearch]![chkSubPerfTestSearch]=0,(Not ([tblRouterData].[SubPerfTest])=[tblRouterData].[SubPerfTest]),True)))
AND ((tblRouterData.SubMatlTest)=IIf([Forms]![frmRouterSearch]![chkSubMatlTestSearch]=-1,([tblRouterData].[SubMatlTest]),IIf([Forms]![frmRouterSearch]![chkSubMatlTestSearch]=0,(Not ([tblRouterData].[SubMatlTest])=[tblRouterData].[SubMatlTest]),True)))
AND ((tblRouterData.PT)=IIf([Forms]![frmRouterSearch]![chkPTSearch]=-1,([tblRouterData].[PT]),IIf([Forms]![frmRouterSearch]![chkPTSearch]=0,(Not ([tblRouterData].[PT])=[tblRouterData].[PT]),True))) 
AND ((tblRouterData.UT)=IIf([Forms]![frmRouterSearch]![chkUTSearch]=-1,([tblRouterData].[UT]),IIf([Forms]![frmRouterSearch]![chkUTSearch]=0,(Not ([tblRouterData].[UT])=[tblRouterData].[UT]),True))) 
AND ((tblRouterData.RT)=IIf([Forms]![frmRouterSearch]![chkRTSearch]=-1,([tblRouterData].[RT]),IIf([Forms]![frmRouterSearch]![chkRTSearch]=0,(Not ([tblRouterData].[RT])=[tblRouterData].[RT]),True))) 
AND ((tblRouterData.Hydro)=IIf([Forms]![frmRouterSearch]![chkHydroSearch]=-1,([tblRouterData].[Hydro]),IIf([Forms]![frmRouterSearch]![chkHydroSearch]=0,(Not ([tblRouterData].[Hydro])=[tblRouterData].[Hydro]),True)))
AND ((tblRouterData.FlowCal)=IIf([Forms]![frmRouterSearch]![chkFlowCalSearch]=-1,([tblRouterData].[FlowCal]),IIf([Forms]![frmRouterSearch]![chkFlowCalSearch]=0,(Not ([tblRouterData].[FlowCal])=[tblRouterData].[FlowCal]),True)))
AND ((tblRouterData.ANI)=IIf([Forms]![frmRouterSearch]![chkANISearch]=-1,([tblRouterData].[ANI]),IIf([Forms]![frmRouterSearch]![chkANISearch]=0,(Not ([tblRouterData].[ANI])=[tblRouterData].[ANI]),True)))
AND ((tblRouterData.Stamp)=IIf([Forms]![frmRouterSearch]![chkStampSearch]=-1,([tblRouterData].[Stamp]),IIf([Forms]![frmRouterSearch]![chkStampSearch]=0,(Not ([tblRouterData].[Stamp])=[tblRouterData].[Stamp]),True)))
AND ((tblRouterData.Comments) Like "*" & [Forms]![frmRouterSearch]![txtCommentSearch] & "*"));

[pbaldy]

With a bunch of optional filtering items like that, I'm far more likely to use dynamic SQL than a saved query. It's demonstrated in the sample db here:

http://www.baldyweb.com/BuildSQL.htm

[rawlstonb]

With a bunch of optional filtering items like that, I'm far more likely to use dynamic SQL than a saved query. It's demonstrated in the sample db here:

http://www.baldyweb.com/BuildSQL.htm

Can I take the dynamic SQL and put it into a printable report?

[pbaldy]

Sure, you can take the criteria out of the report's source query and build a wherecondition. This shows a simple one, yours would get more complicated, but not too difficult once you understand the concept:

http://www.baldyweb.com/wherecondition.htm

Allen has some thoughts here too:

http://allenbrowne.com/ser-62.html

[rawlstonb]

First off, thank you for your help! People like you make the world go round...

Just to clarify though... before I take a wrecking ball to the form and report tomorrow... is this a personal preference, smoother, you would do it this way thing... or is there not an efficient way to build a query where the yes/no's don't pay attention to the no values?

I don't mind rebuilding it, and I'm certainly grateful for your replies... but I do have to have the whole thing done by Monday and backtracking a day to research a new way of building a search/report/form output, then constructing it, probably getting stuck with some code issue to come back here and wait an hour for a response that makes sense to me might not be the best time-sensitive approach if there's a simple code tweak I can do to the checkboxes that will make it work.

I will explore your method regardless at a later date even if there's a simple fix to the query... but if I can get something to make it function, for now, that would be preferable.

Again, I'm not trying to get a 'tude here haha... just want to get this done so I can enjoy the holidays

[Micron]

If a second opinion would help, I agree with the suggested approach re building the sql on the fly. If unchecked boxes have no meaning, then a loop over checkboxes that are True should help to simplify things. However, you wrote

unchecked values on this form have no meaning to the query.

but I wonder what about all the other controls you're showing in design view? Regardless, I would not delete your existing form or anything related to it. Rename and work with a copy unless you are going to start over, but do save the current objects.

[rawlstonb]

If a second opinion would help, I agree with the suggested approach re building the sql on the fly. If unchecked boxes have no meaning, then a loop over checkboxes that are True should help to simplify things. However, you wrote but I wonder what about all the other controls you're showing in design view? Regardless, I would not delete your existing form or anything related to it. Rename and work with a copy unless you are going to start over, but do save the current objects.

Anything with a null value won't be a factor in the query... it's really just laying the entire world of search options out for the user to make sure they can find whatever they're looking for with whatever limited information they have... often the part# will be all they need, because it's a unique value... but there are other scenarios where they'll just have the drawing#... and others where they just need a good template to work off of, so they'll know the part type and the operations needed... they may just want a report for everything they've authored for the year or certain part types for particular customers...

it's meant to be multi-functional on criteria and not having all the information needed to find one particular record but still have that record show up

the query works fine without the checkboxes... the checkboxes screwed up the search results and made everything filter to each particular checkbox checked that is in the record for it to show up

[Micron]

Gotcha. Many ways to skin a cat as they say. What just came to mind was something employed in a CMMS application where I worked which was a listbox of "defined" queries which you might be inclined to dismiss at first. Clicking on a list item made visible the necessary controls for that search. You could enforce that data be supplied for simple searches such as drawing number which would eliminate having to worry about controls with no values. As user needs demand, you add queries to the list. When (probably not if) you come up with more fields to search on, you're potentially faced with modifying code and search form which I acknowledge would be the same even if you had such a search form. I only mention that for future consideration when creating whatever approach you go with.

Any method that involves building sql on the fly should result in a simplified version of what you have. Consider

IIf([Forms]![frmRouterSearch]![chkInstallSearch]=-1,([tblRouterData].[Install]),IIf([Forms]![frmRouterSearch]![chkInstallSearch]=0,(Not ([tblRouterData].[Install])=[tblRouterData].[Install]),True)))

You would not need to worry about any situation other than (to use common English)
"IF the control has data, then include some field in the query". All the IIF comparisons you're making would go away.

Consider making use of the control Tag property because I'm seeing no direct connection between a checkbox (or perhaps any other control) and the field name for the query. For example, you could append to the sql variable if a control is True (or not null) and grab the field name from the tag property and concatenate it into the sql statement. Or, you might put the whole sql portion in the Tag property (e.g. for a checkbox: " AND tblMyTable.MyField = True "). IIRC the tag property doesn't use quotes - I only used them here to illustrate that leading/trailing spaces and separators such as commas would have to be considered. Truth be told, I haven't taken this approach but have used the Tag property lots of times.

EDIT: I just noticed all the fields that come after SELECT tblRouterData.*....
The asterisk means "all fields from the table". Every field after that, which is from the same table, is superfluous. I only just saw it because I was going to play around with some code to suggest how you might approach it. Also, just occurred to me that since the search form controls are unbound, you could name them after their fields and make use of the Name property in sql building. While this is often the case, I almost never build a form where the controls have the same name as their bound fields, so it didn't come to mind right away. Also, it's not entirely clear to me if a lot of your controls (especially checkboxes) are not indicative of any table field - just that you're deciding to include field X if checkbox Y is True (checked). If that were the case, I'd consider having a control that mirrors the field data type (textboxes for numbers, text, dates; checkboxes for True/False fields; combos for lists, etc) instead.

[Micron]

OK, presuming each unbound control is named after the table field, and there's nothing about that name that requires [] brackets, here's a start at what it might resemble:

Dim sql As String, sWhere As String
Dim ctl As Control

sql = "SELECT tblRouterData.* FROM tblRouterData"

For Each ctl in Me.Controls
'test only for desired control types
   If ctl.Type = acTextbox Or ctl.Type = acCombobox Then 
     If Not IsNullEmpty(ctl) Then 'add to string IF control is NOT empty
       sWhere = sWhere & ctl.Name & " = " & ctl & " AND "
     End If
   End If
Next 

'if something has been added to the WHERE part, remove trailing "AND " and concatenate, else just use sql part
If sWhere <> "" Then
   sWhere = Left(sWhere, Len(sWhere)-5)
   sql = sql & " WHERE " & sWhere
End IF
'if sWhere variable has no value, sql contains the original sql statement

'now do something with the sql such as open form/report based on it?
DoCmd.OpenForm "frmMyForm"
Forms!frmMyForm.Recordsource = sql
Forms!frmMyForm.Requery 'not sure if this would be necessary

In a standard module, you'd need the function to check for data in the control. If it returns True (the control has no data) it gets bypassed.

Public Function IsNullEmpty(ctl As Control) As Boolean
IsNullEmpty = False
If IsNull(ctl) Or Len(ctl & vbNullString) = 0 Then
   IsNullEmpty = True
End Function

This is all 'air code', is untested and may not fit the approach you decide to take (e.g. as mentioned, presumes the unbound control has the same name as the table field & that the control holds a value appropriate to that field). It also doesn't validate that for example, only numbers exist where the field in numeric but then you didn't have that either. Alternatively, you could base the opening form/report on your table/query instead of building it in code and pass sWhere as the WhereCondition in the OpenForm method. Note that this parameter must not include the word WHERE.

If you need to include other control type names, they can be found here
https://docs.microsoft.com/en-us/off....accontroltype

[rawlstonb]

OK, presuming each unbound control is named after the table field, and there's nothing about that name that requires [] brackets, here's a start at what it might resemble:

Dim sql As String, sWhere As String
Dim ctl As Control

sql = "SELECT tblRouterData.* FROM tblRouterData"

For Each ctl in Me.Controls
'test only for desired control types
   If ctl.Type = acTextbox Or ctl.Type = acCombobox Then 
     If Not IsNullEmpty(ctl) Then 'add to string IF control is NOT empty
       sWhere = sWhere & ctl.Name & " = " & ctl & " AND "
     End If
   End If
Next 

'if something has been added to the WHERE part, remove trailing "AND " and concatenate, else just use sql part
If sWhere <> "" Then
   sWhere = Left(sWhere, Len(sWhere)-5)
   sql = sql & " WHERE " & sWhere
End IF
'if sWhere variable has no value, sql contains the original sql statement

'now do something with the sql such as open form/report based on it?
DoCmd.OpenForm "frmMyForm"
Forms!frmMyForm.Recordsource = sql
Forms!frmMyForm.Requery 'not sure if this would be necessary

In a standard module, you'd need the function to check for data in the control. If it returns True (the control has no data) it gets bypassed.

Public Function IsNullEmpty(ctl As Control) As Boolean
IsNullEmpty = False
If IsNull(ctl) Or Len(ctl & vbNullString) = 0 Then
   IsNullEmpty = True
End Function

This is all 'air code', is untested and may not fit the approach you decide to take (e.g. as mentioned, presumes the unbound control has the same name as the table field & that the control holds a value appropriate to that field). It also doesn't validate that for example, only numbers exist where the field in numeric but then you didn't have that either. Alternatively, you could base the opening form/report on your table/query instead of building it in code and pass sWhere as the WhereCondition in the OpenForm method. Note that this parameter must not include the word WHERE.

If you need to include other control type names, they can be found here
https://docs.microsoft.com/en-us/off....accontroltype

Really appreciate your input... I'm gonna be hard at it first thing tomorrow... too many beers in my system at the moment to compute anything right now haha

But seriously... thank you for your time... I'll let you know if I can make anything of it in the morning... keep an eye out for my post please

[rawlstonb]

OK, presuming each unbound control is named after the table field, and there's nothing about that name that requires [] brackets, here's a start at what it might resemble:

Dim sql As String, sWhere As String
Dim ctl As Control

sql = "SELECT tblRouterData.* FROM tblRouterData"

For Each ctl in Me.Controls
'test only for desired control types
   If ctl.Type = acTextbox Or ctl.Type = acCombobox Then 
     If Not IsNullEmpty(ctl) Then 'add to string IF control is NOT empty
       sWhere = sWhere & ctl.Name & " = " & ctl & " AND "
     End If
   End If
Next 

'if something has been added to the WHERE part, remove trailing "AND " and concatenate, else just use sql part
If sWhere <> "" Then
   sWhere = Left(sWhere, Len(sWhere)-5)
   sql = sql & " WHERE " & sWhere
End IF
'if sWhere variable has no value, sql contains the original sql statement

'now do something with the sql such as open form/report based on it?
DoCmd.OpenForm "frmMyForm"
Forms!frmMyForm.Recordsource = sql
Forms!frmMyForm.Requery 'not sure if this would be necessary

In a standard module, you'd need the function to check for data in the control. If it returns True (the control has no data) it gets bypassed.

Public Function IsNullEmpty(ctl As Control) As Boolean
IsNullEmpty = False
If IsNull(ctl) Or Len(ctl & vbNullString) = 0 Then
   IsNullEmpty = True
End Function

This is all 'air code', is untested and may not fit the approach you decide to take (e.g. as mentioned, presumes the unbound control has the same name as the table field & that the control holds a value appropriate to that field). It also doesn't validate that for example, only numbers exist where the field in numeric but then you didn't have that either. Alternatively, you could base the opening form/report on your table/query instead of building it in code and pass sWhere as the WhereCondition in the OpenForm method. Note that this parameter must not include the word WHERE.

If you need to include other control type names, they can be found here
https://docs.microsoft.com/en-us/off....accontroltype

Want to try this... not sure how to import the data back into the report though... what record source to use?

Also, just being honest... we are now past my comfort zone in a deep sea of wtf-am-I-doing... I can read through code and sort of get what it's there for... but I know next to nothing of proper syntax... I am confident I can repeat repetitive things in a given format... but I don't know what was left as generic for illustration purposes vs what was purposefully left generic in what you posted.

...and I've never used a module before

Open to new things though! Quick learner... just gonna need some hand-holding and coddling to get through this

[Micron]

Posting a compacted/zipped copy of your db is an option. Sensitive data can be removed so that there's only enough records to work with, and such fields can be updated from real names to Davy Jones, D. Duck, etc.

The only generic thing about my code is the last 3 lines as I didn't know the name of the form or report to be opened using the records resulting from such code. If you said one way or the other before your most recent post, I lost that notion when writing that - involved in too many threads to keep everything in the front of my old brain. FWIW, I have at times spent many hours working with someone to produce complex solutions, and yours doesn't seem all that onerous so your chances of arriving at a solution are good here - even if I had to bow out. Such a lot of knowledgeable and helping people here that it's almost ridiculous.

Do you even have a report yet? If not, can you create one? What's also missing from the code sample is the event as I don't know how you're going to call it. A button click on a search form? I also get the drift that you were using checkboxes to 'flag' that a certain form control should be included. The code doesn't take that approach. It basically says, if there is a textbox or combo box on this form that has data in it, then use it to string together a criteria clause for a query, open a form/report and then assign this constructed sql statement to the form/report recordsource property. The form/report then displays the data according to the criteria the user enters. If none of those controls have values, simply open the object based on a sql statement that just grabs the entire table. I do believe you need to start with a new form as was previously mentioned.

Again, you might be able to grasp that while I don't think the task is too complex, there are a lot of variables and a db with records, a search form and report to be opened would be a big help. I'm all for helping out, but I draw the line much prior to building an entire db solution for free as you can probably understand.

PS - I think you're choosing 'reply with quote' or whatever the button says rather than choosing 'reply' as you're quoting everything previously written. This just adds unnecessary verbal diarrhea and buries your response way down at the bottom, especially when you quote large blocks of code.

[rawlstonb]

here she is

[Micron]

OK, couple of issues.
- I see that your checkboxes are not used to flag that you do/don't want to include a field - they are T/F fields in your table. That will require some code modification.
- are you sure you want to search on a comments field? It will slow things down significantly. Sometimes too much bling is a bad thing.
- can you provide an example of what to you is a valid result based on just a few criteria so that I know something is working or not?
- last and maybe most important, I see that so far you've tried this with an embedded macro, which for me, failed on the 1st try. Unfortunately having to work with this would be a deal breaker as I don't use macros at all (save for maybe AutoExec) and don't wish to learn that which is inferior to code in more than one way.

If code is OK, it might be best to take this off line for now (pm's) as I find if there's lots of clarification and questions, it doesn't add anything to the thread other than verbal diarrhea (that's twice I've written that phrase today).

[rawlstonb]

I was messing with it in so many ways today, I don't even remember what all I tried in desperation to get the search to work... the "txt(insert tblField)Search" callouts in the SQL probably don't work because I renamed the frmRouterSearch controls to the tblRouterData field values as you suggested in your post... but it brought up an error and I had no clue how to fix it, so I probably got frustrated and left what was kind of sort of working in disarray...

I gave you my number in PM

For the record... I don't care how, I'm not married to anything at all... I'm just trying to get the thing to work... I don't know VBA syntax, so I use macros whenever I can because they make sense to me for the most part and they work for simple things... As long as it achieves the desired result (and hopefully it can be explained why it does for future changes) I'm down for whatever changes you see fit... just keep a change log so I can fix it on the hard copy