Select Distinct Sql statement

[jjsteele]

I have used this example http://www.mrexcel.com/board2/viewtopic.php?t=94118&highlight=combo to select data using 3 combo boxes. But I need it to only show data that has a checkbox ticked. I have tried adding in another variable to the sql statement but it gives an error of too complicated?

This is my orignal sql statement from the first combobox

SELECT DISTINCT [tblAppointmentSlots].[TestLocation] FROM tblAppointmentSlots WHERE ((([tblAppointmentSlots].[Available])='Yes')) And ((([tblAppointmentSlots].[Test])=[txtTest]));

This is my modded version, I am trying to make it only show distinct values where available (checkbox) is ticked or yes.

SELECT DISTINCT [tblAppointmentSlots].[TestLocation] FROM tblAppointmentSlots WHERE ((([tblAppointmentSlots].[Test])=[txtTest]));

jjsteele

 

[SydneyGeek]

Looks like you grabbed the wrong statement to delete -- try

SELECT DISTINCT [tblAppointmentSlots].[TestLocation] FROM tblAppointmentSlots WHERE (([tblAppointmentSlots].[Available])='Yes'));

By the way, did you do this in straight SQL or using the graphical query interface? If you're just getting started with SQL, I'd recommend the interface first. Get the query doing pretty well what you need, then go to SQL view and add the DISTINCT keyword to give you the unique values.

Denis

 

[jjsteele]

Ok got the code round the wrong way but corrected below with a better explaination.

This is my orignal sql statement from the first combobox

SELECT DISTINCT [tblAppointmentSlots].[TestLocation] FROM tblAppointmentSlots WHERE ((([tblAppointmentSlots].[Test])=[txtTest]));

So I am saying for the combo box show distinct testlocation values from the tblAppointmentSlots Table where the value of test is equal to the value in txtTest Textbox. Thats All fine and works as required

This is my modded version, I am trying to add the additional requirement of the value in available of tblAppointmentSlots table equal to yes (as its a tickbox). Its this code that does not work

SELECT DISTINCT [tblAppointmentSlots].[TestLocation] FROM tblAppointmentSlots WHERE ((([tblAppointmentSlots].[Available])='Yes')) And ((([tblAppointmentSlots].[Test])=[txtTest]));

jjsteele

 

[SydneyGeek]

Hi, try this:

SELECT DISTINCT [tblAppointmentSlots].[TestLocation] FROM tblAppointmentSlots WHERE ((([tblAppointmentSlots].[Available])='Yes')) And ((([tblAppointmentSlots].[Test])=TRUE));

Denis