Problems getting query critreria from a form.

[MrBrown]

Hi there,<br><br>I have a table with employee numbers, teams and pay details(Pay details format is text with \£CCCCCC;0; input mask).<br><br>I can design a query to list all the employees with a pay value of between £10 and £20, (eg query criteria of &gt;=&quot;£10&quot; And &lt;=&quot;£20&quot.<br><br>BUT I want a user to be able to pick that type of criteria from a combo box in a form.<br><br>I can get it to work with just &quot;text&quot; matches, (eg the combo box lists teams the employees are in, and the query takes that value and lists them. Achieved by entering...<br><br>Like [Forms]![Dynamic_report]![CBOteam]<br><br>in the query criteria.)<br><br>I am running out of ideas, any help is appreciated, if you don't understand my problem please ask me some questions.<br><br>Mr Brown. 8¬)

 

[Robertd]

Hi Mr Brown,<br><br>the obivious, why are you using a text field for currency?<br><br>ok i take it CBOteams is the combo box that selects the &quot;team&quot; and you want to add the ability to select a range by using other combobox's like<br><br>&nbsp;from CBO lowvalue to CBO upper value on the form<br><br>ok quick and dirty <br>add a text box called say &quot;range&quot; and on the after update event in each of the CBO's do some string manipulation on the text in &quot;range&quot; ie build the criteria '&gt;=&quot;£10&quot;' 'And' '&lt;=&quot;£20&quot;' you then can make the &quot;range&quot; text box contain what ever approiate criteria you need when this is working correctly make the text box not visible and refer to like forms!name!range in the field in the query.<br><br>see ya<br>RobertD<br><br><br>

 

[MrBrown]

Thanks for replying RobertD!!!<br><br>Erm...<br>Well, first off, I'm using the text format instead of currency because you cannot have a zero length string for this format type. And there are quite a few people without a pay rate put in (so it would look silly if they had to have £0 as a pay rate, and also I couldn't leave it blank because with that format it would mean having a Null value, and they can't be picked up by queries...Phew.)<br><br>Second, I'm not even trying to go as sophisticated as to have two seperate combo boxes to pick upper and lower bounds. I just have a combo box with the following value list :<br><br>&lt;=&quot;£10&quot;;Under £10;&gt;=&quot;£10&quot; And &lt;=&quot;£20&quot;;Between £10 and £20;&gt;=&quot;£20&quot; And &lt;=&quot;£30&quot;;Between £20 and £30;&gt;=&quot;£30&quot; And &lt;=&quot;£40&quot;;Between £30 and £40;&gt;=&quot;£40&quot;;Over £40<br><br>ie. it looks like this...<br><br>&lt;=&quot;£10&quot;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Under £10<br>&gt;=&quot;£10&quot; And &lt;=&quot;£20&quot;&nbsp;&nbsp;&nbsp;&nbsp;Between £10 and £20<br>&gt;=&quot;£20&quot; And &lt;=&quot;£30&quot;&nbsp;&nbsp;&nbsp;&nbsp;Between £20 and £30<br>&gt;=&quot;£30&quot; And &lt;=&quot;£40&quot;&nbsp;&nbsp;&nbsp;&nbsp;Between £30 and £40<br>&gt;=&quot;£40&quot;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Over £40<br><br>except the first column doesn't have a width and is hence not visible.<br><br>So I want the query to use whatever is selected in that first column to be the criteria for the query.<br>Sounds simple doesn't it. hehe!<br><br>If anyone can help it would be appreciated.<br><br>Any further light to shed on my problem RobertD??<br><br>Thanks<br><br>Mr Brown.

 

[Robertd]

OK.<br><br>is the bound column the hidden column ie the one with the text criteria. <br><br>if so use the after update event to load the criteria into the &quot;range&quot; text box. like<br><br>sub CBO after_update<br><br>&nbsp;&nbsp;&nbsp;me.range = me.CBO<br>&nbsp;&nbsp;&nbsp;' range textbox will take the CBO bound column<br>&nbsp;&nbsp;&nbsp;' value<br>&nbsp;&nbsp;&nbsp;...<br>endsub<br><br>this will give &quot;range&quot; the value of the bound column the criteria, whilst displaying the more english prompt to the user, if you can follow what i mean here<br><br>see ya <br>RobertD<br><br><br><br>

 

[MrBrown]

Yep.<br><br>I've already tried that.<br><br>The first column <U>is</U> the bound column like you thought, so when I tell the query criteria to look at the combo box it should work anyway.<br>I mean, it <u>does</u> work if its NOT that kind of criteria (ie. just a text match or number match)<br><br>It seems to be some problem with sending that kind of criteria from a form to a query?!?!?!<br><br>I'm really getting stuck on this.<br><br>Maybe try setting up a dummy situation like mine and see if you can get it working yourself?<br>I don't know if that will help any potential &quot;Problem Solvers&quot;.<br><br>Cheers People!<br><br>Mr Brown.