Dynamic report generation with comboBoxes

[JaneB19]

Hi,

I'm sorry if people think that this is in the wrong forum but as it's got SQL in it and that's the main problem I'm hoping that somebody can help me? PLEASE!!!

Ok, a quick explanation of what I'm attempting.

I've got an ASP.net web page with VB.net coding, but I want to generate a report depending on the values selected from my DropDownLists (comboBoxes) and RadioButtons. The values in these are dynamic so I can't specify values in them.

The user will not always select a value from each of these, so here's the question!

How can I create an SQL SELECT statement that, if a value is not specified ignores that field (i.e that field is not taken into consideration when returning the values) and just creates the report using the fields specified.

I'm really sorry if I've confused people with the above paragraph but it's the best way I can think of wording it. I'll try to re-word it if somebody asks.

I REALLY hope that somebody can help me out.

Thanks in advance

Jane %-)

 

[mhcruella]

Try declaring a variable in the code for each radio or drop down. This will enable you to decide if that piece of code is going to be included.

Below is a sample of what I did:

Select Case frmeTimePeriod
        Case 1
            strDates = ""
        Case 2
            strDates = "[MechanicTrack tbl].[DateStarted] like '" & StartDate & "*' "
        Case 3
            strDates = "[MechanicTrack tbl].[DateStarted] between #" & StartDate & "# and #" & EndDate + 1 & "#"

End Select

I then used the variable strCriteria in my select statement:

strSQL = "SELECT* "
strSQL = strSQL & "FROM [MechanicTrack tbl] "
strSQL = strSQL & "WHERE " & strDates &  ";"

I breakdown my SQL statements in code because I find them easier to read that way.

This will exclude a value that is not entered. Let me know if you need a better explanation (not my stong suit).

 

[JaneB19]

Hi mbcruella!

Thank you for your help! I'm just wondering but I take it that the strDates only refers to 1 string (comboBox) rather than several?

Another question is: how does the SQL know which field is meant to be compared to the strDates?

The answers to these are probably easy and it's just me being silly because I'm not fully awake yet!

Thanks again

Jane

 

[mhcruella]

strDates is the entire criteria for the [DateStarted] field. I have a form with a start date combo box (named StartDate) and an ending date combo box (named EndDate). The values that are in StartDate and EndDate will be the criteria for the [DataStarted] field in the [Mechanic tbl].
For the next three examples, assume our combo box StartDate = 6/1/2004 and EndDate = 6/30/2004. For example, if I was using Case 1 (no values to limit the SQL statement, the statement would look like:

SELECT * 
FROM [MechanicTrack tbl]
WHERE ;

For case 2:

SELECT * 
FROM [MechanicTrack tbl]
WHERE [MechanicTrack tbl].[DateStarted] like '6/1/2004';

For Case 3:

SELECT * 
FROM [MechanicTrack tbl]
WHERE [MechanicTrack tbl].[DateStarted] between #6/1/2004# and #6/30/2004#;

Case 3 uses the values from two combo boxes. If there are multiple fields that will be limited by the combo boxes, then I would build a case statement for each. A field can use multiple combo boxes or radio buttons to limit it, but there should be one case statement for each field you are limiting.

Let me know if I answered your questions. If you want to give more specifics, I may be able to help you out more.