IsBlank Funcion

[Kali]

Hello

I have a general question. I have a table with 5 fields. I want to get the
service date of these 5 fields and exclude if the data is blank. For example,
the 1st fields will have data for one row, but it will be blank for the
others. I want to bring that date in and then 2nd field, bring in the row
with data and ignore data that are blank. I tried to group it, the problem
if I group it, it will bring in all the fields even the one that are blank.
I tried using the ISNOTBlank function, but it didn't work.
-K

 

[KARL DEWEY]

Post sample of your data and what you want the query results to look like.

 

[Kali]

Karl

My access table looks has the 4 fields and 3 rows below:
Acess# Term InServiceDate PurchaseDate SoldDate
30 2/19/09 blank blank
30 blank 5/10/08 blank
30 blank blank 4/29/06

Right now, if I do groupby , I get 3 rows of data, and NotNull is not
working b/c DatType mismatch(the datatype is Date/Time) but I want 1 row
showing

Acess# InServiceDate PurchaseDate SoldDate
30 2/19/09 5/10/08 4/29/06

 

[Kali]

2/19/2009 3:46 PM PST



Karl
Sorry, my kid hit Post. Hope my formatting is okay when you view it.


My access table looks has the 4 fields and 3 rows below:
Acess# Term InServiceDate PurchaseDate SoldDate
30 Long 2/19/09 blank blank
30 Long blank 5/10/08 blank
30 Long blank blank
4/29/06

Right now, if I do groupby , I get 3 rows of data, and NotNull is not
working b/c DatType mismatch(the datatype is Date/Time).
I want 1 row showing below, ignoring blank cells

Acess# Term InServiceDate PurchaseDate SoldDate
30 Long 2/19/09 5/10/08 4/29/06

I should also mention that the dataType is Number/Text/and Date/Time
respectively for the 5 fields.
--
-K


:

 

[KARL DEWEY]

Try two queries --
[Kali-1]
SELECT Kali.[Acess#], Kali.Term, Kali.InServiceDate as ActionDate,
"InServiceDate" AS DateType
FROM Kali
WHERE Kali.InServiceDate Is Not Null
UNION SELECT Kali.[Acess#], Kali.Term, Kali.PurchaseDate as ActionDate,
"PurchaseDate" AS DateType
FROM Kali
WHERE Kali.PurchaseDate Is Not Null
UNION SELECT Kali.[Acess#], Kali.Term, Kali.SoldDate as ActionDate,
"SoldDate" AS DateType
FROM Kali
WHERE Kali.SoldDate Is Not Null;


TRANSFORM First([Kali-1].[ActionDate]) AS FirstOfActionDate
SELECT [Kali-1].[Acess#], [Kali-1].[Term]
FROM [Kali-1]
GROUP BY [Kali-1].[Acess#], [Kali-1].[Term]
PIVOT [Kali-1].[DateType];

 

[Kali]

Thanks for replying, although I thought there would be an easier way to do
this.
I am not sure how to set the actionDate or how I can start going about what
you suggested. I will continue trying, thanks.
--
-K

Try two queries --
[Kali-1]
SELECT Kali.[Acess#], Kali.Term, Kali.InServiceDate as ActionDate,
"InServiceDate" AS DateType
FROM Kali
WHERE Kali.InServiceDate Is Not Null
UNION SELECT Kali.[Acess#], Kali.Term, Kali.PurchaseDate as ActionDate,
"PurchaseDate" AS DateType
FROM Kali
WHERE Kali.PurchaseDate Is Not Null
UNION SELECT Kali.[Acess#], Kali.Term, Kali.SoldDate as ActionDate,
"SoldDate" AS DateType
FROM Kali
WHERE Kali.SoldDate Is Not Null;


TRANSFORM First([Kali-1].[ActionDate]) AS FirstOfActionDate
SELECT [Kali-1].[Acess#], [Kali-1].[Term]
FROM [Kali-1]
GROUP BY [Kali-1].[Acess#], [Kali-1].[Term]
PIVOT [Kali-1].[DateType];

--
KARL DEWEY
Build a little - Test a little

2/19/2009 3:46 PM PST



Karl
Sorry, my kid hit Post. Hope my formatting is okay when you view it.


My access table looks has the 4 fields and 3 rows below:
Acess# Term InServiceDate PurchaseDate SoldDate
30 Long 2/19/09 blank blank
30 Long blank 5/10/08 blank
30 Long blank blank
4/29/06

Right now, if I do groupby , I get 3 rows of data, and NotNull is not
working b/c DatType mismatch(the datatype is Date/Time).
I want 1 row showing below, ignoring blank cells

Acess# Term InServiceDate PurchaseDate SoldDate
30 Long 2/19/09 5/10/08 4/29/06

I should also mention that the dataType is Number/Text/and Date/Time
respectively for the 5 fields.
--
-K


:

 

[Kali]

I tried the query you suggested,but the dates are not coming up in a single
row that I need, instead on 3 different rows. I need the dates side by side.
I hope that make sense.
--
-K

Try two queries --
[Kali-1]
SELECT Kali.[Acess#], Kali.Term, Kali.InServiceDate as ActionDate,
"InServiceDate" AS DateType
FROM Kali
WHERE Kali.InServiceDate Is Not Null
UNION SELECT Kali.[Acess#], Kali.Term, Kali.PurchaseDate as ActionDate,
"PurchaseDate" AS DateType
FROM Kali
WHERE Kali.PurchaseDate Is Not Null
UNION SELECT Kali.[Acess#], Kali.Term, Kali.SoldDate as ActionDate,
"SoldDate" AS DateType
FROM Kali
WHERE Kali.SoldDate Is Not Null;


TRANSFORM First([Kali-1].[ActionDate]) AS FirstOfActionDate
SELECT [Kali-1].[Acess#], [Kali-1].[Term]
FROM [Kali-1]
GROUP BY [Kali-1].[Acess#], [Kali-1].[Term]
PIVOT [Kali-1].[DateType];

--
KARL DEWEY
Build a little - Test a little

2/19/2009 3:46 PM PST



Karl
Sorry, my kid hit Post. Hope my formatting is okay when you view it.


My access table looks has the 4 fields and 3 rows below:
Acess# Term InServiceDate PurchaseDate SoldDate
30 Long 2/19/09 blank blank
30 Long blank 5/10/08 blank
30 Long blank blank
4/29/06

Right now, if I do groupby , I get 3 rows of data, and NotNull is not
working b/c DatType mismatch(the datatype is Date/Time).
I want 1 row showing below, ignoring blank cells

Acess# Term InServiceDate PurchaseDate SoldDate
30 Long 2/19/09 5/10/08 4/29/06

I should also mention that the dataType is Number/Text/and Date/Time
respectively for the 5 fields.
--
-K


:

 

[John W. Vinson]

I tried the query you suggested,but the dates are not coming up in a single
row that I need, instead on 3 different rows. I need the dates side by side.
I hope that make sense.

Do note that Karl is suggesting *two separate queries* - a UNION query to
normalize your (unnormalized) data, and a Pivot Table query to recast them in
spreadsheet form. Did you use both queries?