Code to send Form data to Query

[fredpeters555]

I have a radio group 'frView' on a form that a user selects the conditon of a field he wishes to filter, displays the results in textfield 'txtViewName1' for the user to view.

The query then calls the results of 'txtViewName1'.

Codes for both the form and query follow.

Although I get the proper results for 'All' and 'N/A', it does not return ANY results for the 'NOT N/A'.

Via Select case, I have tried all variations of txtViewName1 = 'not', '<>','&'and '*', all to no avail.



Using the following code to populate txtViewName1:
----------------------------------------------------------

Select Case frView
      Case 1
        'All
        txtViewName1 = "*"
      
      Case 2
        'Assigned
        txtViewName1 = "NOT N/A"
      
      Case 3
        'Un_Assigned
        txtViewName1 = "N/A"
         
    End Select
    DoCmd.Requery

The sql view for the query is:
---------------------------------

SELECT IPScheme.ID, IPScheme.tIPGroup, IPScheme.tIP, IPScheme.tLocation, IPScheme.tDone, IPScheme.tMake, IPScheme.tModel, IPScheme.tDesc, IPScheme.tNotes
FROM IPScheme
WHERE (((IPScheme.tIPGroup) Like [Forms]![fIPScheme]![txtGroupName]) AND ((IPScheme.tIP) Like [Forms]![fIPScheme]![txtBranchName] & "*") AND ((IPScheme.tLocation) Like ([Forms]![fIPScheme]![txtViewName1])) AND ((IPScheme.tDone) Like [Forms]![fIPScheme]![txtDone]));

----------------------------
PLEASE!! any suggestions?

Just remember...they're only ones and zero's you're playing with.
Fred

 

[dhookom]

You can't place the "Not" in the criteria like this. It just won't be successful. You can only include a value to compare, not how to compare it.

How are you using the query? Is it the record source for a form or report?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[fredpeters555]

Duane,
Thanks for the reply.
The query is the record source for a subform contained in the same form as the option boxes.I need it to requery when the change is made. It is also the source for a results report.

Just remember...they're only ones and zero's you're playing with.
Fred

 

[dhookom]

I would use code to dynamically build the entire SQL statement that is the record source for the subform. You can either change the SQL of a saved query or just set the Row Source property of the subform to a new value.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[fredpeters555]

hmmm...a little deep for me at this point, but thanks loads for the support. I'll start to learn that to get a medium range solution in place.

Just remember...they're only ones and zero's you're playing with.
Fred