SQL statement dosent work with dates

[habbabub]

Why doesent this work
aa = Me.Due
aa = DateAdd("m", aa, Date)

Tasklist.RowSource = "SELECT [Tasks].Staff_Name, [Tasks].Project_Title, [Tasks].Percentage_Complete FROM [Tasks] WHERE [Tasks].Date_Allocated < " & aa & " ORDER BY [Tasks].Staff_Name;"



the sql dosent display any results!!!

 

[boblarson]

What value is Me.Due?

DateAdd("m",aa, Date) would assume that aa is a number. Is it?

 

[habbabub]

me.due is a number from a drop down list eg. 1 2 3 or 4

 

[boblarson]

So, if you put in a breakpoint and then put your cursor over aa when you break at the SQL statement, what does it show?

Also what does the SQL statement show when you break just after it. Use your debug tools to see what exactly is coming out, because it might not be what you are thinking.

 

[habbabub]

aa= 2
and the sql is

"SELECT [Tasks].Staff_Name, [Tasks].Project_Title, [Tasks].Percentage_Complete FROM [Tasks] WHERE [Tasks].Date_Allocated < #4/5/2008# ORDER BY [Tasks].Staff_Name;"

 

[habbabub]

please help

 

[WayneRyan]

HB,

If you get your Tasks table in Design View, is Date_Allocated really defined
as a date.

If it is, and there really are qualifying records, I'd like to see you post a sample
of your database.

Wayne

 

[habbabub]

yea the fields are defined as dates but my suspision lies in the sql statement

"SELECT [Tasks].Staff_Name, [Tasks].Project_Title, [Tasks].Percentage_Complete FROM [Tasks] WHERE [Tasks].Date_Allocated < #4/5/2008# ORDER BY [Tasks].Staff_Name;"

this one works fine ( with date() )

"SELECT [Tasks].Staff_Name, [Tasks].Project_Title, [Tasks].Percentage_Complete FROM [Tasks] WHERE [Tasks].Date_Allocated < DATE() ORDER BY [Tasks].Staff_Name;"

 

[habbabub]

btw i appreciate the "HB" abbreviation haha

 

[WayneRyan]

HB,

I'd venture to guess that your default date format is not dd/mm/yyyy.

It's a Windows setting, but I don't deal with it at all.

The solution would be to use:

Format(DateAdd("m",aa, Date), "dd/mm/yyyy")

The Format function will "force" the date string into the structure that you require.

Wayne

 

[habbabub]

but from the input for the sql string im getting is already in that format. do u need to actaully specify the format in VB for it to work?
the sql string is as below

"SELECT [Tasks].Staff_Name, [Tasks].Project_Title, [Tasks].Percentage_Complete FROM [Tasks] WHERE [Tasks].Date_Allocated < #4/5/2008# ORDER BY [Tasks].Staff_Name;"

 

[habbabub]

is the sql statement correct?

 

[WayneRyan]

HB,

Yes, it is correct.

Is 4/5/2008 April 5, 2008?

Wayne

 

[habbabub]

no actually im on australian time, which is may 4th 2008

 

[habbabub]

is it nesseary to have #4/5/2008#

the "#"?

 

[WayneRyan]

HB,

I still think the Format function is what you need here. You can format the
DateAdd function to match your data.

Can you post a small sample of your DB?

Wayne