SQL question

[bennybee]

im trying to use an SQL statement to filter a dropdown box on a form.

the following is the row source value for the field Meeting_Subject on a form:

SELECT DISTINCTROW [Meeting_Agenda].[Subject] FROM [Meeting_Agenda] WHERE HearingMeeting_Date=[Meeting].[Meeting_Date];

there is a field on my form called HearingMeeting_Date. where i want the user to select from a drop down box, the date which they want to view the Meeting_Agenda subjects.

if i remove the WHERE clause, it lists all the subjects for the agendas, but not specifically the ones relating to the date selected in the "HearingMeeting_Date" field.

if i have the WHERE clause, i get prompted for a parameter value saying
"Meeting.Meeting_Date".

how can i use this query to select only those subjects that relate specifically to the meeting date?
i want it to look at the HearingMeeting_Date entered on the form, and only select the subjects which relate to this date.

please help!!

 

[Pat Hartman]

You need to reference the form field properly. I also removed DISTINCTROW and qualified the column in the where clause.

SELECT [Meeting_Agenda].[Subject] FROM [Meeting_Agenda] WHERE [Meeting_Agenda].[HearingMeeting_Date] = Forms!YourFormName![Meeting_Date];

Don't forget to requery the combo in the AfterUpdate event of [Meeting_Date].

 

[bennybee]

ok i think that i have found my problem.

this is what i have:

I have a table - Meeting, and another table Meeting_Agenda.
on the form the Meeting_Agenda is a subform of Meeting.

the meeting_agenda table is linked to the meeting table by a field called "Meeting_Number", so each record stored in the subform (meeting_agenda) is given a meeting number.

the meeting table stores teh date of the meeting, so thats the value that i need to reference against.

i tried this:
SELECT [Meeting_Agenda].[Subject] FROM [Meeting_Agenda], [Meeting] WHERE [Meeting_Agenda].[Meeting_Number]=[Meeting].[Meeting_Number] AND Forms!Hearing_Agenda![HearingMeeting_Date] = [Meeting].[Meeting_Date];

but it returns a blank answer... however it doesnt prompt for a response now. just gives a blank response.

is it because im trying to find a value that is not linked?

how can i fix this?

 

[Pat Hartman]

If it doesn't prompt for an answer, that means that the form you are referencing is open and Jet has found the control You probably don't have the correct value in the form field. Also, your query does not have a proper join.

SELECT [Meeting_Agenda].[Subject] FROM [Meeting_Agenda] Inner Join [Meeting] ON [Meeting_Agenda].[Meeting_Number]=[Meeting].[Meeting_Number] WHERE [Meeting].[Meeting_Date] = Forms![Hearing_Agenda]![HearingMeeting_Date];

 

[bennybee]

Im still having the same problem.
it is giving me a blank response in the Meeting_Subject field in the Hearing_Agenda field...

is it possible that i can email this to you to have a look at and see it in more detial, cause once i get this part done, then im on the downward stretch as far as my database project is concerned.

atm the file is too big to send to the forum, so can i email it to someone? please?

 

[Pat Hartman]

I don't understand what you are saying. The query doesn't contain either the field or the table -

Meeting_Subject field in the Hearing_Agenda field

Copy the SQL from your actual rowsource query and post it here.