Incorrect syntax at my strsql coding

[dennis1989]

hmm i tried to delimit the date but it still have the same error
Incorrect Syntax near the keyword 'in'

[vb5prgrmr]

Argh! DENNIS!!!! Build your string, ADD THE DEBUG.PRING strSQL... WHAT DOES IT SAY???? Do you see where there MIGHT BE two words without a SPACE between them!!!????

[dennis1989]

sorry i will try it now .im really newbie in programming

[dennis1989]

i got error 3001 when i add debug.print. argument are of the wrong type

[vb5prgrmr]

Okay, then... use a message box to display the string...(then you can make a screen shot by ALT+PrntScrn)



Good Luck

[dennis1989]

u mean just show u the strsql string or the entire coding ?

[vb5prgrmr]

Just the string that you pass...

[BlackLibrary]

Here is the code you need instead.

On Error GoTo errhandle

' Get Start Date & End Date

StartDt = CDate(Format(Ra nge("A4").Value , "dd-MM-yyyy") & " 00:00:00")
EndDt = CDate(Format(Ra nge("A4").Value , "dd-MM-yyyy") & " 23:59:59")

strSq1 = "SELECT DISTINCT activitydesc,ma terialid FROM activity_table " & "WHERE activityid in (Select distinct activityid from sample where(sampledt BETWEEN " & StartDt & " AND " & EndDT &")"


' Re-Connect to Database

The issue is specifically with the variables.

1. You need to give them the right values first, -before- you build the SQL string.
2. You SQL string did not break out the variables from the string properly.

When you pass a SQL string to be executed, it passes the entire string as it computes. If you don't break out your variables properly, in this case, your date range variables, it will choke on them.

In this case, your date range was just the WORDS, StartDt and EndDt, when really you wanted the VALUE of StartDt and EndDt respectively.

[dennis1989]

Thanks for the help black library but i still have that syntax error at the keyword in "in"

strSql = "SELECT DISTINCT activitydesc,ma terialid FROM activity_table" & "WHERE activityid in " & _
"(SELECT DISTINCT activityid FROM sample where(sampledt BETWEEN " & startdt & " AND " & enddt & ")

[dennis1989]

i manage to solve the initial problem with ur coding but my coding is have error 31 on this code :

errhandle:
MsgBox Err.Number & " : " & Err.Description , vbCritical, "Error!"
bolConnected = False
If Not sqlConnection Is Nothing Then
Set sqlConnection = Nothing
End If
End Sub

[dennis1989]

strSql = "SELECT DISTINCT activitydesc,ma terialid FROM Activity " & _
"WHERE activityid in " & _
"(SELECT DISTINCT activityid FROM sample where(sampledt BETWEEN " & startdt & " AND " & enddt & ")) " & _
"WHERE sampledt BETWEEN CONVERT(DATETIM E, '01/18/2010', 102) AND CONVERT(DATETIM E, '01/18/2010 23:59:59', 102))"


I revised my code yet i still have syntax error at keywords 23.
Can someone help me?
Thanks in advance

[debasisdas]

Display the strSql to a message box and post he screen shot.

[dennis1989]

Some changes to my project now, instead of using the combobox to display the value, i need to display the value in excel cells. Wonder if anyone can help me with the code on my first post. Thanks in advance