Setting Query In an If statement 1

[SpankYou]

Hey,

I have 8 check boxes named ChkA, ChkC, etc basically when I click on an ok button, I'll have an IF statement that says...

If chkA.checked = true then
.
.
.
End IF

Between the If And The End If, I will either put the relevant letters into a variable (e.g. chkA = "A&quot or declare a boolean to show which letters have been clicked. Now basically after this selection process, I need an VBA/SQL statement that says..

if "A" (via ChkA) has been selected, then search through a query and show all records where a certain field begins with the letter A.

These are Job Numbers e.g.

a441344
c552522
j456341
e563222
...

So if a user selects multiple checkboxes, I needs to bring up all the jobs that start with those letters.

This requires both VBA and SQL Im assuming, my VBA is ok but SQL not great, I'll post it in the queries Forum as well if it is more relevant to them.

Thanks for any help I can get.

Cheers

Sam

 

[SkipVought]

Hi,

In your SQL Where clause, you will want to have an IN quallifier in conjunction with SUBSTR...

Select * From MyTable
Where SUBSTR(Job_Number,1,1) IN (MyInList)

MyInList should be formatted like this...

MyInList = "'A','B','J'"

So as you process the check boxes, you will build MyInList.

Hope this helps


Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SpankYou]

I currently have a query with many other clauses, should I tap this on the end of the where clause? Or can I run it from VBA code? I'm a bit new when it comes to SQL. Would it just be

Where SUBSTR(CH_CODE,1,1) IN (MyInList) Or

Where SUBSTR(CH_CODE,1,1) IN (forms!.FrMine.MyInList)

Thanks For the help

Sam

 

[SkipVought]

It must be run from code since you have a variable. Unless your query accesses alot of data, just tack it on to the end of your Where clause.

What you need to end up with is your SQL in a string variable. Check out xlbo's post in thread68-610667.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SpankYou]

Hey SkipVought,

I managed to sort the problem out another way,

but thanks for the help

Cheers

Sam