Can someone dumb up sql select for me.. (1 Viewer)

[helper11]

Hi,

I am trying to do a sql select from a query using 2 values from a "view sellers sales" form combo box. I am getting month and user from the combo boxes and I want to select the record from my "report sales query" using those 2 values.

I have been trying this for a while but I am so confused on it now and lost. I found other ways around it using filters on sub forms, but I really want to try it this way and understand it better as I don't under stand what is going on in those statements.

Thank you...

 

[rainman89]

Here is a generic example with a select statement using combo boxes

SELECT transaction.SupplierID, transaction.UnitsBought, Format([TransDate],"dd") AS tdate
FROM [transaction]
WHERE (((transaction.SupplierID) = [forms]![form1]![combo0]));

Is that "dumb" enough or do you need more?

 

[helper11]

I need it dumber, because I have no idea what supplierid is or any of the fields you are using there, or what transaction is, that is what is confusing me the most. That is why I included the names of the items to get a better idea on this. I also want to filter it on both fields is this possible?

Here is a generic example with a select statement using combo boxes

SELECT transaction.SupplierID, transaction.UnitsBought, Format([TransDate],"dd") AS tdate
FROM [transaction]
WHERE (((transaction.SupplierID) = [forms]![form1]![combo0]));

Is that "dumb" enough or do you need more?

 

[rainman89]

OK! That was just an example of the structure using a table I had in one of my databases...

So lets break it down..

Transaction is the name of the table that I am trying to get the records from.
SupplierID is the field that I am matching against the combo box to return the result that I am looking for.

So for you, I guess what we will need is the actual fields that you are trying to return and the actual names of the combo boxes on your form!

Also, is the "report sales query" the query you want to get the values from, or do you want to have the do the work for you?

 

[HiTechCoach]

I need it dumber, because I have no idea what supplierid is or any of the fields you are using there, or what transaction is, that is what is confusing me the most. That is why I included the names of the items to get a better idea on this. I also want to filter it on both fields is this possible?

If examples are confusing then I would urge you to post the SQL you are attempting to use. Even better would be to attach a sample of our database.

 

[helper11]

Hello,

So in my case it would be something like the following:

Select report sales query.expr3, report sales query.expr1
From [report sales query]
Where (((report sales query.expr3)=Me.Combo3) AND ((report sales query.expr1)=Me.Combo1)))

Does this look like it would work to be called from a button in the form?

 

[rainman89]

possible......

You said one is a month? Is it stored that way in your table?

Can you post up a sample of your database like coach asked?

 

[helper11]

Hello,

Sorry, I can't send up the code as I have not written anything yet, as I am trying to understand what it is doing and then to get it straight enough in my mind to apply it to my code.

The month is stored as a number for the month, and the table lets them select january, febuary so on...

Thanks for the guidance on this so far..

 

[rainman89]

If its stored as a number then you should be able to use your code that you posted up..

I realize you have no code written as of yet, but can you post your actual database up here so we can see what you are working with?

 

[helper11]

Hello,

Here is what I have written quickly to try and fill in a subform using the found value from the report sales sellers query.

Dim LSQL As String

LSQL = "select * from [Report Sales Sellers] where expr1 ='" & Me.Combo7 & "' AND MonthName(Expr3) ='" & Me.Combo3 & "'"""
Me.Total_Sales.Form.RecordSource = LSQL

Am I doing this right or am I missing something?

Thanks again for all the help..

 

[boblarson]

You actually have fields in your table named Expr1 and Expr3?

 

[helper11]

Hello,

The fields names are in the query that I am trying to use to limit it by.

Before I was using the following:

If Not IsNull(Me.Combo7) And Not IsNull(Me.Combo3) Then
'assumes bound column in combo is the month number
strSales = "expr1 ='" & Me.Combo7 & "' AND MonthName(Expr3) ='" & Me.Combo3 & "'"
Me.Total_Sales.Form.Filter = strSales
Me.Total_Sales.Form.FilterOn = True

I want to try and figure out the sql way of writing this and then I can hopefully figure out from there how to export it.

Thank you.

 

[rainman89]

Am I doing this right or am I missing something?

Well I don't know, does it work??


You said month is stored as a number in your table, so why bother converting it to text to match it to your combo value? Why not just use the ID column of your combo box and match it that way? Not a big deal, just how I would do it.

LSQL = "select * from [Report Sales Sellers] where expr1 ='" &  Me.Combo7 & "' AND [COLOR=Red]MonthName(Expr3) ='" & Me.Combo3 & "'"""[/COLOR]
    Me.Total_Sales.Form.RecordSource = LSQL

 

[boblarson]

1. You should use meaningful names, not Expr1, etc.

2. You should name your controls something meaningful, instead of Combo7, Combo3, etc.

3. Put in a Debug.Print for each combo box value to make sure it is sending to the code what you think it should be.

So, like this:

If Not IsNull(Me.Combo7) And Not IsNull(Me.Combo3) Then
   'assumes bound column in combo is the month number
[COLOR="Blue"][B]   Debug.Print "Combo7 - " & Me.Combo7 
   Debug.Print "Combo3 - " & Me.Combo3[/B][/COLOR]
   strSales = "expr1 ='" & Me.Combo7 & "' AND MonthName(Expr3) ='" & Me.Combo3 & "'"
       Me.Total_Sales.Form.Filter = strSales
       Me.Total_Sales.Form.FilterOn = True

 

[helper11]

Hello,

No, it does not work. It does not seem to be going to the Report Sales Sellers at all so I do not know what I am doing wrong. This seems simple for those who know what they are doing.

I am just trying to get a subform filled from a query names Report Sales Sellers where the month is combo 3 and user is combo 7 just to get an idea of how this works. The subform is called Total_Sales am I going in the right direction with this?

Thank again for all the help..

 

[helper11]

Hello,

boblarson, thank you for the idea. I have that part of the code working perfectly. I am just trying to learn more on vba and fill in the subform using the select and where values. I know I should use better variable names, just because i had to create a chart to keep track of them means nothing...

Thanks again...

 

[rainman89]

Post up your Database, we will take a look at it.

 

[boblarson]

The subform is called Total_Sales

Are you sure that the Subform CONTROL (control on the main form which houses the subform) is named this. That is the name to be using (it can be the same as the subform but many times it isn't and you need to use the subform control name and not the subform name if they are different).

 

[helper11]

Hello,

I have been able to create something very similar, are there other ways to write it? What I want to be able to do now is to export either a report using the current record or export it to excel using vba. Here is the code I was able to get to work:

Private Sub Combo52_AfterUpdate()
Dim rsRecordset As Recordset
Dim sCriteria As String
Dim month_value As Integer


Set rsRecordset = Me.RecordsetClone

month_value = Me.Combo54.Column(1)

sCriteria = "Expr1 ='" & Me.Combo52 & "' AND Expr3 = " & month_value & ""

rsRecordset.FindFirst sCriteria

If Not rsRecordset.NoMatch = True Then
Me.Bookmark = rsRecordset.Bookmark
End If
rsRecordset.Close


End Sub

Thanks for all the help and any suggestions on how to do the next step will be greatly appreciated.