SQL LIKE help, please, thank you very much

[thomaspatton]

To start with, I uploaded my entire Database (a WIP, so don't criticize too harshly). This database isn't going to be on a network or anything. Just locally used on a public external HD so it doesn't need the whole DB Security thing.

Posted here because it's too big in .zip even after compacting. (http://h1.ripway.com/thomaspatton/USAAAS/AMODatabase.zip)

Now for my issue :

I did have it setup to where I could open "INPUTfrmStudCounsel", type in a class date and type in a full name and it would pop open "frmStudCounsel" with the name of the student I needed. That was easily accomplished using the WHERE in SQL for queCounsel. But, I ran into some problems.

First off, what if two students had the same name? I tried it and when "frmStudCounsel" pops open, it's blank, even after changing it to a continuous form.

Second, what if someone can't spell worth a damn or only knows the first couple letters of a students name? I tried fixing that issue by using

WHERE [tblStudent].[LastName] LIKE '[Forms].[INPUTfrmStudCounsel].[txtTest]%' AND (([tblStudent].[FYClass]=[Forms].[INPUTfrmStudCounsel].[cboTest]));
but it just brings up a balnk form everytime, even with a full name.

Anyone that can take a look at my DB and give me some feedback on those problems, I love you long time, soldier boy.

I'm trying to juggle learning VBA, SQL and a little DAO out of a book for this DB and I'm losing my mind running into problem after problem. I just can't seem to figure out how to search/query using multiple, sometimes variable, values.:banghead:

Kill me please...

[Carl A]

This is a stripped down version of your database. I put a multicolumn ComboBox on your form and added a new query to populate the ComboBox with student information so a selection can be made based on the PrepSSN number. This will alleviate the problem with duplicate last names. However your PrepSSN field also allows duplicates. A unique field, which doesn?t allows duplicates, is usually preferable


:snowman: Semper Fi

[thomaspatton]

Thanks Carl. I see what ya did there, but unfortunately it won't work for this. I'm looking at having close to 2,000 names in there at any given time when this thing is finished. On top of that, the names will be contstantly rearranged as I purge records from 3yrs ago out of the tables.

I'm beginning to think it's not really going to possible to search for multiple variables without using DAO in my VBA. Thanks though.