SQL trouble

[yhgtbfk]

I have a database where there are many programs.
ie: Accounting, Marketing, Business etc...

The user's valid programs are listed like this "Accounting, Marketing"

This is stored in the staff table.

When I run the following:

DLookup("program", "program", "program IN(select program from staff where program LIKE '*Program*'")

Obviously this doesnt work as it measures "Accounting, Marketing" against "program"

What I want it to do is find the programs within the program table that are within the string "Accounting, Marketing"

Does this make sense?

 

[WayneRyan]

y,

Let the user select from a combobox. Its RowSource is the Select
statement within your DLookUp.

Wayne

 

[yhgtbfk]

y,

Let the user select from a combobox. Its RowSource is the Select
statement within your DLookUp.

Wayne

Um...Not quite. Let me try to explain again.

In one table are the numbers 1, 2, 3, 4, 5

The user has a string "1, 4, 5"

Now I want to create the row source for the combo box (via VB) to have the options 2, 3. ie:

row source = "SELECT number FROM number WHERE number NOT IN (select numberstring from staff where numberstring LIKE '* ???? *'

What I have currently done is allow the user to select any program, and then validate whether or not it is part of their string. But for neatness I would rather the options not be there.

Your help was greatly appreciated though

 

[WayneRyan]

Y,

RowSource = "SELECT number " & _
            "FROM number " & _
            "WHERE number NOT IN (" & Me.UserString & ");"

This will translate to:

SELECT number FROM number WHERE number NOT IN (1,4,5);

if the UserString = "1,4,5"
[/code]

Wayne

 

[yhgtbfk]

Y,

RowSource = "SELECT number " & _
            "FROM number " & _
            "WHERE number NOT IN (" & Me.UserString & ");"

This will translate to:

SELECT number FROM number WHERE number NOT IN (1,4,5);

if the UserString = "1,4,5"
[/code]

Wayne

Im getting really close now...

What about

SELECT program FROM program WHERE program NOT IN ('" & me.userstring & "');

where the UserString = "Accounting;Marketing;Sales"

 

[WayneRyan]

y,

Good, but numbers don't need "punctuation", strings do.

Strings need: IN ('String1', 'String2', 'String3')

The user would have to put in all of the single-quotes.

You can look at multi-select listboxes. There are examples here.

Wayne

 

[yhgtbfk]

y,

Good, but numbers don't need "punctuation", strings do.

Strings need: IN ('String1', 'String2', 'String3')

The user would have to put in all of the single-quotes.

You can look at multi-select listboxes. There are examples here.

Wayne

I changed it so the userstring read "'marketing', 'accounting', 'finance'"

It works perfectly...So far...

Thank you for your help