Incorporating Variables into an SQL Statement

[cjpicc11]

Hey guys I'm automating a report for work, and trying to incorporate a Date Variable into an SQL statement. Below is the Variable I have set up followed by the SQL Query I'm trying to run which errors out. The time is static because I need to run it for that full day, I just need to set the variable for the current month and date ("Now-7"). I would appreciate any help at all. Thanks in Advance.

qryDate = Now - 7
qryMonth = Month(qryDate)
qryDay = Day(qryDate)
qryYear = Year(qryDate)
qryDate = qryMonth & "/" & qryDay & "/" & qryYear

Set rstCount = conMain.Execute("select count(*)from SIEBEL.S_EVT_ACT where X_LOGIN_BRANCH like '__' and Qrydate and x_call_type like 'Inbound%'")
ExcelApp.Range("B4").CopyFromRecordset rstCount
rstCount.Close

 

[cjpicc11]

Still with the missing Expression error at "Having

 

[PHV]

Which "Having" ?
No having anywhere in the code I posted !

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

 

[cjpicc11]

Sorry copied wrong statement, tried yours and now getting invalid charachter.




Set rstCount = conMain.Execute("Select Count(S_EVT_ACT) From SIEBEL Where X_LOGIN_BRANCH Like '__' And ASGN_DT=#" & Format(Now-7, "yyyy-mm-dd") & "# And X_CALL_TYPE Like 'Inbound%'")

 

[PHV]

What is the RDBMS ?

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

 

[PHV]

I guess the invalid character is the #, so try to replace both with single quote.

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

 

[cjpicc11]

Error: Literal does not match format string
..when running:

Select Count (*) From SIEBEL.S_EVT_ACT Where X_LOGIN_BRANCH Like '__' And ASGN_DT=' " & Format(Now-7, "yyyy-mm-dd") & " 'And X_CALL_TYPE Like 'Inbound%'

 

[cjpicc11]

Hey guys Disregard last post I think that worked. Thanks for all your help

 

[PHV]

1)[tt] ...-7, "yyyy-mm-dd") & [highlight]"'[/highlight] And X_CALL...[/tt]
2) WHAT IS THE RDBMS ?

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

 

[DaveInIowa]

I don't worry about things like concatenation and double-quotes. I use something like the following technique whenever I build an SQL statement with substitution parameters. It seems to make it a lot easier to read.

    Dim SQL As String
    
    SQL = "SELECT COUNT(S_EVT_ACT) "
    SQL = SQL & "FROM SIEBEL "
    SQL = SQL & "WHERE X_LOGIN_BRANCH LIKE '__' "
    SQL = SQL & "AND ASGN_DT=#<AssignDate># "
    SQL = SQL & "AND X_CALL_TYPE LIKE 'Inbound%' "
    
    SQL = Replace(SQL, "<AssignDate>", Format(Now - 7, "yyyy-mm-dd"))

 

[PHV]

I think that worked
So, if I understand properly, you have the solution since 23 Feb 06 12:25 ?

 

[cjpicc11]

?

Data does not seem to be populating in my excel sheet. When I had a static date in my statement it was working fine. But I run this report weekly and need current data.