sql to report

[red]

i was wondering if there was a way of putting an sql statement into a report through vb code. i know how to do it for a listbox but i would like to put it into a report so we can print out the results of the sql statement.

 

[Pat Hartman]

Save the query and then use it as the RecordSource rather than a table.

 

[red]

this is my code:

strSQL = "SELECT contacts.contact.id, contacts.postalcode, contacts.city, contacts.firstname, contacts.lastname, contacts.state, contacts.day_phone, contacts.evening_phone, contacts.budget_range, contacts.new, contacts.used, contacts.length, contacts.date_of_birth, contacts.salesperson, contacts.ctl1st, contacts.ctl2nd, contacts.ctl3rd, contacts.ctl4th, contacts.phone, contacts.walk_in, contacts.referral, contacts.show, contacts.advertising, contacts.source, contacts.insert, contacts.ad, contacts.newspaper, contacts.show_name, contacts.referral_name " & "FROM Contacts"

strWhere = "WHERE strWhereValue;"

strOrder = "ORDER BY contacts.contactid;"

'Pass the SQL to the RowSource of the listbox

Me.RecordSource= strSQL & " " & strWhere & " " & strOrder

With the last statement Me.recordsource= strSQL & " " & strWhere & " " & str Order i get a error saying that there are characters after the sql statement and i don't even have a space there. what do you think?

 

[DALeffler]

Listen to Pat: She knows what she's talking about...

You've got a couple of problems:

strWhere = "WHERE strWhereValue;"

should at least be:

strWhere = "WHERE " & strWhereValue

I don't see where you're setting a string value for strWhereValue at all and the ";" goes at the end of the sql string.

If you assign sql statements on the fly like this, you're going to have to compact the data base more often.

Read this thread.

I used to do exactly what you're doing and could never figure out why my db was always growing by almost a Mb every time I used the thing. When I redesigned the forms and reports to use queries instead of calculated sql strings for the record sources, my bloat problems were eliminated completely (at least as far as I can tell from the directory property listings for the db).

If you need to assign different record sources to a report, do it in the Report_Open event. Then your code simply becomes:

If somecondition Then
     Me.RecordSource = "MyQueryName1"
Else
     Me.RecordSource = "MyQueryName2"
End If

hth,

 

[red]

ok Doug i have a few things. the strWhereValue is a bunch of if statements to find a field that is not null and what ever the value is in that field put it in strWhereValue. now could you explain that code for the recordsource a little more because this is what has to happen, i have a form that has probably about 30 fields that could have something entered into it. i want them to be able to search what ever they need to search. now i want the results of the search to go into a report, so a new report every time they do a search. can you think of better code than what i have now?

Thanks a bunch!

 

[DALeffler]

I'm assuming your search form is unbound and that every search field (text box) you have on the form is also unbound.

Try this:

Design a query that brings in all the fields of your table (double click on the "*" field in the query design grid for the displayed table you want to run the query on).

In the next empty column of the query design grid, enter the following:

Field: IIf([MyTable].[MyTableFieldName1]=[forms]![MyFormName]![MyTxtBxName1],True,IIf(IsNull([forms]![MyFormName]![MyTxtBxName1]),True,False))

Show: unchecked.
Criteria: True
or: blank

In the next column, you enter the same statement but change the table and form field assignments , like this:

Field: IIf([MyTable].[MyTableFieldName2]=[forms]![MyFormName]![MyTxtBxName2],True,IIf(IsNull([forms]![MyFormName]![MyTxtBxName2]),True,False))

Show: unchecked.
Criteria: True
or: blank

and then continue to add query grid columns (Where clause elements when "Show" is unchecked) for each field on the form you want the query to search the table against.

That'll set up a query that returns every record in the table (or query) if none of the forms' text boxes have data in them. It's relatively simple to check the form for empty controls when the user clicks "Search".

Then set your report record source to the above query.

Give that a try. Start with a small table.

I'll set up a small demo db if you're interested (or if anyone else is interested), but gimme some time - tommorrows a work day where I don't have internet access...