Passing Parms 1

[goldygirl]

Dear Group,
Maybe somebody can help me out with a passing parameters issue.
Here what I have….

I have a form DateRange; This form has month and year parameters for report data selection…

cmdHC.CommandText =
"select sum([IViami]) as Total from netsales
where (((IVMKPL) IN('LA', 'LB', 'LC', 'QA', 'QC', 'FA', 'FB', 'FD', 'NA', 'NB', 'NC', 'PA', 'HA', 'PB', 'PC')))
and ihyear= '" & Forms![DateRange]![Year] & "'"
And IHmonth = '" & Forms![DateRange]![Month] & "'"
And CMCUSN = " & txtCustNum"

I’m getting a syntax error….

Thanks

 

[PHV]

If iHyear and iHmonth are numeric, get rid of the single quotes surrounding their values.

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

 

[goldygirl]

Year & Month are numeric fields...
I removed single quotes , but I am still getting a sintax error...

cmdHC.CommandText = "select sum([IViami]) as Total from netsales
where (((IVMKPL) IN('LA', 'LB', 'LC', 'QA', 'QC', 'FA', 'FB', 'FD', 'NA', 'NB', 'NC', 'PA', 'HA', 'PB', 'PC')))
and ihyear= " & Forms![DateRange]![Year] & "
And IHPERD = " & Forms![DateRange]![Period] & "
And CMCUSN = " & txtCustNum

 

[CajunCenturion]

It's hard to know exact spacing so you have a problem with spaces before you "And" clauses in the where clause.

Also, your quotes do not match up:
The first string begins is the following:
"select sum ... and ihyear= '"
You second string is simply the closing single quote after the ihear value
"'". What follows that is
And IHmonth = '"
but there is no starting quote. I would replace the ending double quote with a space to allow the and to continue in that string.
Same thing is occurring after the Month value, and what is the purpose of the quote after txtCustNum" ?

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[randy700]

Try this....

cmdHC.CommandText = 
"SELECT sum(iviami) as Total FROM Netsales " & _
"WHERE (((ivmkpl) IN('LA', 'LB', 'LC', 'QA', " & _
"'QC', 'FA', 'FB', 'FD', 'NA', 'NB', 'NC', " & _
"'PA', 'HA', 'PB', 'PC'))) " & _
"AND ihyear = " & intYear & " AND ihperd = " & intPeriod & _
"AND cmcusn = " & intCustNum

Randy

 

[goldygirl]

Thanks, it worked))))