It's been 10+ years and I need a little help with SQL

[BobbaFet]

Hi all, I want to make a report based on a variable SQL query but I can't for the life of me remember how to do that

Here's the thing, the database is a simple address table with a bunch of addresses in it which have route numbers. Now what I want on my form is a button that when clicked pops up a box where I can enter a route number and then all records with the corresponding route number are selected and sent to my report. Shouldn't be too hard but I just don't remember how to do this.

Quickly in steps:
- User is done doing stuff on the form
- User hits print route button
- A box pops up asking which route number
- User enters route number and hits ok button
- Query does it's stuff and presto route is presented in the report ready to print.

How do I make this happen? Last time I've used MS Access I was still in school and haven't worked databases since then.

BobbaFet

if not Programming = 'Severe Migraine' then
                       ShowMessage('Eureka!');

 

[BobbaFet]

PS: I am well aware that I can base reports on queries so that's not the part I am asking about. It's how do I make a query where a user is asked for input to preform the query with.

BobbaFet

if not Programming = 'Severe Migraine' then
                       ShowMessage('Eureka!');

 

[dhookom]

I would add an unbound combo box of routes on the form where you click the button. Then add code to open the report like:

Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.cboRoute) Then
    [green]'assuming RouteNumber is text not numeric[/green]
    strWhere = strWhere & " AND [RouteNumber]='" & _
        Me.cboRoute & "' "
End If
DoCmd.OpenReport "Report Name Here", acViewPreview, , strWhere

Duane
Hook'D on Access
MS Access MVP

 

[BobbaFet]

Routenumber is numeric and I am not quite sure how to use your code... It doesn't look like SQL to me... (Sorry if I am pissing you off but I haven't used MS Access since I left school)

BobbaFet

if not Programming = 'Severe Migraine' then
                       ShowMessage('Eureka!');

 

[dhookom]

Did you add a combo box named "cboRoute" to your form and set the bound column to the numeric route field?

Did you add a command button on the form?

Did you add code in the Event Procedure for the button like:

Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.cboRoute) Then
[green]    'assuming RouteNumber Field is  numeric[/green]
    strWhere = strWhere & " AND [RouteNumber]=" & Me.cboRoute
End If
DoCmd.OpenReport "Report Name Here", acViewPreview, , strWhere

The strWhere create a filter so the report is opened with only the route selected in the route combo box.



Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

Or you may do something like:

If Not IsNull(Me.cboRoute) Then[green]
    'assuming RouteNumber Field is  numeric[/green]
    DoCmd.OpenReport "Report Name Here", acViewPreview, , " [RouteNumber] = " & Me.cboRoute
[blue]
Else
    MsgBox "Please, select Route"[/blue]
End If

Have fun.

---- Andy

 

[lameid]

You haven't given names so if your route is entered in cboRoute like the above examples and your form is named MyForm and your table is named MyTable and Route is a long integer... The parameters line/clause is optional. There are reasons to use it or not based on datatypes. Visit Allen Browne's site to learn more (I do not have the link handy and am not certatin of which to use.... I want to say not to use it on text).

PARAMETERS Forms!MyForm!cboRoute Long
Select MyTable.*
From MyTable
Where [Route] = Forms!MyForm!cboRoute

 

[FledglingAnalyst]

If you need something you can put into a query, you would use brackets in the where clause to define a variable, which would prompt the query to ask you to input the value. Something like:

Select Field1, field2, field3, field4
From tbl1
Where (((tbl1.field1)=[Route Number]));

You are going to get a pop-up from Access to Input "Route Number".