SQL Question (1 Viewer)

[Coldsteel]

I have been working on a form to do so quick calcuations for me. I built a SQL stament and a form for me to enter the data. I have a txtbox called txtNumber, where I enter the number of the Seller and run the query which will display the totals for that seller in my form. The problem i am having is connecting the data entered in the from to run the query and display the info. Can anyone point me in the right direction? Here is my info

Form info:

Text Box:
Enter Seller ID in "txtNumber"

Text Box:
Display total Approved in "txtApproved"

Cmd Button:
OK Button Code:

Private Sub Command116_Click()
On Error GoTo Err_Command116_Click
Dim stDocName As String
stDocName = "QuickStatsView"
DoCmd.OpenQuery stDocName, acNormal, acEdit
Exit_Command116_Click:
Exit Sub
Err_Command116_Click:
MsgBox Err.Description
Resume Exit_Command116_Click

End Sub



My SQL Stmt:

SELECT tbl_referral.[Time Closed], tbl_referral.[DATE REFERRED], tbl_referral.[Product 1 Referred], tbl_referral.STATUS, tbl_referral.[APPROVED/DENIED], tbl_referral.[Prod Specialist #], tbl_referral.Payout, tbl_referral.[PS Payout], tbl_referral.[New $ Amt], tbl_referral.[CD Bonus], tbl_referral.STARPOINTS,

Sum(IIf([Time Closed] Not Between GetDateLower() And Now(),IIf([STATUS]="Closed - Funded",1,0))) AS [Prev Month],

Sum(IIf([DATE REFERRED] Between GetDateLower() And Now(),IIf([STATUS]="Closed - Funded",1,0))) AS [Current Month],

Sum(IIf([DATE REFERRED] Between DateSerial(Year(Date()),Month(Date()),1) And Now(),IIf([APPROVED/DENIED]="Approved",1,0))) AS Approved

FROM tbl_referral

GROUP BY tbl_referral.[Time Closed], tbl_referral.[DATE REFERRED], tbl_referral.[Product 1 Referred], tbl_referral.STATUS, tbl_referral.[APPROVED/DENIED], tbl_referral.[Prod Specialist #], tbl_referral.Payout, tbl_referral.[PS Payout], tbl_referral.[New $ Amt], tbl_referral.[CD Bonus], tbl_referral.STARPOINTS

HAVING (((tbl_referral.[Prod Specialist #])=[Forms].[QuickStats].[txtID]));

 

[Kiwiman]

Howzit

How about putting the link to the form in the where part??

SELECT tbl_referral.[Time Closed], tbl_referral.[DATE REFERRED], tbl_referral.[Product 1 Referred], tbl_referral.STATUS, tbl_referral.[APPROVED/DENIED], tbl_referral.[Prod Specialist #], tbl_referral.Payout, tbl_referral.[PS Payout], tbl_referral.[New $ Amt], tbl_referral.[CD Bonus], tbl_referral.STARPOINTS, 
 
Sum(IIf([Time Closed] Not Between GetDateLower() And Now(),IIf([STATUS]="Closed - Funded",1,0))) AS [Prev Month], 
 
Sum(IIf([DATE REFERRED] Between GetDateLower() And Now(),IIf([STATUS]="Closed - Funded",1,0))) AS [Current Month], 
 
Sum(IIf([DATE REFERRED] Between DateSerial(Year(Date()),Month(Date()),1) And Now(),IIf([APPROVED/DENIED]="Approved",1,0))) AS Approved

FROM tbl_referral
[B]WHERE (((tbl_referral.[Prod Specialist #])=[Forms].[QuickStats].[txtID]))[/B]
GROUP BY tbl_referral.[Time Closed], tbl_referral.[DATE REFERRED], tbl_referral.[Product 1 Referred], tbl_referral.STATUS, tbl_referral.[APPROVED/DENIED], tbl_referral.[Prod Specialist #], tbl_referral.Payout, tbl_referral.[PS Payout], tbl_referral.[New $ Amt], tbl_referral.[CD Bonus], tbl_referral.STARPOINTS

My SQL Stmt:

SELECT tbl_referral.[Time Closed], tbl_referral.[DATE REFERRED], tbl_referral.[Product 1 Referred], tbl_referral.STATUS, tbl_referral.[APPROVED/DENIED], tbl_referral.[Prod Specialist #], tbl_referral.Payout, tbl_referral.[PS Payout], tbl_referral.[New $ Amt], tbl_referral.[CD Bonus], tbl_referral.STARPOINTS,

Sum(IIf([Time Closed] Not Between GetDateLower() And Now(),IIf([STATUS]="Closed - Funded",1,0))) AS [Prev Month],

Sum(IIf([DATE REFERRED] Between GetDateLower() And Now(),IIf([STATUS]="Closed - Funded",1,0))) AS [Current Month],

Sum(IIf([DATE REFERRED] Between DateSerial(Year(Date()),Month(Date()),1) And Now(),IIf([APPROVED/DENIED]="Approved",1,0))) AS Approved

FROM tbl_referral

GROUP BY tbl_referral.[Time Closed], tbl_referral.[DATE REFERRED], tbl_referral.[Product 1 Referred], tbl_referral.STATUS, tbl_referral.[APPROVED/DENIED], tbl_referral.[Prod Specialist #], tbl_referral.Payout, tbl_referral.[PS Payout], tbl_referral.[New $ Amt], tbl_referral.[CD Bonus], tbl_referral.STARPOINTS

HAVING (((tbl_referral.[Prod Specialist #])=[Forms].[QuickStats].[txtID]));

 

[LPurvis]

While I agree that it's a much better practice to assign non-aggregate criteria to the WHERE clause rather than the HAVING clause, it won't make any actual difference to your queries execution.

Indeed, I'm not even clear on what you actual problem is.
(You refer to a "txtNumber" in the question and yet have "txtID" in the statement).

Is the query actually working, but you're unsure how to grab a value for display in the local form using the "txtApproved" control?
A simple DLookup could be enough to do that for you.

 

[DCrake]

Why not create another public variable and function to grab the ID much in the same way you use the GetDateLower() function.

Public LngEmpID As Long

Function GetEmpID() As Long
    GetEmpID = LngEmpID
End Function

Then pass the emp id to the LngEmpID variable form the form.

David

 

[Coldsteel]

Thanks for the help all!!!

I adjusted my SQL Statment with the correct format and updated the txtID to txtNumber on the my SQL statement. The SQL statement is giving me the desired result when I run it by itself. When I use the form I am still having problems unfortunately. The SQL Statement prompts me with Enter Parameter Value [Forms].[QuickStats].[txtNumber] when I open the form. The main purpose of the form is to give me the quick total of all approved sells with in the current month in relation to the Seller ID. The idea is I enter the Seller ID, in the unbound text box called txtNumber, press ok to run the SQL Statement and the total approved for the current month displays in the an unbound text box called txtApproved. Am I approaching this problem correctly? Are there any examples out there which I may learn from?

Thanks,
Mike

 

[DCrake]

On the last line of your sql statement which reads

HAVING (((tbl_referral.[Prod Specialist #])=[Forms].[QuickStats].[txtID]));

Should read

HAVING (((tbl_referral.[Prod Specialist #])=" & [Forms].[QuickStats].[txtID] & "));"


Don't like the fact you are using #'s in your field names, this can be very confusing when dealing with dates as they use them as data type identifiers when writing sql. If possible would suggest you drop them.

David

 

[namliam]

Don't like the fact you are using #'s in your field names, this can be very confusing when dealing with dates as they use them as data type identifiers when writing sql. If possible would suggest you drop them.

Seconded, but also want to add.. .This goes for any special character !_)(*&^ etc... in any column names.

If you take it one step further, which I recomend you do, this would also include spaces... This will prevent future problems with missing [] around spaced names.

The first step of this you have already taken in using tbl prefix for Tables But you have not followed thru in using this convention in I.e. your queries (qry) where you simply use "QuickStatsView" instead of qryQuickStatsView.

Lastly, please dont leave default names for objects, like Command116, but give them a 'better' more self explaining name like cmdSearch or something. This will help in future support for your DB, either from you or anyone else!

Good luck and Happy coding !