Replies

I believe that the field has been formatted with both date and time, which would account for no data on the last day of the month. I guess I have to look for “between 9/1/2002 and 10/1/2002” in order to get at the last day of the month.

I am connecting to SQL Server through ODBC within an MDB.

The data type of the field is char, so the length is only one character. Are you saying that my syntax should have worked? I even added the field to the recordset, with no positive result.

Looking back to my original post, I inadvertantly left out important information: When I run my code, I get a error stating “you have entered an expression that has no value”, with an error code of 2427. when I go to debug, the following code is highlighted:

If CLASS_LEV = “S” Then SLOT_DESC.Visible = False _
Else SLOT_DESC.Visible = True

Thanks to Claus and Tom for yourhelp. Modifying the suggestions to fit my application, here is what I came up with:

Private Sub lbxMembers_DblClick(Cancel As Integer)
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset(“TblActivitiesReg”)
rst.AddNew
rst!ClassID = “SELECT ClassID FROM qryClasses WHERE ClassDate = ‘” & Me!lstClassDate & _
“‘ AND ClassName = ‘” & Me!cboClassName & “‘;”
rst!ID = lbxMembers
rst.Update
rst.Close
Me!lstParticipants.Requery

The problem is that I am getting a Data Type Conversion error at the SELECT statement above. The fields of both tables (originating and receiving) are Long data types. I attempted to move the SELECT statement to a variable and then place the variable, but that didn’t work either. I tried changing the variable data type to Variant, and got a Data Type Mismatch error (as opposed to the Data Type Conversion error I got earlier).

Any thoughts??

Thanks for you help. Contacting Iomega was my next option, but wanted to find out if anyone else out there had heard of anything.

My ZIP is an internal ATAPI, but I plan to take the disks themselves to another computer to rule out their corruption. The Tools disk itself is a ZIP disk. I have access to the files on that disk, which is even more bizzare!! That’s why I need to rule out the corruption of the other disks.

Well, thanks for you help.

Dear Iomega…

Thanks for your reply. I know the format of the ZIP disk would not have been altered, but wasn’t sure about NTFS reading the ZIP disk. I know this shouldn’t be a problem, but wondered if this could be a Win2K bug of some sort.

The Tools disk is a ZIP disk that came with the ZIP drive–it contains all the initial tools and drivers. It is odd that I can access it, but not other disks. There is even a folder I created on that disk prior to my upgrade. I have access to it, but not the other disks. There were no problems with either of the other disks prior to the upgrade. It is too much of a coincidence for me that both disks failed simultaneously while I upgraded my OS.

I have been to the Iomega and Microsoft websites, but could find nothing useful. I downloaded the latest IomegaWare software, but it made no difference. Do you have any other suggestions?

Thanks for your reply. I already thought of using a LOOKUP table, but I don’t aleady have the values in electronic format. A lookup table will work well, but I was hoping to find a less entry-intensive solution. Probably no such animal exists. Is it possible to include a range of values in a single lookup row?

The 500 values appear to almost be random. For example, the following values map the category number 7: 1-9, 12-35, 45, 65, 214-215, 243, 484, 496-500. None of the other categories have any more of a pattern to it.

Currently, I do not have the values in electronic format. I may just have to manually create a LOOKUP table with all 500 values, but I was hoping to find a less entry-intensive solution. Is it possible to include a range of numbers within a single lookup row? If so, how would that work. Thanks.

Oops. As I would make changes to the formula, I forgot to make it an array. Your formula does work as designed. Thanks for everyone’s help. My first post to the Lounge was a rousing success!!

Your formula works if I specify exactly the rows that contain data. If any of the rows 1:50 are blank (for instance, my data ran out on row 49), the answer defaults to 1.

My data gets added to weekly, and therefore the number of rows grows weekly through the course of our FY. How do I get the range to grow dynamically as the amount of data grows.

It is organize into a simple database list.

Great!! This works, but I don’t understand how. I need to study up on Arrays!

Now, I need to go one step further. Is there a way to automagically calculate the rows that need to be counted. In the formula example you’ve given ‘A1:A50’ would change periodically. Is there a way to automate this?