MS Query

MarkAndrews

Well-known Member
Joined
May 2, 2006
Messages
1,963
I have a problem with MS Query

It isn't copying some data over (which derive from an excel formula & is numbers)

Any idea's?

TIA
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

lozzablake

Well-known Member
Joined
Dec 15, 2005
Messages
818
What do you mean by not copying. Do you mean it is not displaying all the records that you expect to receive back?
 

MarkAndrews

Well-known Member
Joined
May 2, 2006
Messages
1,963
I have a sheet full of data, I use MS Query to extract the data i need (via a validation cell)

I then use this query & combine various countif's etc for my data colation & charts
Abort - Jan.xls
ABCDEFGHIJ
1ClientIDIntroducerIDMatterIDStartDateAbortDateAuthActDateAuthactReceivedClientNetworkdaysIntroducer
2104056378503/01/06NULLNULLNoGenericSAP LegalMove
3104056378703/01/06NULLNULLNoGenericSAP LegalMove
4213356379003/01/06NULL08/02/06YesFirstActive(REM) FirstActive
5213356379103/01/0610/01/06NULLNoFirstActive(REM)6FirstActive
Jan


Column E - Dates are not being displayed in the SQL Query, nor the networkdays

(Above example is of the raw data before running it through the query)

I have redone the query to no avail

I am stuck!

Thanks
 

lozzablake

Well-known Member
Joined
Dec 15, 2005
Messages
818
If you right click on the query and choose edit query and then go through the options until you get to disply in Excel or edit query, choose edit query.

This displays the SQL query window. Is the data still showing as NULL in that. If so then your underlying data has Nulls and you need to fix that.
 

MarkAndrews

Well-known Member
Joined
May 2, 2006
Messages
1,963

ADVERTISEMENT

This is my generated SQL

Code:
SELECT `Data$`.ClientID, `Data$`.IntroducerID, `Data$`.MatterID, `Data$`.StartDate, `Data$`.AbortDate, `Data$`.AuthActDate, `Data$`.`Authact Received`, `Data$`.Client, `Data$`.Networkdays, `Data$`.Introducer
FROM `P:\MI & Analysis\Abort - Jan`.`Data$` `Data$`
WHERE (`Data$`.Introducer Like ?)
 

lozzablake

Well-known Member
Joined
Dec 15, 2005
Messages
818
You need to disitnguish between whether it is a problem in Excel or a problem in the underlying data. Is AuthAcDate allowed to have nulls in the underlying data? If so then Excel is just displaying what is there.
 

MarkAndrews

Well-known Member
Joined
May 2, 2006
Messages
1,963

ADVERTISEMENT

You need to disitnguish between whether it is a problem in Excel or a problem in the underlying data. Is AuthAcDate allowed to have nulls in the underlying data? If so then Excel is just displaying what is there.

AuthAcDate has Null & dates in the raw data - see below data

I need it to show both NULL & a date (if there is one in the column) in the query
Abort - Jan.xls
FGHI
1AuthActDateAuthactReceivedClientNetworkdays
2NULLNoGenericSAP 
3NULLNoGenericSAP 
408/02/06YesFirstActive(REM) 
5NULLNoFirstActive(REM)6
6NULLNoGenericSAP 
7NULLNoGenericSAP 
8NULLNoGenericSAP 
9NULLNoGenericSAP 
10NULLNoHalifax 
11NULLNoGenericSAP163
12NULLNoOffsetNatWest(PAD)13
13NULLNoGenericSAP 
14NULLNoGenericSAP 
1510/01/06YesGenericSAP3
16NULLNoWOL78
17NULLNoWOL6
1825/01/06YesWOL133
19NULLNoWOL56
2031/01/06YesWOL93
21NULLNoWOL32
22NULLNoWOL133
23NULLNoHalifax4
24NULLNoWoolINT2
25NULLNoGenericSAP3
2606/01/06YesBBS152
27NULLNoGenericSAP73
28NULLNoOffsetRBS(REM)77
Data
 

lozzablake

Well-known Member
Joined
Dec 15, 2005
Messages
818
Sorry, I do not understand the problem. If there is a null in the date field then there is clearly going to be a null in networkdays.

The formulae are showing the right results. Do you want them to show something else?
 

MarkAndrews

Well-known Member
Joined
May 2, 2006
Messages
1,963
The Null, Is the word Null

It's not copying (if and when) there are dates in that column, which is what i need

The networkdays works from this column and the start date - these are not also displaying in the MS Query (query)

I need both the Null & Date (if there is one) displaying in the query, to get the networkdays result for my chart..?
 

Watch MrExcel Video

Forum statistics

Threads
1,113,795
Messages
5,544,337
Members
410,603
Latest member
rseckler
Top