problem in expressions

Hello,

When i select jan month i should display december and previous year using expressions. plz advice me.

You can do that pretty easily using the DateAdd function. The trick is to subtract a month from an appropriate date.

You can do this whether you start with a real date or just a part of a date, it just takes a few preliminary steps if you don't have a real date.

In your case, let's assume that you are "selecting jan month" using a drop down of some sort, so you need the preliminary steps. You can put together a value representing the month in your drop down together with a day value of 1 plus a string value of the current year, to represent a dummy date to be subtracted from.

Like this (where MySelection would be 1 for January):

Code Snippet

=CSTR( MySelection) & "/1/" & CSTR(YEAR(NOW))

... you will need to adjust that string concatenation for your locale -- this is m-d-y format.

Now you can cast that string to a real date

Code Snippet

=CDATE(CSTR( MySelection) & "/1/" & CSTR(YEAR(NOW))))

... and you can subtract a month from that date, using DateAdd

Code Snippet

=DateAdd(DateInterval.Month,-1, CDATE(CSTR( MySelection) & "/1/" & CSTR(YEAR(NOW)))))

... and finally you can format the result to appear however you want (in this case, if MySelection is 1, you will see December 2006, but if it is 2 you will see January 2007).

Code Snippet

=Format(DateAdd(DateInterval.Month,-1, CDATE(CSTR( MySelection) & "/1/" & CSTR(YEAR(NOW))))),"MMMM yyyy")

HTH,

>L<

problem counting multiple occurrances of a pair of entries

Apologies for the previous post - I hit the wrong mouse button

I was having problems writing up this query

I had a table with columns A B C i needed to write up a query that would count all occurrances of unique A,B pair entries in the table

for eg:

A B C
red pink x
red pink y
green blue z
red pink a
green yello b
green blue c

The query should return

red pink 3
green blue 2
green yello 1

I hope that helps... any help would be appreciated

Thanks in advanceOK...really simple now...just cut and paste in to QA

USE Northwind
GO

CREATE TABLE myTable99 (A varchar(10),B varchar(10),C varchar(10))
GO

INSERT INTO myTable99 (A,B,C)
SELECT 'red', 'pink', 'x' UNION ALL
SELECT 'red', 'pink', 'y' UNION ALL
SELECT 'green','blue', 'z' UNION ALL
SELECT 'red', 'pink', 'a' UNION ALL
SELECT 'green','yello', 'b' UNION ALL
SELECT 'green','blue', 'c'

SELECT A,B,COUNT(*)
FROM myTable99
GROUP BY A,B
GO

DROP TABLE myTable99
GO