Problems with Listbox Rowsource in MS Access 2003

[cwhitcomb2]

I have created a form in my database that resembles a calendar. I have 42 textboxes that display the day of the week for whatever month and year is selected in the combo boxes at the top of the form. The text boxes are named Text1, Text2, etc. The combo boxes are named cboYear and cboMonth.

Here is the problem: I put a listbox under each textbox that should show the project# for the projects that are active on that day. I'm using 3 fields from my Projects table in the query: Project#, ProjectStart, ProjectFinish. Project# is an autonumber field and is the primary key for the Projects table. ProjectStart and ProjectFinish are date/time fields. Basically, what each listbox should return is the Project# for all projects between the ProjectStart and ProjectFinish date.

I have tried everything that I can think of to make this work. Everytime I run a query it tells me that it is too complex. Here are the details I've tried as rowsource and as a query:

SELECT Projects.[Project#] FROM Projects WHERE (((DateSerial([cboYear],[cboMonth],[Text1])) Between Projects.ProjectStart And Projects.ProjectFinish));

I cannot imagine that this query is too complex for Access to work with. I have been staring at this for three days but have had no luck. Any suggestions you can offer are greatly appreciated. Thanks!

[Rabbit]

To reference a value on a form you need to use Forms!FormName!ControlName

[cwhitcomb2]

How do I go about doing that?

I don't know how to rephrase the syntax to fit that in.

[Rabbit]

Well, instead of cboYear you use Forms!FormName!cboYear. The same goes for cboMonth and Text1.

[cwhitcomb2]

Alright,

I have exhausted all the possible ways to write a rowsource for this listbox. NOTHING is working. Nothing. I have tried to write it by refering to the controls on the form like you suggested and nothing. I tried to write it without using Forms!FormName!Control and still nothing. In fact, all this listbox seems capable of returning is nothing. Here is my current rowsource syntax:

SELECT Projects.[Project#], ProjectStart, ProjectEnd FROM Projects WHERE ((Forms!Calendar!Text3) Between Projects.ProjectStart And Projects.ProjectEnd) ORDER BY Projects.[Project#];

This is where I am now, I cannot think of any other way to do this and it's driving me crazy. Any suggestions are very much appreciated. THANK YOU!

[cwhitcomb2]

Oh, and Text3 is equal to this:

=DateSerial([cboYear],[cboMonth],[Text6])

And Text6 is equal to this:

=IIf(Text6.Tag=Weekday(DateSerial([cboYear],[cboMonth],1)),1,Text5+1)

[Rabbit]

Assuming the following:

1) You have 2 combo boxes named cboYear and cboMonth.

2) You have a textbox named txtDay.

3) You have a listbox named lstProjects.

4) They are all on the form Form1.

5) You want the listbox to list all projects that fall on the chosen date.

Then you do the following.

1) The listbox row source is:
SELECT WhatEverColumnsYouWant FROM SomeTable WHERE ProjectStartDate <= CDate(Forms!Form1!cboMonth & "/" & Forms!Form1!txtDay & "/" & Forms!Form1!cboYear) AND ProjectEndDate >= CDate(Forms!Form1!cboMonth & "/" & Forms!Form1!txtDay & "/" & Forms!Form1!cboYear)

2) In the AfterUpdate event of the two comboboxes and text box you requery the list box.