Replies

Thanks for the link! What a great guide. I had already done the google xml file so that is all set.

Leesha

Hi!

I’m not doing it from the wizard, however I do have all of the rows, columns and values identified. Nothing I try works. I use crosstabs all the time so this one really has me stumped. It runs fine until I put in the filter of [forms]![frmReports]![txtyear]. If I don’t try to filter the year its OK. If I use this same filter on a select query it runs fine and filters as it should. It’s only when I try to do it in a crosstab query.

Leesha

Hi Andrew,
The form was open so that is why I wasn’t sure about adding in the tabbed page. I went back and hunted and found that at somepoint (God only knows why or when) I set the bound column to 2. Once I reset it to 1 the report ran.

Thanks!
Leesha

LOL! When it comes to me with sql Server don’t assume anything. It’s only by luck when I get things to work. I used Wendell’s sql and it works as long as CustomerID and Paid are not in the query. Once I put either or both of those I get an error related to “group by” which I never would’ve gotten past had it not been for your hint above. Once I changed that I was set.

Thanks to both of you!
Leesha

Hi Pat!

I’m running it in sql, not access. I need it for a asp.net web page.

Thanks,
Leesha

Thanks! That is what I needed!! Now I can see why I was getting zeros.

Leesha

Hi Hans!

The first one worked perfectly. Thanks to both of you for the explanations. My gosh is time complicated!!!

Thanks and Merry Christmas,
Leesha

Hi Hans,

I got an OBDC error stating something about using the convert function to run this query. This database has a few links to sql tables and don’t you know this is one of them. I can always create a temporary table using and Access table if there isn’t an easy way to change this code, which of course I would need your help on.

Also……………..my never ending need to understand how something works……………………..what is the forumula you gave me actually doing? I’ve not used “Int” before.

Thanks!
Leesha

OMG!!! That was simply too easy! I love it!

BTW, I didn’t get an email for either of these two responses. They aren’t in my spam either.

Thanks!
Leesha

If the sum of the times is greater than 24 hours, then you won’t be able to display it correctly as time. Times can’t be more than 24 hours because dates and times are stored as decimals with the whole number part representing the Days and the decimal the Hours.

I usually use something like ([endtime]-[startime])* 24 which converts it from time into a number of hours as a number. They can be added up easily, and give the right answer if multiplied by, say, a rate of pay.

That worked perfectly!!!

Thanks!
Leesha

Perfect! thanks!

The only problem with this recent code is that the row that has cells that are blank(not null) is filtred out leaving only the rows with names. Normally this would be fine but what I run into is that when the page loads, either the first or last student name is shown, depending on the sort order. I need this to show the row with no data WITHOUT the comma. This way if the user saves the page, without entering / choosing a name, the cell remains blank in that row. Otherwise, they end up saving the wrong name if they don’t make a change. It’s a pain I know. I really do hate asp.net and web basd pages!!!

Thanks,
Leesha

Thanks to both of you!

The database is actually pretty clean in that at the present time there are no spaces in the first and last names that I can tell anyway. There is one row that has no data in any of the fields. This is done on purpose so that when the asp.net page loads it doesn’t default to a record name, but rather to a blank field. The user is required to choose a name from the dropdownlist, they can’t save the page if no name is chosen. The problem I’m running into is that when there is a comma in the dropdownlist (which is the case when there LastName, FirstName MI are blank) the web page “thinks” that there is a name in the dropdownlist and allows the page to be save with just the comma and doesn’t force the user to choose a name. I need to somehow have the row appear without the comma, thus why I thought having the query return is as an empty cell would work. Does that make sense?

Leesha

Leesha,

Your code seems to be good as you can see in the attachment. Can you post your full SELECT statement ?
Added later :
Could it be that there are spaces in the fields LastName, FirstName or Mi ?

Hi Francois!
Yes, there are no values in those fields. In that instance, I need the comma not to be inserted, or I should say, the fields not to be joined together. It would ONLY be when nothing is in ALL THREE fields. Is that possible?

Thanks,
Leesha

To be honest, once you get the hang of them, they are just as easy as Access IIF’s.
In fact if you have multiple criteria then they can be a lot easier than the Access nested IIF functions.

Well I told you I’d be back. I’ll bet you didn’t figure it would be so quickly! The code I’m using works great with the exception of when LastName, FirstName and MI are all null. Then the query returns a comma only. I want this to return a null field with no comma at all. How would I write that? The code presently is:

SELECT TOP (100) PERCENT IDStudentName,
ISNULL(LastName + ‘, ‘, ”) + ISNULL(FirstName + ‘ ‘, ”) + ISNULL(Mi, ”) AS [Student Name], FROM dbo.tblStudentNames
ORDER BY [Student Name]

Thanks!
Leesha