Using unbound field as Query criteria

[davesmith202]

I have a value in an unbound field on a Form, which is 1234 OR 765 OR 356.

In the QBE criteria grid, I used builder to reference this form:

Forms!myform!myunboundfield

The column this is in is for the ID field, which is a number.

However, it is not filtering the data correctly. If I copy the above text and paste it into the QBE grid, then it will work. But when I reference it, it fails. If I change the value to just a number on my unbound field, it works. So the issue seems to be that its bringing across the text as a string and so perhaps effectively puts quotes around it when referencing it.

Any ideas of a solution to this one?

Thanks,

Dave

 

[pr2-eugin]

Sorry I am a bit confused here.. So in the text box you enter data like 1234 Or 765 Or 356, and in the QBE you have someID = Forms!myform!myunboundfield ?

Or you are using 1234, and in the QBE you have someID = Forms!myform!myunboundfield?

If it is the first, I have to say that will not work.. If it is latter, then I am puzzled why it would not work for you !

 

[davesmith202]

Its like the first example you gave. You have the 1234 Or 765 Or 356 in the unbound field and in the ClientID column in the QBE grid, I have put Forms!myform!myunboundfield as the criteria. You can do this for dates or numbers, so why not for my example?

 

[pr2-eugin]

Not using Compiled Query. When you use the QBE to input actual values like 1234 Or 54 the generated SQL becomes

WHERE (((someField) = 1234) Or ((someField) = 54));

If you save the Query with controls of Forms (QBF), then the SQL stored is..

WHERE ((someField) = Forms!myform!myunboundfield);

Since they are precompiled, the field will look for whatever is in there.. If you entered 123 Or 45, it will look for that String as a whole, it cannot break it down, as

WHERE (((someField) = Forms!myform!myunboundfield) Or ((someField) = Forms!myform!myunboundfield));

Makes sense?

You can get what you want by using QueryDefs or Dynamic SQL (SQL using VBA in Run time).

 

[davesmith202]

I kind of understand what you mean, although not fully. Are you saying that I cannot put multiple values in a field and then reference it? Or are you saying that I can, so long as I also include the text WHERE etc?

 

[pr2-eugin]

You have to create another two controls on the Form, if you wish to reference controls, so you can use..

WHERE (((someField) = Forms!myform![COLOR=Blue][B]myunboundfield1[/B][/COLOR]) Or ((someField) = Forms!myform![COLOR=Red][B]myunboundfield2[/B][/COLOR]) Or ((someField) = Forms!myform![COLOR=Green][B]myunboundfield3[/B][/COLOR]));

 

[davesmith202]

Arggh! That's a pain! Can I somehow refer to a global variable and use that instead of the value in an unbound field?

e.g. the criteria would be something like WHERE strCriteria, with that text put in the QBE grid?

Not sure if you can do that.

 

[pr2-eugin]

Unfortunately not. You cannot change the criteria like that. (hate to quotes myself)

You can get what you want by using QueryDefs or Dynamic SQL (SQL using VBA in Run time).

With QBE your options are limited on what you can do with Form controls.

 

[davesmith202]

Ok. I tried to create something that my client could easily replicate herself. She can do a very small amount of code but I was trying to do a work around so she could create other examples herself. Looks like its going to be a full vba solution for her and she will just have to grit her teeth and get on with it. or bring me in.

I created a multiselect listbox and we are using it to filter a query. Hey ho!

 

[pr2-eugin]

Looks like its going to be a full vba solution for her and she will just have to grit her teeth and get on with it.

Well it's about time people realize how amazing coding could be..

I created a multiselect listbox and we are using it to filter a query. Hey ho!

Not sure how this will work out.

 

[davesmith202]

Well, I have already written the code to create the WHERE condition. I just need to use the DAO query code in my other post you kindly answered and tack the WHERE condition on the end. Simples!

 

[pr2-eugin]

Good work !

 

[WannaKatana]

I use a function that retrieves the value. =GetFormFieldValue() (example)

 

[WannaKatana]

I use a function that retrieves the value. =GetFormFieldValue() (example)

I just wrote this to get a formated version of the field value for use in the Where clause:

Public Function GetFormFieldValue() As String

    GetFormFieldValue = Format([Forms]![frmMainMenu]![StartDate], "mmm") & "-" & Format([Forms]![frmMainMenu]![StartDate], "yyyy")
    
End Function