Access Dates

[funkmonsteruk]

I've just made a Universe which is hooked up to an Access Database. Everything works fine, apart from one date field.

The problem is that i don't seem to be able to use it as a condition (I get a return of no data to fetch, when there is definitely data to fetch), I've checked it's properties and it can be used in a condition.... Also if i use it as a condition and ask BO to show a list of values, nothing happens - Very strange.

Even stranger is that in the same access table is another date field which seems to be identicle which works fine...

Anybody have any ideas????

 

[srobiolle]

Is the universe definition also in a Access table or in an Oracle one ?

The way the date is stored in the database can be different, and be interpreted in a wrong way by B.O.
What happens if you create in Designer the sub-variables Year/Quarter/Month related to your date, and the print them in a report ? Are they OK or scrambled ?

 

[funkmonsteruk]

It's all done on Access. I've tried adding in the year/quarter/month sub-variables and they are all right....

Very Very strange............

 

[srobiolle]

Can you give us the SQL statement ?

 

[Sridharan]

Is the LOV exported alongwith the Universe????

Sri

 

[funkmonsteruk]

Whats the LOV????

 

[funkmonsteruk]

This is the sql that is being generated:

SELECT
Will.TestatorID,
{fn concat(Testator.Surname & ', ',Testator.Forenames)}
FROM
Will,
Testator,
Event,
History
WHERE
( Testator.TestatorID=Will.TestatorID )
AND ( Will.WillID=History.WillID )
AND ( History.EventID=Event.EventID )
AND ( Testator.Deleted = No )
AND (
Event.EventDescription = '3 - Will/Codicil Signed Advice'
AND History.UpdatedDate = {d '2003-05-19'}
)

The above sql contains the dodgy date field.

The following sql contains the date field which works...

SELECT
Will.TestatorID,
{fn concat(Testator.Surname & ', ',Testator.Forenames)}
FROM
Will,
Testator,
Event,
History
WHERE
( Testator.TestatorID=Will.TestatorID )
AND ( Will.WillID=History.WillID )
AND ( History.EventID=Event.EventID )
AND ( Testator.Deleted = No )
AND (
Event.EventDescription = '3 - Will/Codicil Signed Advice'
AND History.EventDate = {d '2003-05-19'}
)

 

[Sridharan]

Hi,

LOV is List Of Values for an Object which will show up when you have prompt and you can choose from the values instead of users entering manually.

Have look at the Datatype of UpdatedDate and check whether it is same as EventDate.

Sri

 

[vaass]

It all depends on the prompt where u have given, if u have given the date prompt in Report level the format you have to use is the default BO Date Format, or if u have given the prompt in Universe level then you have follow the database format.