Query to use values in form as WHERE clause

[raggajunglist]

Morning all,

Am trying to create a really simple form where the user can select one of five fields, an operator (just =, Not, <, and >) and the values are pulled from whatever field is chosen.

I have the form down and can populate the values list based on the field however now i need to use the values in these boxes to create a WHERE clause.
I'm really new to SQL and have only done a couple of online tutorials but i think im on the right lines?

DoCmd.RunSQL "SELECT * FROM qryLinkMasterPrimary WHERE " + cboFields.Value + " " + cboOperator.Value + " " + cboValues.Value

Is this even slightly close? I've tried searching the internet and everything i find is waaaaay too complicated for me to get my head round.
Any help is greatly appreciated

Mike Harkess

 

[rsmonkey]

not far off to be honest..
well first of all define your combo box values as a variable. thus:

valcboFields = Me.cboFields.Value
valcboOperator = Me.cboOperator.Value
valcboValues = Me.cboValues

strSQL = "SELECT * FROM qryLinkMasterPrimary WHERE " & valcboFields & " AND " & valcboOperator & " AND " & valcboValues & ""

Me.(your list box name).Rowsource = strSQL
Me.(your list box name).Requery

I would advise you to use more meaningful names on your Combo's etc..
If your values are dealing with numbers it should have single quotes around it:
'" & valNAME & "'

 

[raggajunglist]

Thanks for the reply rsmonkey,

Thats good news, i thought i was well off

Me.(your list box name).Rowsource = strSQL
Me.(your list box name).Requery

Sorry for sounding dumb but there is no listbox is that a prob?
I only have 3 unbound combo boxes and then a command button which i want to run the sql statement.

Is there another way i should be doing it or can i still do it this way round?

Thanks

Mike

 

[ajetrumpet]

Ok you guys, this has really got me confused...

Hey Mike,
Can I see what your form looks like?? Your selecting FIELD NAMES from a combo box via a VALUE LIST right?? I understand that part, but what I don't understand is how you're getting what you need with these annoying " characters...

Would either of you mind explaining this?? I might just post a thread on it....

I assume this...

" & valcboFields & " AND " & valcboOperator & " AND " & valcboValues & ""

has confused me. "SELECT * " refers to records, so are we reversing the usual process of selecting fields before records??

 

[raggajunglist]

Hey aje,

Have attached a bmp of the form. Basically (for example), manager selects on combo box 1 that he/she wants to query by Date, then they choose an operator so in this case =, then the third combo box is filled with all the dates availiable so the manager chooses 01/10/07.
So query should be..........Date = 01/10/07

Im not getting what i need, with or without annoying "s.
Also what do you want me to explain?

Thanks

Mike

 

[ajetrumpet]

Mike,

I'll look at the file and see if I can help.

About the explanations...nevermind; I see a lot of code posted here and across the internet in general that use MANY, MANY " characters when referencing objects, controls, etc...

I have rarely used them, and I'm wondering why, or if they even matter...and if they do, why haven't I used them?? Am I special?? That's the deal, it's just a wonder of mine.

 

[raggajunglist]

Aje,

I'm really new to SQL so I can't say i realised any different, i guess if something works without needing extra quotations then why put them in.

Thanks for having a look for me buddy!

Mike

 

[Rabbie]

....

About the explanations...nevermind; I see a lot of code posted here and across the internet in general that use MANY, MANY " characters when referencing objects, controls, etc...

I have rarely used them, and I'm wondering why, or if they even matter...and if they do, why haven't I used them?? Am I special?? That's the deal, it's just a wonder of mine.

If you look he is building a string out of several sub strings. The literal Strings are enclosed in the " characters so that Access knows they are literals The filed contents are being added to the string as well.

 

[raggajunglist]

Rabbie,

You sound like you know whats going and & what i'm trying to do...
You got any advice for me bro?

 

[Rabbie]

Have you tried

dim strSQL as String
strSQL = "SELECT * FROM qryLinkMasterPrimary WHERE " & cboFields.Value & " " & cboOperator.Value & " " & cboValues.Value

msgbox(strSQL) ' Just for diagnostic processes
DoCmd.RunSQL strSQL

Hope this helps

 

[raggajunglist]

Hmm strange, the Msgbox result seems ok to me,
SELECT * FROM qryLinkMasterPrimary WHERE AccNumber = 1234567
but i get this error

RUN TIME ERROR '2342'
A RunSQL action requires an arguement consisting of an SQL statement..

Any ideas? Have played around a little bit but with no luck...

Cheers for the help boss, much appreciated!

 

[Rabbie]

I have checked in Access Help for RunSQL. The query needs to be an Action Query or a data definition Query. Thats why you are getting that error.

 

[raggajunglist]

Bugger,
So will i have to create a virtual table that will hold the results from the query or something?
I looked on the help topic and it really doesnt help much at all.
You got any idea how i can do this?
Cheers for the help

 

[raggajunglist]

Right....
Had a little play around and funnily enough have ended up back on the topic of quotation marks....

Check this out, it works... kinda

Dim strSQL As String

strSQL = "SELECT qryLinkMasterPrimary.* INTO NewTable FROM qryLinkMasterPrimary WHERE " & cboFields.Value & " " & cboOperator.Value & " " & "'" & cboValues.Value & "'"

MsgBox (strSQL)
DoCmd.RunSQL strSQL

DoCmd.OpenTable "NewTable", acViewPreview, acReadOnly

But... and it is a pretty big but... it only works for the = operator.
So if i say Date NOT, or Date > the msgbox seems to display the correct SQL string but when the error pops up i get it like....
'ID Not 'joebloggs"
With a single at the start and a double at the end???
I figure something will have to be done for the variable values like dates needing #01/10/07# and number values needing ' but i figured this could be easily done with a select case or something?
Come on peeps please help i reckon im close!!!

 

[ajetrumpet]

I have checked in Access Help for RunSQL. The query needs to be an Action Query or a data definition Query. Thats why you are getting that error.

No kidding??

So lets say I RUN my SQL?? If I were a machine, I would give you an error because I have to ask..."OK, I selected it, now what do you want me to do with it??" But I wouldn't have to ask that stupid question if you told me to OPEN query instead.

 

[raggajunglist]

I tried over and over a while back like this and had no luck so tried the RunSQL business.

SELECT * FROM qryLinkMastAdds
WHERE ((([Forms]![Form1]![cboFields].[Value] & " " & [Forms]![Form1]![cboOperator].[Value] & " " & "'" & [Forms]![Form1]![cboValues].[Value] & "'")=True));

Does this make any sense?

 

[ajetrumpet]

Check this out, it works... kinda

Dim strSQL As String

strSQL = "SELECT qryLinkMasterPrimary.* INTO NewTable FROM qryLinkMasterPrimary WHERE " & cboFields.Value & " " & cboOperator.Value & " " & "'" & cboValues.Value & "'"

MsgBox (strSQL)
DoCmd.RunSQL strSQL

DoCmd.OpenTable "NewTable", acViewPreview, acReadOnly

Mike,
You are opening a recordset and viewing the results based off of all three combos right??

But... and it is a pretty big but... it only works for the = operator. So if i say Date NOT, or Date > the msgbox seems to display the correct SQL string but when the error pops up i get it like....
'ID Not 'joebloggs"

The > and < can be used with text strings as well as numbers. I'm not sure if the NOT in the second combo is right, but maybe you could change that...??

if me.cboOperator.value = "NOT"
then me.cboOperator.value = "<>"
end if

 

[ajetrumpet]

I tried over and over a while back like this and had no luck so tried the RunSQL business.

You CANNOT just SELECT with the RunSQL command, per my last post, and Rabbie's link.

 

[raggajunglist]

No im not opening a record set, but yes i want to see the results based on all three combos.
Should i be using a recordset?
I can get it to work with the = but not anything else...

 

[raggajunglist]

wayhey, you were right bout the <> instead of Not though, ta!
thats two down two to go!