Using form control as query criteria in report 2

[bfamo]

I've made a report that counts my customers based on type and location (district). What I want is to use a form to deside which district the query should show results for.

I'm having problems referring to the form in the query. Any help here would be great! The code is as follows:

        qry = "SELECT Count(*) AS Count01" & _
         " FROM TblCustomer WHERE " & _
         " [CustomerType] = 1" & _
	[Red] " AND [District] = forms!FrmEvaluering!CtrlCombo" [/Red]
         
    rs.Open qry, CurrentProject.Connection, adOpenStatic, adLockOptimistic
    Me.LblCount01.Caption = rs!Count01
    rs.Close

 

[Remou]

That would be

[tt]...
" AND [District] = '" & forms!FrmEvaluering!CtrlCombo & "'"[/tt]

If [District] is a text field, or

[tt]...
" AND [District] = " & forms!FrmEvaluering!CtrlCombo[/tt]

If it is numeric.

 

[bfamo]

[District] is a text field. I tried the code, but I get a compile error in visual basic; "Expected: line number or label og statement or end of statement"

thanks!

 

[HarleyQuinn]

Could you post your code with Remou's suggested changes in so we can see where it might be going wrong?

Cheers

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[bfamo]

        qry = "SELECT Count(*) AS Count01" & _
         " FROM TblCustomer WHERE " & _
         " [CustomerType] = 1"
         [Red]" AND [District] = '"[/Red] & forms!FrmEvaluering!CtrlCombo & "'"
         
    rs.Open qry, CurrentProject.Connection, adOpenStatic, adLockOptimistic
    Me.LblCount01.Caption = rs!Count01
    rs.Close

I've highlighted the bit of code that gives the compile error.

 

[HarleyQuinn]

Try adding the continuation character for the line above:

qry = "SELECT Count(*) AS Count01" & _
         " FROM TblCustomer WHERE " & _
         " [CustomerType] = 1" [red]& _[/red]
         " AND [District] = '" & forms!FrmEvaluering!CtrlCombo & "'"

Hope this helps


HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[bfamo]

yeah... that solved it

Thanks alot you guys

 

[HarleyQuinn]

Glad to help, thanks for the star

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.