Cannot filter on Date

[#1]

Hi

It's been a while since I used QV, and I am quite rusty!

I have an excel file that ( for this example) contains 2 fields, the date and a site derscription

So, I load 'Date' and 'Site', as a 'tempMain', , reload as 'Main' to allow the ORDER BY and then use a date script I found on the web to generate dates from the earliest date in the 'tempMain' table

using...

MasterCalendar:

load

load TempDate as Date,

week(TempDate) as Week,

Year(TempDate) as Year,

...etc

Now, I create two tables, linked by the key field 'Date'

BUT

When I select "Year" , ALl my "sites are deselected, but in theory, if I select 2011 in Year, the key should link back to all the dates in the Main that are in 2011

... shoudl it not..?..!

Assistance please!

[Anonymous]

Most of the time this means that your 2 date fields are not the same format.

Try to find it in this.

If you can share your QV-document I will take a look.

[erichshiino]

you can press Ctrl+T to check the links between tables.

On your main table you have TempDate or Date as a date field?

I guess it's not properly linked.

Maybe, you could use just :

load

load TempDate,

week(TempDate) as Week,

Year(TempDate) as Year,

etc

Hope it helps,

Erich

[ali_hijazi · Accepted solution]

if your date column in the excel sheet contains values with time then if you use the num function to get the numeric representation of your date then it will give you a decimal value

whereas if you use the num with the TempDate of the master calendar it will give you an integer

to solve this problem use the following on the column of the excel sheet date(floor(columnName))

the floor function takes the integer part of the date and the date() function gives you the date format that can be linked with the TempDate of the master calendar

[#5]

Thanks All. Every response was useful, in that it told me what to looko for and gave me the solution