Running query from textbox value in form

[Moss100]

I need to run a query depending on the value of a text box.

I am sorting on form called Sort
From a text box called Style
The field being sorted is called Type


My query, in field "type" has the criteria: [forms]![sort]![Style]

This works fine when the content of the textbox is for example "flat"

But is the contents of the textbox is "flat" Or "Terrace" then the query returns no reseults.

Im guessing that the problem is that when the textbox contains the Or command it is not running. If i just past "flat" Or "Terrace" into the query directly it runs fine so there is a problem with it accepting it from the text box

How can I resolve this? Many thanks Mark

 

[Moss100]

To clarify, the query works with the text box containing - flat - but not when containg flat Or terrace.

Thanks Mark

 

[Golom]

We would need to see your SQL to be sure but I assume that you have something like

WHERE SomeField = "'" & [forms]![sort]![Style] & "'"

That will resolve to

WHERE SomeField = 'Flat OR Terrace'

So it is looking for the string 'Flat OR Terrace' and it does not treat the OR as a logical operator ... just as a text string.

If you post your SQL we may be able to suggest alternative constructs.

 

[Moss100]

SELECT Properties.[Property Number], Properties.[Property Type]
FROM Properties
WHERE (((Properties.[Property Type])=[forms]![Match_Property_To_Tenant]![propertytype]));


Thanks

 

[dhookom]

Your criteria won't work with multiple values. There are a couple alternatives. One is to faq701-7433.

Duane
Hook'D on Access
MS Access MVP

 

[Golom]

Assuming that 'OR' is the only separator that may occur, you could do something like

WHERE InStr(1, [forms]![Match_Property_To_Tenant]![propertytype], [PropertyType]) > 0

That will return TRUE and include the record whenever the value of [PropertyType] is found anywhere in the string from the text box.

 

[Moss100]

So if i understand correctly it is not possible to include the or statment in the text box for the query?

Thanks Mark

 

[Golom]

So if I understand correctly it is not possible to include the or statment in the text box for the query?

Not as you have done it because just plugging the contents of the text box into a query as a parameter results in it being treated as a string of text and not as a sequence of arguments and logical operators. You can of course use VB to parse the text box contents and build a correctly constructed SQL statement in code.

If, for example, you told users to separate property types with commas rather than the word "OR" (e.g. Flat, Terrace) then

Dim SQL As String
Dim rs As RecordSet
SQL = "SELECT [Property Number], [Property Type] " & _
      "FROM Properties WHERE [Property Type] IN (" & _
      [forms]![Match_Property_To_Tenant]![propertytype] & ")"
Set rs = db.OpenRecordset (SQL)

 

[Moss100]

When I attach the code below to the double click event on a button I get an error - what is wrong? Thanks Mark

Private Sub Command24_DblClick(Cancel As Integer)
Dim SQL As String
Dim rs As Recordset
SQL = "SELECT [Property Number], [Property Type] " & _
"FROM Properties WHERE [Property Type] IN (" & _
[Forms]![Match_Property_To_Tenant]![PropertyType] & ")"
Set rs = db.OpenRecordset(SQL)
End Sub

 

[Golom]

I just gave you skeleton code. The most obvious possible error is that db is not defined. I was just using that as a placeholder for a database object. You would need something like

Dim db As DAO.Database
Set db = CurrentDb

At the beginning of your code.

Note that this assumes that you are using DAO (Data Access Objects). If you are using ADO then the objects and syntax are somewhat different. Even if it runs without errors though, you won't see anything. You have created a recordset (rs) but you need to display it. Knowing nothing about your application I can't really say exactly how you want to display the contents of the recordset.

 

[dhookom]

I would use the solution I already suggested of changing the SQL of a saved query. It would also help us if you told us what you mean by "run a query". "run" is typically reserved for action queries. Do you want to display/open the query? Is the query the record source of a form or report?

Duane
Hook'D on Access
MS Access MVP

 

[Moss100]

A full explanation:

I have a continuous form which displays a list of properties.

In the header of the form, I have a text box.

Whena user types FLAT into the text box the filter is applied to the form so that the form diplays only flats. Fine that works all OK.

I would like the user to be able to type FLAT, TERRACE (or whatever property type input) and this would filter the form to show all flats and terrace houses.


Hopefully that explains what I am trying to acheive.

Thanks Mark

 

[dhookom]

I would then use Golom's suggestion with a slight modification
[CODE SQL]
WHERE InStr(1, "," & [forms]![Match_Property_To_Tenant]![propertytype] & ",", "," & [PropertyType] & ",") > 0
[/code]
Users would need to enter values without spaces
[tt]
Good: flat,Terrace
Bad: flat, Terrace
[/tt]
Use the after update or some other event to requery the form.


Duane
Hook'D on Access
MS Access MVP

 

[Moss100]

I have put this in the after update event of the text box - but it says compile error - sorry for being so dumb - any ideas?

Thanks

Private Sub PropertyType_AfterUpdate()
WHERE InStr(1, "," & [Forms]![Match_Property_To_Tenant]![PropertyType] & ",", "," & [PropertyType] & ",") > 0
End Sub

 

[Golom]

What Duane and I are saying is that your continuous form is bound to some query and it currently has a WHERE clause of the form

[blue]WHERE (((Properties.[Property Type])=[forms]![Match_Property_To_Tenant]![propertytype]))[/blue]

Modify the query in SQL view and replace that WHERE clause with the one Duane gave you. This is SQL code, not VB code, so it belongs in an SQL query and not in a VB module.