Replies

Sure wish I could. It’s Friday and a beautiful day outside.

Hi–

Thanks for the try, but it’s not what I’m after. I have a table that shows who did what on what day. From there, I have a query that shows employees that worked on a weekend or holiday and the hours they reported working.

So my query ends up with

Emp1 Sunday
Emp1 Saturday
Emp2 Sunday
Emp3 Holiday
Emp3 Sunday

I want to return that there are three individuals reporting that they worked on a weekend or holiday. This is a bit closer to the real life query, so maybe it clarifies what I’m trying to do.

I _could_ get my result by making a group by query for EmployeeID, then counting the number of rows in the query. But I don’t want to use two queries because I’m programmatically changing everything step by step (I know I could change the querydefs, but I don’t want to do that either).

So what I want is the Access97 equivalent of Count(Distinct EmployeeID), which so far has not worked for me, despite the fact that it shows up on page 140 of the Access Developer’s Handbook, and I haven’t been able to find proof that it doesn’t work or a workaround for the issue.

If anyone has any other ideas….

Hi Gary….

The table (actually, it’s a query of a larger table) contains a list of employees with the days of the week (in reality, it just picks up employees that worked on a Sat, Sun, or a holiday) and the hours they worked that day. I need to count how many people worked on those days. The trouble is that there is a potential that someone could work Saturday and Sunday and a holiday, so their name shows up three times in the query. When you do a count, it picks up the number of records, not the number of unique individuals.

Almost. I’m looking for

Count(Distinct EmpNumber)

Which would return 2 as the result. But it doesn’t seem to work in Access 97, so I’m looking for an alternative and/or confirmation that it just doesn’t work.

If you do Count(EmpNumber), the answer is 4 since there are four records. I know you can do the above with two queries (first is a group by, then a count), but I’m trying to do it with just one query.

Sure, I do this all the time. You’ll want to look up something the Print # statement (in Access help):

Open strFileName For Output As hFile
Print #hFile, strHeader
Print #hFile, strTitle
Print #hFile, strTOC
Print #hFile, strText
Print #hFile, strFooter
Close #hFile

There’s also the TransferText methods.

Good luck!

Cecilia

Hi Hans!

Thank you so much for sharing this! It is incredibly more elegant than the original example (all those on formats and such–uggggh!).

Beautiful!

Cecilia

Hmmm. Wisdom=none. Humor, well, if sarcasm counts, I’ve got tons

You can actually put the function directly in the criteria of the query. Instead of just hte In string, make sure it includes the in and paren, then you can call the function directly from the design grid, ie. SelectedItems(Forms!myForm.listbox1)

To build a report, you can even use the results of the in string in the where….Something like

DoCmd.OpenReport “myreport”,,,,,(I don’t know how many commas) “Criteria in (” & SelectedItems(me.listbox1) & “)”

Or again, from the OnOpen of the report….
Me.recordsource=”Select field from table where criteria in (” & SelectedItems(me.listbox1) & “)”

Hope this makes sense….

This is the part where my brain cells stop working, because I’m a visual type person….

Are you trying to populate a listbox? Can you run through the steps that you want the user to make?

For instance, if I want to populate a listbox after the user selects something in a combo, I would (probably) use the afterupdate event of the combo to set the recordsource of the listbox and refresh the form/listbox. The function returns the IN part, so you’d have to modify it to have a whole select statement that would fit into the recordsource. (ie. listbox2.recordsource=”Select stuff where criteria in (” & SelectedItems(me.listbox1) & “)”

Although now I’m getting confused as to why we’re bouncing from control to control….wouldn’t that make your users a bit crazy?

Maybe someone else can jump in here?

This looks cool! I’m going to play with it, but I think between your example and the reference above, I’ve got it!

Thanks!

Cecilia

Hi!

The issue with the sheet tab is this: My report contains five rows at the top that have a top header, and the first two columns on the left are the left hand header. Then I have about 30 columns going across the page.

When I print the sheet, I want each sheet to have the top header and the left header. But since the top header only occurs above the first few columns, it doesn’t repeat itself across the pages.

Thanks for the reference, part of the struggle here is that I’m not even sure what I should be looking for!

Cecilia

Hi Charlotte–

I think I was going overboard with helping out the users. The problem is that some of us map our drives to different letters because we move around between regions and divisions. But most don’t, and don’t know how, and don’t even what a UNC path is. So in order for things to go correctly, I need to use the unc path, but a user wouldn’t know what that is, they’d think it was jibberish. So I wanted to return to the user the equivalent of where things were being saved, in a manner that he’d understand.

Instead of going through all that, I’ll probably just tell some “superusers” how to do it and let the others go to them for help changing drive paths.

Cecilia

Urrrrgh. I hadn’t thought of that, but….

We have every drive letter mapped and then some. But that’s not really the problem, part of it is that if you do a lot of travelling, or cross-divisional work (like I do), you might have drives mapped that are 3,000 miles away across a 28.8b (or less) Wan. I’ll try it, but I’m worried that between the number of drives mapped and the potential for getting bogged down on the Wan, it’ll be really slow.

I’m hoping there’s a neater way to do it.

I just wrote this out for the project I’m working on….

Public Function SelectedItems(ctl As Control) As String
‘Returns an IN string for selected items in a listbox
Dim strActivities As String
Dim varItem As Variant

strActivities = “”
If ctl.ItemsSelected.Count > 0 Then
For Each varItem In ctl.ItemsSelected
strActivities = strActivities & “‘” & ctl.ItemData(varItem) & “‘,”
Next varItem
End If
SelectedItems = Left$(strActivities, Len(strActivities) – 1)
End Function

Hope this helps,

Cecilia

Hi Charlotte,

Well, I’ve been pretty consistent about destroying objects. I’m checking the If statements now, how in depth do I need to go?

For instance, is
If IsNull(Forms!frmData.cboDivision)
okay, or do I need to change it to
If IsNull(Forms!frmData.cboDivision)=True
and similarly
If IsMissing(strDivision)
These are all over the help files.
The other one I have is If rst.NoMatch, should this be If rst.NoMatch=True?

TIA!

Cecilia

How about checking the characters by their chr codes?

Numbers start at chr(48) through chr(57), letters are chr(65) through chr(90) and chr(97) through (122), underscore is chr(94). You could either validate by going through each of these in a loop or use the before update to check the string.

Just an idea to make up for my dumbness earlier today

Cecilia