Convert String to Date Problem in SQL statement. 1

[kosala1981]

In the following statement i tried to send BeginningDate(textbox value) and

EndingDate(txtbox value) to the SQL stmnt but it gave an error saying "Type

Mismatch" even though i used CDate method. answer for this problem is greatly

appreciated......

"WHERE Project.tProjTitle='" + Trim(cboProjectTitle) + "' AND

Project.nProjId=Indicator.nProjId AND Indicator.tIndicatorName='" +

cboIndicator + "' AND Indicator.nIndicatorId=IndicatorData.nIndicatorId AND

Region.nRegionId=IndicatorData.nRegionId AND

Institute.nInstituteId=IndicatorData.nInstituteId AND" + _
"IndicatorData.indicDate Between '" + CDate(BeginningDate) +

"' AND '" + CDate(EndingDate) + "' " + _"GROUP BY

IndicatorData.nRegionId, " + Trim(Replace(cboRows, "_", ".")) + " "

 

[JerryKlmns]

SELECT ... " & _
"FROM Project As P, " & _
"Indicator As I, " & _
"IndicatorData As ID, " & _
"Region As R " & _
"WHERE P.tProjTitle='" & Trim(cboProjectTitle) & "' AND " & _
"P.nProjId=I.nProjId AND " & _
"I.tIndicatorName='" & cboIndicator + & "' AND " & _
"I.nIndicatorId=ID.nIndicatorId AND " & _
"R.nRegionId=ID.nRegionId AND " & _
"I.nInstituteId=ID.nInstituteId AND " & _
"ID.indicDate Between #" & CDate(BeginningDate) & "# AND #" & CDate(EndingDate) & "# " & _
"GROUP BY ID.nRegionId, " & Trim(Replace(cboRows, "_", ".")) & " "

You should debug.print your sql statement
You could write it in the QDE and copy it to a string variable in the module.

 

[kosala1981]

Thanx a lot myfriend.....

 

[JerryKlmns]

You are very welcomed!

Tips:

Use alias for table names to get sorter sql statements.

Equi-joins (joins in WHERE clause) return non-updatable queries. That goes too for GROUP BY, DISTINCT.

Thanx for the pinky