Query to use values in form as WHERE clause

raggajunglist

Registered User.
Local time
Today, 16:51
Joined
Aug 30, 2007
Messages
40
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
 
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 & "'
 
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
 
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??
 
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
 

Attachments

Last edited:
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.
 
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
 
....

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.
 
Rabbie,

You sound like you know whats going and & what i'm trying to do...
You got any advice for me bro?
 
Have you tried
Code:
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
 
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!
 
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.:(
 
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 :)
 
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!!!
 
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. :rolleyes: :rolleyes:
 
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?
 
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...??
Code:
if me.cboOperator.value = "NOT"
then me.cboOperator.value = "<>"
end if
 
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...
 
wayhey, you were right bout the <> instead of Not though, ta!
thats two down two to go!
:)
 

Users who are viewing this thread

Back
Top Bottom