asked on

Access 2007 - Form drop down list row source filter

I may be way off on this but I'm trying to "filter" a drop down list control in my form. I can use the following as the row source to get all the values from the table....

SELECT [ICNTable].[ICNNumber] FROM ICNTable ORDER BY [ICNNumber];

But the ICN # is a combination of the CRN # (first 7 charactions) plus 4 characters. I've used this type of syntax before in writing queries in VB but I was hoping to use it as the row source for the drop down box. I don't get a syntax error when I run the form but it still shows me all the records when I want just the ones that have a specific value in the first 7 places. Is this possible?

SELECT [ICNTable].[ICNNumber] FROM ICNTable WHERE "left(ICNNumber,7) = " & chr$(34) & CRNNumber & chr$(34) ORDER BY [ICNNumber];

Thanks!
Helen

answer_dude

Where is CRNNumber coming from? If it's a field on the table, then just do:

SELECT DISTINCT ICNTable.CRNNumber
FROM ICNTable;

answer_dude

If you want ICNNumber returned, though -- and if CRNNumber is on the table... and if ICNNumber is always CRNNumber + 4 digits... then you'll still be selecting all ICNNumbers anyway.

answer_dude

Based on your SQL example, this would be the closest to what you want (but again, assuming CRNNumber is always a subset of ISNNumber then you're probably not going to get your desired result).

SELECT DISTINCT ICNTable.ICNNumber, Left([ICNNumber],7) AS ICNLeft
FROM ICNTable
WHERE (((Left([ICNNumber],7))=ICNTable.CRNNumber));

Open in new window

answer_dude

Sorry... this one:

SELECT DISTINCT ICNTable.ICNNumber
FROM ICNTable
WHERE (((Left([ICNNumber],7))=[ICNTable].[CRNNumber]))
ORDER BY ICNTable.ICNNumber;

Open in new window

greenprg

ASKER

Sorry for the delay. I thought I posted this reply already but then I had a virus problem I had to deal with. Fun....Fun.

So anyway, I tried the following

SELECT [ICNTable].[ICNNumber] FROM ICNTable WHERE (Left(ICNNumber,7)=CRNNumber) ORDER BY ICNNumber;

which does not give any syntax error but it still lists ALL ICN Numbers. I only want the ones that have the same 7 starting digits as the CRNNumber displaying in the form. I tried me.CRNNumber but then I get Enter Parameter Value when I click on the drop down list control.

answer_dude

What is the name of the field on the form that contains the CRN Number?

ASKER CERTIFIED SOLUTION

answer_dude

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

greenprg

ASKER

Perfect! It works great!

Thanks!
Helen

greenprg

ASKER

Works perfectly. Very cool!