String problem

[Johny]

I have several checkboxes where only 1 can be checked, every time a checkbox has been checked, I want update the rowsource of my listbox. So i get the SQL string and update the string with the new condition.

Can it be done like this:

stSQL=

"SELECT field1, field2
FROM tbl1
WHERE field1='test' AND field2='test2'"

Now I wanna replace 'test' into 'AccessWorld'. Where do I start?

I already figgered out the beginning and end position of the word (here: 'test' ) but the lenght of the words are obviously not equal to each other, so this is a dead end.

Help

 

[KenHigg]

May I assume you are using a frame/radio buttons combo instead of check boxes?

ken

 

[Johny]

yea, forgot to mention that

 

[KenHigg]

pseudo:

--------------------------
stSQL= "SELECT field1, field2 FROM tbl1 WHERE field1= "

select case me!myfra
case 0
stSQL = stSQL & "'test' "
case 1
stSQL = stSQL & "'AccessWorld' "
end select

stSQL = stSQL & "AND field2='test2'"
--------------------------

???
ken

 

[Johny]

No, the thing is: stSQL already exists. stSQL= rowsource of my listbox.
What you are doing now is initialising stSQL but in my case it already has a value.

I'll explain my situation a little more.
My form consists of 1 listbox and 2 frames that each contains 3 radio buttons where only one can be chosen)
The data that will be shown in the listbox depends on the selections you make.

eg:
my_listbox contains all the assignments
frame_1: has 3 radio buttons ("urgent", "not urgent", "within a week")
frame_2: has 3 radio buttons ("schoolwork","ironing","feeding the dogs")

so when the form is loaded, rowsource of my_listbox is initialised:
"SELECT date, urgent, sort FROM Assignment"
(none of the radio buttons are checked, so no conditions are set).

When I select "urgent", I have to change the rowsource of my_listbox into:
"SELECT date, urgent, sort FROM Assignment WHERE urgent='urgent'"

When I select "schoolwork", I have to change the rowsource of my_listbox into:
"SELECT date, urgent, sort FROM Assignment WHERE urgent='urgent' AND sort='schoolwork'"

When I select "not urgent", I have to change the rowsource of my_listbox into:
"SELECT date, urgent, sort FROM Assignment WHERE urgent='not urgent' AND sort='schoolwork'"

When I select 'feeding the dogs', I have to change the rowsource of my_listbox into:
"SELECT date, urgent, sort FROM Assignment WHERE urgent='not urgent' AND sort='feeding the dogs'"

You see where I am going? I think ya misunderstood my question.
I hope you can help me out here because I really want this to work

 

[KenHigg]

Since you cannot unselect all radio buttons in a frame, I would put a 'Show All' button that would effectively remove any criteria for that field by placing an '*' in the sql. (and set it as the default value for the frame).

Then use the following:

stSQL= "SELECT field1, field2 FROM tbl1 WHERE field1= '" & me!myframe1 & "' and field2 = '" & me!myframe2 & ';"

???
ken