Compile error: expected end of statement

[qlan]

Hi,

I have
SQLquery = SQLquery &"("Not IsNull (tblProjects.ProjectCompletedDate)" & "Or Not IsNull (tblProjectBusinessTypes.CompletedDate)" )as [CompletedDate], "

It give me this error "compile error: expected end of statement".

Basically, I want CompletedDate to include if IsNull (tblProjects.ProjectCompletedDate) Then tblProjectBusinessTypes.CompletedDate Else tblProjects.ProjectCompletedDate

Thanks in advance for your help

 

[rjoubert]

SQLquery = SQLquery & "(Not IsNull (tblProjects.ProjectCompletedDate) Or Not IsNull (tblProjectBusinessTypes.CompletedDate)) as [CompletedDate]"

 

[TheAceMan1]

How are ya qlan . . .

For what you've posted, Perhaps this:

[blue]SQLquery = SQLquery & " ((tblProjects.ProjectCompletedDate Is Not Null) Or (tblProjectBusinessTypes.CompletedDate Is Not Null)) as [CompletedDate], "[/blue]

See Ya! . . . . . .

 

[qlan]

I tried the second option and my CompletedDate now show 2 records for the same project. CompletedDate with -1 and 0. The first option didn't return any record.

 

[TheAceMan1]

qlan . . .

There's two much [blue]hidden[/blue] in the SQL to tell. Do a [blue]Debug.Print SQLquery[/blue] after the sql is fully concatenated and post what appears in the [blue]Immediate Window[/blue] . . .

See Ya! . . . . . .

 

[qlan]

SQLquery = SQLquery & " ((tblProjects.ProjectCompletedDate Is Not Null) Or (tblProjectBusinessTypes.CompletedDate Is Not Null)) as [CompletedDate], "

For some reasons, it returns true/false. If there is tblProjects.ProjectCompletedDate or tblProjectBusinessTypes.CompletedDate, it then returns -1 else it returns 0. What I need it to show is the actual dates not the value of 0 or 1. thanks

 

[PHV]

Perhaps something like this ?
SQLquery = SQLquery & " Nz(tblProjects.ProjectCompletedDate, tblProjectBusinessTypes.CompletedDate) AS [CompletedDate], "

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[qlan]

Thanks so much! it works.