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

 

[Chance1234]

Not clear from the above but somehting like
....
qryDate = qryMonth & "/" & qryDay & "/" & qryYear

str_sql = "SELECT COUNT * FROM SIEBEL.S_EVT_ACT WHERE X_LOGIN_BRANCH LIKE'____' and date =#" & qrydate & "# AND X_call_type LIKE 'inboud%';"

rstcount.open str_sql, conMain



Chance,

Filmmaker, taken gentleman and crunch day + 22

 

[cjpicc11]

hmm.. Didnt seem to work what does the expression stand for. Sorry I'm still learning.

 

[RoyVidar]

Chance forgot turning the bold (written as [ignore]bold[/ignore]) off, which leaves the initial ...

Now - I think Chance's point is that
1 - the criterion needs to involve the name of your datefield
2 - delemit the criterion with octothorpes (#)

[tt]...and YourDateField = #" & qrydate & "# ...[/tt]

BTW - you know that underscore is the other ADO wildcard (in addition to %)?

Roy-Vidar

 

[Chance1234]

those pesky closing tags !

Chance,

Filmmaker, taken gentleman and crunch day + 22

 

[cjpicc11]

Still receiving that runtime invalid Expression error with running:


Set rstCount = conMain.Execute(" SELECT COUNT (*) FROM SIEBEL.S_EVT_ACT WHERE X_LOGIN_BRANCH LIKE '__' and date = #" & qryDate & "# AND X_call_type LIKE 'inbound%")

 

[PHV]

Set rstCount = conMain.Execute(" SELECT COUNT (*) FROM SIEBEL.S_EVT_ACT WHERE X_LOGIN_BRANCH LIKE '__' and date = #" & qryDate & "# AND X_call_type LIKE 'inbound%[!]'[/!]")

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

 

[cjpicc11]

Nope, still generating error. Thanks for the help guys, I really appreciate it.

 

[PHV]

If the backend is MS-Access:
Set rstCount = conMain.Execute("SELECT COUNT (*) FROM SIEBEL.S_EVT_ACT WHERE X_LOGIN_BRANCH LIKE '__' and [!][[/!]date[!]][/!] = #" & qryDate & "# AND X_call_type LIKE 'inbound%[tt]'"[/tt]
If the backend is SQL Server:
Set rstCount = conMain.Execute("SELECT COUNT (*) FROM SIEBEL.S_EVT_ACT WHERE X_LOGIN_BRANCH LIKE '__' and date =[tt] [!]'" & Format(Now - 7, "yyyy-mm-dd") & "'[/!][/tt] AND X_call_type LIKE 'inbound%[tt]'"[/tt]

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

 

[Chance1234]

Is it possible you can post a row of your data with the column headings ?

Chance,

Filmmaker, taken gentleman and crunch day + 22

 

[cjpicc11]

I dont know if this is what you were looking for.


Week of 1/16 Monday 1/16 Tuesday 1/17 Wednesday 1/18 Thursday 1/19 Friday 1/20 Saturday 1/21 Totals
9000 27 23 12 6 11 0 79

 

[Chance1234]

the actual data you are running the query against ?

Chance,

Filmmaker, taken gentleman and crunch day + 22

 

[cjpicc11]

ROW_ID CREATED CREATED_BY LAST_UPD LAST_UPD_BY DCKING_NUM
MODIFICATION_NUM CONFLICT_ID ACTIVITY_UID ALARM_FLAG
ALLOW_BREAK_FLG APPT_REPT_FLG BEST_ACTION_FLG BILLABLE_FLG
CAL_DISP_FLG DO_NOT_ROUTE_FLG EMAIL_ATT_FLG EMAIL_FORWARD_FLG
INST_ALL_OCCR_FLG ROW_STATUS SCHED_LOCKED_FLG
STATUS_RPT_FLG TEMPLATE_FLG TMSHT_RLTD_FLG ASGN_MANL_FLG
ASGN_USR_EXCLD_FLG ACD_CALL_DURATION ACD_DISC_FLG
ACD_INBOUND_FLG ACD_LOG_ENTRY_DT ACD_LOG_ENTRY_TM ACD_WAIT_TM
APPT_ALARM_TM_MIN APPT_CALL_FLG APPT_DURATION_MIN APPT_END_DT
APPT_END_TM APPT_REPT_END_DT APPT_REPT_FREQ APPT_REPT_FRI_FLG
APPT_REPT_MON_FLG APPT_REPT_SAT_FLG APPT_REPT_SUN_FLG
APPT_REPT_THU_FLG APPT_REPT_TUE_FLG APPT_REPT_WED_FLG
APPT_REPT_WK_MO APPT_START_DT APPT_START_TM ASGN_DNRM_FLG ASGN_DT
ASGN_SYS_FLG ASSESS_1 ASSESS_10 ASSESS_11 ASSESS_12
ASSESS_13 ASSESS_14 ASSESS_15 ASSESS_16 ASSESS_17
ASSESS_18 ASSESS_19 ASSESS_2 ASSESS_20 ASSESS_3
ASSESS_4 ASSESS_5 ASSESS_6 ASSESS_7 ASSESS_8
ASSESS_9 ASSOCIATED_COST CAL_DISP_DUE_FLG COST_EXCH_DT
DURATION_HRS EST_RMNG_WRK_TM EXP_RLTD_FLG LEAD_TM MANDATORY_FLG
PCT_COMPLETE PRIV_FLG TODO_ACTL_END_DT TODO_ACTL_END_TM
TODO_ACTL_START_DT TODO_ACTL_START_TM TODO_AFTER_DT
TODO_AFTER_TM TODO_DUE_DT TODO_DUE_TM TODO_PLAN_END_DT
TODO_PLAN_END_TM TODO_PLAN_START_DT TODO_PLAN_START_TM
TRAVEL_TM_MIN TTL_EXP_AMT WORK_TM_MIN ACCNT_ISSUE_ID
ACCNT_OBJCTV_ID ACCNT_SRC_ID ACT_TMPL_ID AGREEMENT_ID
APPT_REPT_APPT_ID APPT_REPT_REPL_CD APPT_REPT_TYPE ASSESS_FOR
ASSET_ID CALL_ID CAL_TYPE_CD CAMP_CON_ID COMMENTS
COMMENTS_LONG COMM_SESSION_HNDL CON_PRDINT_ID COST_CURCY_CD
CREATOR_LOGIN DONE_FLG EMAIL_BCC_LINE EMAIL_CC_LINE
EMAIL_RECIP_ADDR EMAIL_RECIP_NAME EMAIL_SNDR_ADDR
EMAIL_SNDR_NAME EMAIL_TO_LINE EVT_PRIORITY_CD EVT_STAT_CD
LEAD_TM_UOM_CD LOC_DESC MDF_ALLOC_ID NAME OBJCTV_ID
OPTY_ID ORDER_ITEM_ID OWNER_LOGIN OWNER_OU_ID OWNER_PER_ID
PART_RPR_ID PAR_EVT_ID PERFRM_BY_OU_ID PERFRM_BY_PER_ID
PM_ACTION_ID PREV_ACT_ID PRI_LST_ID PROJ_ID PROJ_ISS_ID
PROJ_ITEM_ID PR_ATT_ID PR_CON_ID PR_EXPITEM_TYPE_ID
PR_EXP_RPT_ID PR_ORDER_ID PR_PRDINT_ID PR_SYMPTOM_CD
PR_TMSHT_LINE_ID PUBLISH_CD QUOTE_ITEM_ID RATE_LST_ID
RPLY_PH_NUM SD_ID SRA_DEFECT_ID SRA_RESOLUTION_CD SRA_SR_ID
SRA_STAT_CD SRA_TYPE_CD SRC_ID SRV_REGN_ID TARGET_OU_ADDR_ID
TARGET_OU_ID TARGET_PER_ADDR_ID TARGET_PER_ID TMPL_PLANITEM_ID
TODO_APPT_ID TODO_CD USER_MSG_ID WC_START_VIEW WC_TYPE_CD
X_SALES_CALL_SIGNIFICANCE X_SUB_TYPE X_URGENT X_CALL_OBJ
X_CALL_TYPE X_CALLER_FIRST_NAME X_CALLER_LAST_NAME X_COMMENTS
X_COORDINATOR_NAME X_DATE_TIME_NEEDED X_DELIVERY_PU X_DURATION
X_DURATION_TYPE X_EMAIL_ADDRESS X_EMP_NUM X_EQUIPMENT_NEEDED
X_ERF_FUEL X_FOLLOW_UP_NEEDED X_JOB_LOCATION X_JOB_NUMBER
X_LOGIN_BRANCH X_MAIN_PHONE_NUMBER X_PHONE_NUMBER_TYPE
X_PICKUP_NUM X_PO_NUMBER X_POST_TO_PLAN X_RATE_QUOTED X_RATE_TYPE
X_RESULT_COMMENTS X_SALES_REP_BRANCH X_SEND_TEXT

 

[Chance1234]

just a few! which one is the date field ?

Chance,

Filmmaker, taken gentleman and crunch day + 22

 

[cjpicc11]

Sorry about that, the date field that I would like to pull this information off of is ASSGN_DT

 

[Chance1234]

Str_sql = "SELECT Count(SIEBEL.S_EVT_ACT) AS CountOfS_EVT_ACT
FROM SIEBEL
GROUP BY SIEBEL.ASGN_DT, SIEBEL.X_LOGIN_BRANCH, SIEBEL.X_CALL_TYPE
HAVING (((SIEBEL.ASGN_DT)=#" & qryday & " #) AND ((SIEBEL.X_LOGIN_BRANCH) Like "__") AND ((SIEBEL.X_CALL_TYPE) Like "Inbound*")); "

Chance,

Filmmaker, taken gentleman and crunch day + 22

 

[cjpicc11]

I'm getting a missing Expression error when running
("SELECT Count(*)from SIEBEL.S_EVT_ACT where X_LOGIN_BRANCH like '__' and HAVING (((SIEBEL.ASGN_DT)=#" & qryDate & " #) AND (SIEBEL.X_CALL_TYPE) like 'Inbound%'")

...which is coming from "Having

 

[Chance1234]

has qrydate got its value ?

Chance,

Filmmaker, taken gentleman and crunch day + 22

 

[cjpicc11]

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

 

[PHV]

Perhaps this ?
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%'")

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