Referenial Integrity (Access 2000 SP2)

Topic

New Reply

I have a table for timesheets that looks back to three tables to pull names. The look backs are for “Client Name”, “Employee Name”, and “Acccount Name”. Each of these three tables has an “ID” set to autonumber that corresponds to the name. When I go to establish the relationships, I can only set the referential integrity for “Employee Name”. When I go to set it for the other two I get an error message that there is no unique index. I’m assuming its because there is already a unique index that is a number set when the referential integrity was set for “Employee Name”, however I’m not sure if this is the case. When I originally set up the Name tables to look back to, I didn’t establish an ID as an autonumber, but instead set the name as the primary key as I didn’t want duplicates. This became an issue with similar names like “John Smith”, etc. I was going to use SS#’s however the end user doesn’t always obtain those so that was an issue. My reason for going back and adding the ID’s to each table was due to problems I was having with reports. The report issue is now clear, however I’d like to set the referential integrity back again. Any thoughts???

Thank you,
Leesha

Reply | Quote

Replies

An AutoNumber field is always unique, but it’s not automatically the primary key of a table.

Open your “Client Name” table in design view.
Click in the ID field.
Then click the “Primary Key” button in the toolbar (it looks like a key). This makes the ID field into the primary key field of the table; you will see a tiny key symbol to the left of the field name.
Close the table and save the design changes.
Open the Relationships window.
You should now be able to set referential integrity for the relationship between the Timesheets table and Clients table.

Repeat these steps for the “Account Name” table.

Reply | Quote

Morning Hans!

I already did the steps you recommended multiple times. I’ve tried making the primary key the name vs the ID number and that doesn’t work either. I’ve never had this happen before so its stumped me to no end which was I thought maybe it had to do with the three name tables that the Timesheet table references, each having fields called ID. It doesn’t help that this is an already established database with tons of data in it (which of course I have an original saved of), vs one I’m developing from scratch. To make matters worse I’ve already set up multiple reports using your help from the other day to my post referencing using list boxes to open reports, so pulling the ID catagory will reduce me insanity!!! Ugh. Now what??

Leesha

Reply | Quote

Leesha,

This kind of problem can be very frustrating, and it’s hard to investigate form a distance. Could you post a screenshot of the Relationships window as it is now?

(If you don’t know how to make screenshots, open the Relationships window and type Alt+PrintScreen. This will place a picture of the active window on the clipboard. Then, open Word and paste the picture into a blank document. Save the document. If it is less than 100 KB, you can attach it directly to a reply; if it is too big, zip it and attach the zip file. For later, you might do a search in the Software Finds and Wants forum to search for free screen capture programs.)

Reply | Quote

I’ve attached the preview shot you requested.

Thanks,
Alicia

Reply | Quote

Wow! This is really going to take a lot of work to get right. It’s up to you to decide whether you want to do it. In the end, your database structure will have been improved, but a lot of things will get broken before you are there…

tblTimesheet now has a field Client Name, which is a text field, linked to Client Name in tblClientDemographics. It should be a numeric field (Long Integer) linked to the ID field, but you can’t just change it into a numeric field.

Start by adding a new field to tblTimesheet.
Name: ClientID
Type: Numeric (Long Integer)

Save & close the table.

Next, create a new query in design view. Add tblTimesheet and tblClientDemographics. They should be linked on Client Name.
Make the query into an update query (Query menu).
Add the ClientID field from tblTimesheet to the query grid.
Set the Change To to [tblClientDemographics].[ID]
Run the query (menu option Query/Run). If you get an error message, there are problems with your data. Otherwise, Access will ask for confirmation to save … records. Click OK.

Switch to the database window, and open tblTimesheet. Check that the ClientID field has been filled. Close the table again.
Open the Relationships window, and delete the existing relationship between tblTimesheet and tblClientDemographics.
Add a new one from ID in tblClientDemographics to ClientID in tblTimesheet, and try to set referential integrity.

If all is well up to now, you can open tblTimesheet in design view again, and delete the Client Name field.
Set the Caption property of ClientID to Client Name.
In the Lookup tab, change the display type of ClientID to Combo Box.
Set the Row Source to tblClientDemographics.
Set the number of columns to 3
Set column widths to 0″;1″;1″.
Move the ClientID field up to the position of the former Cliebnt Name field.
Save the table design. The table should be OK now, but…

You will have to modify everything that uses the Client Name field in tblTimesheet: queries, forms and reports.

Reply | Quote

Well, I’m a glutton for punishment! That’s what I get for helping a friend out!!! I appreciate the input and of course the perfectionist in me will make me fix it so I’ll be getting to work on it tonight.

Thanks!
Leesha

Reply | Quote

Hi Hans,

Well, I followed your steps for all three tables (Client, Account, Employee) and the referential integrity is set and all the forms etc. changed and running fine. My ONLY issue is that in all three tables (client, account and employee)the name field changes to the ID number on a whim, only one at a time in each database. IE, if I’m running account related reports, the account name will change to the ID number that is associated with it. Any idea where that is coming from?

Leesha

Reply | Quote

Do you mean that after you have run an account-related report, the Account Name field in tblAccount has actually been modified to contain ID’s instead of names? Or is it a matter of ID’s being displayed instead of names? Or something else?

Reply | Quote

Morning!

The Account Name Field in tble Account has actually been modified to contai ID’s instead of names. I went back and rechecked all queries and found 2 that didn’t actually have links between the tables and thought that might of been it, and yet it is still happening. I’m not sure where to begin to look. I’m about ready to trash the whole database and rework it but the owner has sooooooooooooo much data in it!

Thank you
Leesha

Reply | Quote

Frankly, I don’t see how this can happen unless you have an incorrect update query.

Reply | Quote

Nope, the update query went fine. Not one error message and the I went back and checked the data and it was valid. To make matters worse, it only changes the the data in one line item at a time and it varies. It doesn’t go back and replace the ID number for every cell in the database. Sigh, gonna be a long day.

Thanks!
Leesha

Reply | Quote

Next problem……………..I’m slowly, very slowly working through the form, query and report changes that are a result of the tblClient, tblAccount and tblEmployee ID changes I made for referential integrity. I’m stuck on list that when clicked on it would go to the line item for that row in the database. Now when the list opens, the ID numbers vs the actual names show. I’ve tried adding the associated name fields from the corresponding tables and although that helps, for some reason it limits the amount of data that shows up. There are no parameters placed on the query such as date range etc. When I run the actual query in design view it shows the name. It’s only in the list box view that only the numbers show. Is there a way for me to change this???

Thank you,
Alicia

Reply | Quote

You must include the ID and name in the Row Source of the list box, and set the Column Widths property to 0″;1″ (the second number isn’t important, you can change it if you like, but the first must be 0). This will hide the ID column, while still using it as the “value” of the list box.

Reply | Quote

God I appreciate you quick replies. The rowsource data comes from tblTimesheets which no longer has ClientID, AccountID, and EmployeeID now in it as numeric values which lookback to the names in the tblClient, tblEmployee, and tblAccount so that the names show in the combo boxes on the form. I removed the actual Client Name, Employee Name, and Account Name from tblTimesheet after the referential links were set as per your instructions yesterday (thanks for the detail). Should I have kept these?

Thanks,
Leesha

Reply | Quote

Here’s a shot of how tblTimesheets looks.

Leesha

Reply | Quote

No, it’s right that you have removed Client Name etc. from tblTimesheet – it’s redundant information since you already have ClientID etc. in the table.

If you want the names of ALL clients/employees/accounts to be displayed in the list box, set the Row Source to the corresponding table tblClientDemographics etc.
If you want only the names of clients/… that are present in tblTimesheet, set the Row Source to an SQL string like the one below, or create a query with that SQL and use that as Row Source.

SELECT DISTINCT tblClientDemographics.ID, tblClientDemographics.[Client Name] FROM tblClientDemographics INNER JOIN tblTimesheet ON tblClientDemographics.ID = tblTimesheet.ClientID;

(You can copy and paste this, but it’s also possible to create this SQL string by clicking the Builder button (the three dots …) on the right hand side of the Row Source property.

Reply | Quote

Finally!! I had tried your suggestion earlier but kept getting limited versions of the records with not all listed. When I’d take out the actual name links I’d get the entire databse. I made a few changes to properties (I’ve made so many that I don’t even remember what I’ve done at this point, and now its working!! Thanks so much for you patience. I have a feeling this won’t be the last post I make on this database!

Leesha

Reply | Quote

OK, I told you’d I’d be back. This database is quite large and the owner would like to limit the list box in the AR sheet to only show records within a specified date range that they enter. If I put non-bound “To” and “From” nonbound input boxes on the form, how would you suggest entering the code? The present rowsource code for the list box is as follows:

SELECT tblTimeSheet.AutoNumber, tblEmployee.[Employee Name], tblTimeSheet.ClientID, tblTimeSheet.AccountId, tblTimeSheet.Date, tblClientDemographics.[Client Name], tblAccountDemographics.[Account Name], tblTimeSheet.EmployeeID, tblTimeSheet.Paid
FROM tblEmployee INNER JOIN (tblClientDemographics INNER JOIN (tblAccountDemographics INNER JOIN tblTimeSheet ON tblAccountDemographics.ID = tblTimeSheet.AccountId) ON tblClientDemographics.ID = tblTimeSheet.ClientID) ON tblEmployee.[Auto Number] = tblTimeSheet.EmployeeID
ORDER BY tblEmployee.[Employee Name], tblTimeSheet.Date, tblClientDemographics.[Client Name], tblAccountDemographics.[Account Name];

I cut and pasted this directly from the database SQL view so I know this piece runs appropriately. Now I just need to code for the dates.

Thanks,
Leesha

Reply | Quote

Try this (added line in bold):

SELECT tblTimeSheet.AutoNumber, tblEmployee.[Employee Name], tblTimeSheet.ClientID, tblTimeSheet.AccountId, tblTimeSheet.Date, tblClientDemographics.[Client Name], tblAccountDemographics.[Account Name], tblTimeSheet.EmployeeID, tblTimeSheet.Paid
FROM tblEmployee INNER JOIN (tblClientDemographics INNER JOIN (tblAccountDemographics INNER JOIN tblTimeSheet ON tblAccountDemographics.ID = tblTimeSheet.AccountId) ON tblClientDemographics.ID = tblTimeSheet.ClientID) ON tblEmployee.[Auto Number] = tblTimeSheet.EmployeeID
WHERE tblTimeSheet.Date Between Forms!frmMyForm!From And Forms!frmMyForm!To
ORDER BY tblEmployee.[Employee Name], tblTimeSheet.Date, tblClientDemographics.[Client Name], tblAccountDemographics.[Account Name];

You must replace frmMyForm by the name of the form that contains the list box, and if necessary, To and From by the exact names of the unbound text boxes.

Reply | Quote

Thanks Hans! Will give it a shot.

I just posted a new thread re the issue I’m having with numbers replacing the Account name. There is no ryhme nor reason to how its happening!

Thanks,
Leesha

Reply | Quote

This is the code I put behind the command button. I don’t get error messages, however the listbox becomes completely blank????

Me.List48.RowSource = _
“SELECT tblTimeSheet.AutoNumber, tblEmployee.[Employee Name], tblTimeSheet.ClientID, tblTimeSheet.AccountId, tblTimeSheet.Date, tblClientDemographics.[Client Name], tblAccountDemographics.[Account Name], tblTimeSheet.EmployeeID, tblTimeSheet.Paid” & _
“FROM tblEmployee INNER JOIN (tblClientDemographics INNER JOIN (tblAccountDemographics INNER JOIN tblTimeSheet ON tblAccountDemographics.ID = tblTimeSheet.AccountId) ON tblClientDemographics.ID = tblTimeSheet.ClientID) ON tblEmployee.[Auto Number] = tblTimeSheet.EmployeeID” & _
“WHERE tblTimeSheet.Date Between Forms!frmtblTimesheet!Startdate And Forms!frmtblTimesheet!Enddate” & _
“ORDER BY tblEmployee.[Employee Name], tblTimeSheet.Date, tblClientDemographics.[Client Name], tblAccountDemographics.[Account Name]”

Leesha

Reply | Quote

Insert a space within the strings at the end of the lines, otherwise in the concatenated string, there is no space between Paid and FROM, and between EmployeeID and WHERE, etc.

Me.List48.RowSource = _
“SELECT tblTimeSheet.AutoNumber, tblEmployee.[Employee Name], tblTimeSheet.ClientID, tblTimeSheet.AccountId, tblTimeSheet.Date, tblClientDemographics.[Client Name], tblAccountDemographics.[Account Name], tblTimeSheet.EmployeeID, tblTimeSheet.Paid ” & _
“FROM tblEmployee INNER JOIN (tblClientDemographics INNER JOIN (tblAccountDemographics INNER JOIN tblTimeSheet ON tblAccountDemographics.ID = tblTimeSheet.AccountId) ON tblClientDemographics.ID = tblTimeSheet.ClientID) ON tblEmployee.[Auto Number] = tblTimeSheet.EmployeeID ” & _
“WHERE tblTimeSheet.Date Between Forms!frmtblTimesheet!Startdate And Forms!frmtblTimesheet!Enddate ” & _
“ORDER BY tblEmployee.[Employee Name], tblTimeSheet.Date, tblClientDemographics.[Client Name], tblAccountDemographics.[Account Name]”

Now, if you have dates in both StartDate and EndDate (and if there are records whose Date field falls in between, of course) you should see something.

If it still doesn’t work, force the list box to update by adding an instruction below the one with the RowSource:

Me.List48.Requery

(PS Please give your list box a better name than List48 – if somobody is going to look at the database in the future, “List48” is not very self-explaining)

Reply | Quote

Are you sick of me yet? I got error messages looking for parameters for frmtblTimesheet!Startdate and the same for enddate.

RE the listbox name……………ya I know. I’m updating this database for a friend and have had so many problems with it I didn’t want to change too much. I normally name everything. Fortunately there are only two listboxes on the form and I’ve spent so much time with them that I have them memorized!

Leesha

Reply | Quote

If

• frmtblTimesheet has exactly that name
• It is open
• The text boxes on the form are named StartDate and EndDate spelled exactly like that (except for capitalization, that doesn’t matter)[/list]it should work – I have many forms used to select a date range using this method. Check very carefully for typos – in your posts you have used both tblTimesheet and tblTimesheets.

Reply | Quote

Thank you for the clarification. I’m bleary eyed for working on this for the past 10 hours. I must’ve misunderstood.

I have a form called frmTimeSheet and a table called tblTimeSheet. The form “frmTimeSheet” is open and that is the one that I have the unbound listbox and unbound text boxes. When I changed the name to frmTimeSheet I still get the parameter questions and for the heck of it I tried tblTimeSheet and got the same error.

Leesha

Reply | Quote

Well, get some sleep before continuing to work on it. 10 hours straight is far too long!

After that, create a query with the SQL statement that acts as row source of the list box.
In the Query menu, select Parameters…
Enter Forms!frmTimeSheet!StartDate as parameters, and set the data type to Date/Time.
Do the same for Forms!frmTimeSheet!EndDate.
The parameters must have exactly the same spelling as the criteria.
Save this query.

Open the form, and enter dates in StartDate and EndDate. Don’t push the command button yet.
Next, open the query. Do you get prompted for parameters? If so, there must be some kind of spelling error…

If you don’t get prompted for parameters (and if the query returns the expected records), set the Row Source of the list box to the name of the query, and change the code behind the command button to List48.Requery (don’t set the Row Source any more)

Reply | Quote

Cute emoticons! OK, I’ll try to leave it alone for a bit and then come back to it to try your suggestion. I’m literally brain dead at this point. This is a hobby for me (as I’m sure you can tell) so if I don’t get this done on non work time it’ll be forever before I get back to my friend.

Gonna eat I think too! Haven’t done that either!!

Leesha

Reply | Quote

OK, did as instructed and when I ran the query (after putting in the dates on the form) I got a message that said “invalid bracketing of name”. I didn’t put in brackets. The query did it itself. I took them out and it happened again???????????

Leesha

Reply | Quote

Post the SQL of the query.

Pat

Reply | Quote

Pat,

I posted a question on popup forms. I’m not sure if you have seen it yet but if you could look at it and see if you have any ideas. You have always been a big help.

Reply | Quote

Try [Forms]![frmTimeSheet]![StartDate] and [Forms]![frmTimeSheet]![StartDate] both in the criteria line and in Query/Parameters. If that doesn’t help, post the complete SQL, as patt suggested.

Reply | Quote

Morning Hans!

Here’s the code and I sent a view of the query design as I’m not sure if I put the info in the criteria line the way you wanted.

PARAMETERS [Form]![tblTimeSheet]![Startdate] DateTime, [Form]![tblTimeSheet]![Enddate] DateTime;
SELECT tblTimeSheet.AutoNumber, tblEmployee.[Employee Name], tblTimeSheet.Date, tblClientDemographics.[Client Name], tblAccountDemographics.[Account Name], [STARTDATE] AS Expr1, [ENDDATE] AS Expr2
FROM tblEmployee INNER JOIN (tblClientDemographics INNER JOIN (tblAccountDemographics INNER JOIN tblTimeSheet ON tblAccountDemographics.ID = tblTimeSheet.AccountId) ON tblClientDemographics.ID = tblTimeSheet.ClientID) ON tblEmployee.[Auto Number] = tblTimeSheet.EmployeeID
WHERE ((([STARTDATE])=[Form]![frmTimeSheet]![STARTDATE]) AND (([ENDDATE])=[Form]![frmTimeSheet]![ENDATE]));

Leesha

Reply | Quote

(Edited by patt on 21-Apr-03 07:41. Changed the Where to show between)

I noticed you had STARTDATE and ENDDATE in your query, they are undefined, I would take them out for a start. I would have thought you would be testing tblTimeSheet.Date against the two form dates. Also you have not defined the form correctly in the PARAMETERS line, you should use the word Forms and not Form and the form name is not tblTimeSheet but frmTimeSheet (I’m assuming here).

I think your query should probably read:

PARAMETERS [Forms]![frmTimeSheet]![Startdate] DateTime, [Forms]![frmTimeSheet]![Enddate] DateTime;
SELECT tblTimeSheet.AutoNumber, tblEmployee.[Employee Name], tblTimeSheet.Date, tblClientDemographics.[Client Name], tblAccountDemographics.[Account Name]
FROM tblEmployee INNER JOIN (tblClientDemographics INNER JOIN (tblAccountDemographics INNER JOIN tblTimeSheet ON tblAccountDemographics.ID = tblTimeSheet.AccountId) ON tblClientDemographics.ID = tblTimeSheet.ClientID) ON tblEmployee.[Auto Number] = tblTimeSheet.EmployeeID
WHERE (((tblTimeSheet.Date) Between[Forms]![frmTimeSheet]![b]Startdate[/b]) And ([Forms]![frmTimeSheet]![b]Enddate[/b]));

I hope I have highlighted all the problems.

HTH
Pat

Reply | Quote

Hi Pat, Happy Easter,

I cut and copied the code you sent (yes it is frmTimeSheet not tblTimeSheet) and I still get a question asking me to define the Startdate and enddate parameters. I entered the dates into their respective unbound textboxes on frmTimeSheet and then ran the query and got the error. Where should I go from here?

Thanks for your help,
Leesha

Reply | Quote

And a Happy Easter to you too.

So you have the form frmTimeSheet open. What are the 2 date fields on the form called (their Control names) ?

If you have all the names correct it should work.

Pat

Reply | Quote

I have frmAccountsReceivable open. On it is a list box that has the SQL code and query view I posted. Clicking the list box bookmarks the appropriate record from the underlying table, in order that it may be updated and payments applied. The records that show on the form and that are in the listbox are from tblTimeSheet. I am trying to give the end user the option of inserting dates to sort/limit the number of records that show in the listbox, between the dates they choose. The boxes are undbound and named Startdate and enddate. Han’s suggestion was to enter the code to requery the listbox behind a control button. The requery would be based on the query that you and he are helping me to get right.

Leesha

Reply | Quote

You will have to change the form name in your query to frmAccountsReceivable if that is where the 2 unbound list boxes (StartDate and EndDate) are.

Pat

Reply | Quote

I just replaced frmTimesheet with frmAccountsReceivable and no longer get errors, however nothing comes up in the query. Here is the code:

PARAMETERS [Forms]![frmAccountsReceivable]![Startdate] DateTime, [Forms]![frmAccountsReceivable]![Enddate] DateTime;
SELECT tblTimeSheet.AutoNumber, tblEmployee.[Employee Name], tblTimeSheet.Date, tblClientDemographics.[Client Name], tblAccountDemographics.[Account Name]
FROM tblEmployee INNER JOIN (tblClientDemographics INNER JOIN (tblAccountDemographics INNER JOIN tblTimeSheet ON tblAccountDemographics.ID = tblTimeSheet.AccountId) ON tblClientDemographics.ID = tblTimeSheet.ClientID) ON tblEmployee.[Auto Number] = tblTimeSheet.EmployeeID
WHERE (((tblTimeSheet.Date)=[Forms]![frmAccountsReceivable]![Startdate] And (tblTimeSheet.Date)=[Forms]![frmAccountsReceivable]![Enddate]));

Thanks,
Leesha

Reply | Quote

Change your WHERE statement to :

WHERE (((tblTimeSheet.Date) Between [Forms]![frmAccountsReceivable]![Startdate] And [Forms]![frmAccountsReceivable]![Enddate]));

Pat

Reply | Quote

No errors but no data either.

PARAMETERS [Forms]![frmAccountsReceivable]![Startdate] DateTime, [Forms]![frmAccountsReceivable]![Enddate] DateTime;
SELECT tblTimeSheet.AutoNumber, tblEmployee.[Employee Name], tblTimeSheet.Date, tblClientDemographics.[Client Name], tblAccountDemographics.[Account Name]
FROM tblEmployee INNER JOIN (tblClientDemographics INNER JOIN (tblAccountDemographics INNER JOIN tblTimeSheet ON tblAccountDemographics.ID=tblTimeSheet.AccountId) ON tblClientDemographics.ID=tblTimeSheet.ClientID) ON tblEmployee.[Auto Number]=tblTimeSheet.EmployeeID
WHERE (((tblTimeSheet.Date) Between [Forms]![frmAccountsReceivable]![Startdate] And [Forms]![frmAccountsReceivable]![Enddate]));

Leesha

Reply | Quote

I’m am going to ask some dumb questions, but bear with me.
Are you sure there are dates in the tblTimeSheet table that are between [Forms]![frmAccountsReceivable]![Startdate] and [Forms]![frmAccountsReceivable]![Enddate]?
The other thing to look at are the joins. For example, if there are no records in one of the tables for records in the tblTimeSheet table then you will not get records where you use INNER JOINs. You may have to change your joins to a LEFT or a RIGHT join.

Pat

Reply | Quote

First there is no such thing as a dumb question! Second, I appreciate your bearing with me!

The dates in tblTimeSheet start with 11/5/02 and go through the present. There are 1400+ records. I’ve been entering the a range of dates that are 30 days in size so as to capture data. I’m sure that there are dates for the time frame I’m trying to look at. I’ve even tried a variety of dates and still not data. We’ve made progress in that at least there are no errors! As far as right and left joins, this is really outta my league. What would I do?

Thanks,
Leesha

Reply | Quote

>>I’m sure that there are dates for the time frame I’m trying to look at.<<
Can you just make sure there are dates in the date range? What is your date range?

If there are records found in table tblTimeSheet for the range selected, can you make sure there are related records in the other tables.

Let's just establish if there are any records first before we go and change the joins.

Pat

Reply | Quote

<<Can you just make sure there are dates in the date range?

Yep, I'm sure.

<<What is your date range?

I've tried a variety of ranges. IE 11/1/02 – 11/31/02; 1/1/03-1/31/03 and so on. For Novemeber alone there are roughly 285 records.

<>Let’s just establish if there are any records first before we go and change the joins.

Hitting the sack. Will check in the am to see if you have any ideas. I’m blind from looking at this computer screen working on this database. Thanks so much for the help.

Leesha

Reply | Quote

Sounds like you need your sleep, sweet dreams, we will solve this, whoever ends up helping you.

If you don’t have a corresponding record in the other tables defined by your joins then you will not get any records from your query.

Pat

Reply | Quote

Morning Pat,

In answer to your question re the joins, when I run the query without “startdate” and “enddate” defined I get all the records in the database. So, I’m confident that the join works. It’s only when I enter in the nonbound input boxes ‘startdate” and “enddate” on the form frmAccountsReceivable, and then enter them into the query that I loose the data.

Off to a three day nsg meeting. Ugh. Won’t be home till this evening to get this right. Probably a good thing for my eyes to not stare at a computer screen for a bit, but I’m itching to get this fixed.

Have a good one,
Leesha

Reply | Quote

Let me suggest a simple test, as I suspect your date values are not being interpreted correctly from the form. Try entering the dates as #11/01/2002# and 11/30/2002#. The “#” symbol should force the recognition of a date/time value – I suspect it’s being treated as text instead. Hope this helps.

Reply | Quote

Hi Alicia,

Patt and Wendell have given you excellent advice while I was away.

I know that it’s difficult to keep things straight when you’re very tired, but it also makes helping you much more difficult if you provide misleading information. As it turns out, you form has a completely different name from what you gave us yesterday.

I suspect that there is still something different from what ypu have told us. I created a small demo and pasted your query into the row source of the list box and it worked – without any modification! I have attached it; it makes no pretension whatsoever at being complete or elegant. If you open the form, the list box is empty, but if you enter dates in the text boxes in the form footer, for instance 1/1/2003 in the Start date and 4/1/2003 in the End Date, the list box should display some records. The list box does nothing; this is just a demo.

Reply | Quote

Hans, Pat & Wendell…..THANK YOU!! Sure enough the date input boxes weren’t formatted, and once I set the date to short date the query ran. It just about killed me to be at a nursing related training all day and having to wait to get home to see what new advice there would be. I’m on my face and will TRY to leave this project alone till tomorrow night. Unfortunately, my friend needs it back with the changes ASAP. Sorry Hans if I frustrated you. I appreciate the help and the patience. In the process I learned a great deal and from the number of people who read these posts, hopefully so did a lot of other folks.

Leesha

Reply | Quote