Prompt Query

G

gilescis

D. Castaldo
Local time
Yesterday, 19:15
Joined
Jan 23, 2006
Messages
106
:confused: I have to build a query to do the following

my table consists of 7 fields
cg
price
gross
open
IM
state
sdate

I want to build a query that will prompt the user for 3 of those fields:
price , gross , IM

BUT:confused: when prompted I want the user to have the capability of changing the operators ie: > , < , = etc.

one time they may want
price > 100000.00 , Gross = 50000.00 , IM < 8

the next time they run it they may want:
price < 50000.00 Gross > 45000.00 IM = 4.5

Is this possible
 
Rather that all those prompts, pick the fields and operators on a popup form, then contruct your criteria, if any.
 
Do you happen to know of a sample DB of wht you are refering to.

Thanks for the help
 
I’m gonna use generic names for the objects to show you how this technique works, replace “table1” for the name of your table and change the field names etc. to reflect your current DB.

Create a table called “table1” with the following fields:
Name(text)
Price(currency)
Gross(currency)
IM(currency)

Create a query called “Result”. Set the SQL for the query to “select * from table1”

Create a form with the following objects added:

1 Listbox called “lstResult” make sure the column count is set to 4. You can increase this if your select query returns more than 4 coloumns, which it will going by what you said, but for this example just set it to 4.

3 Textboxes called “txtPrice”, “txtGross”, “txtIM”
3 Combo boxes called “cboPrice”,”cboGross”,”cboIM”

Set the row source type of each combo box to value list.

In the row source type: >;<;=

Add labels; see my pic below for what I mean.

Add a button that will search the records.

Right click on button and left click on build event, and select code builder. Type the following code(not the most eloquent but it works!):

Dim db As Database
Dim q As QueryDef
Dim sql As String
Dim sqlbuild As String

Set db = CurrentDb
Set q = db.QueryDefs("Result")

sqlbuild = ""
sql = "Select * from table1"

'Build relevant sql statement to add to the standard select statement
If Chkim = True Then

sqlbuild = " WHERE im " & cboim & " " & txtim

End If

If chkgross = True Then

If sqlbuild <> "" Then

sqlbuild = sqlbuild & " AND " & "gross " & cbogross & " " & txtgross

Else

sqlbuild = "WHERE gross " & cbogross & " " & txtgross

End If

End If

If chkprice = True Then

If sqlbuild <> "" Then

sqlbuild = sqlbuild & " AND " & "price " & cboprice & " " & txtprice

Else

sqlbuild = "WHERE price " & cboprice & " " & txtprice

End If

End If

If sqlbuild <> "" Then

sql = sql & sqlbuild

End If

q.sql = sql
q.Close
‘Refresh form
Me.Refresh

Your form should look some thing like this.

dbsearch.bmp


The query will only add conditions to the select statement if the check box above the condition is ticked. There is no error handling as I only quickly knocked this up.

You may need to add a reference to DAO 2.6 so you can use the QueryDef method. If you don’t know how to do that let me know and also let me know what version of Access you’re running.

Good luck
 
Last edited:
This is great help, One last monkey wrench,
Can I have the result of the search come out in a columnar format so it can be sent to excel so I can build pivot tables from that data ?
 
The query "Result" will hold the latest conditions, so you could add this line to the bottom of the code:

DoCmd.OpenQuery ("Result")

Actually, you might find this better:

DoCmd.OpenQuery "Result", acViewPivotTable, acReadOnly
 
Last edited:
Or I can have the output goto a rpt then from the rpt I can export to excel Correct ?
 
Sure. Create a report based on the Result query(for example named ResultReport) and then add this line at the bottom of the code:

Docmd.OpenReport "ResultReport", acViewPreview
 
Dave if your still around, have another question.....

This search looks great, but I have a possibility of having 13 different fields to use as a search criteria. maybe not all of them but...

For ex....

I may want to search by saledate, price , gross, open

then next time I may want to search by gps,gpm,perdown

prob no more then 4 fields will be used in the criteria.

Can I make this form instead of staticly assigning the firld names, have a drop down menu that allows the user to select the field name, then the operand , then enter value but giving them the option to add another field with a diff operand and diff value up to 4 or 5 choices

Does this make sense

Thanks
 
OK, I'll be looking into this later and tomorrow, will probably upload a zip file as it'll be easier then typing everything out.
 
this is the last piece I need to complete my project, I do appreciate the help
 
Hey

OK, attached to this message is a zipped up copy of a little search app I knocked up. I've added a little error handling this time, but feel free to modify it further.

You may need to add the DAO 3.6 reference(I use this alot for speed).

It is an Access 2002 file so it should work fine but let me know of any problems.

Again, this was a quicky so feel free to tidy up any code and add any other functionality you need.

I'd recommend looking at what I did, I used very straight forward programming on purpose, no doubt this can be done alot flashier, but it works pretty well.

Let me know when you have the file, as I will take it down afterwards, unless others want to use it.

Good luck.
 

Attachments

Ok I added the search to my DB, I changed the table name in the SELECT
I also change the tbl name in the QsLookup
When I select my criteria then search I get no results
Here is my code
Private Sub btnSearch_Click()

'Dim lsOne, lsTwo, lsThree, lsFour, lsFive As String
Dim lvOne, lvTwo, lvThree, lvFour, lvFive As Variant
Dim lsSQL As String
Dim db As Database
Dim q As QueryDef

Set db = CurrentDb
Set q = db.QueryDefs("Result")

On Error GoTo ERRORH

lsSQL = "SELECT * FROM tblCGSold WHERE "

'Check for first criteria
Select Case cboOne.Column(0)

'Text field
Case "T"

'Add ' ' to whatever the user entered so the database will accept it
lvOne = "'" & txtOne & "'"
lsSQL = lsSQL & cboOne.Column(1) & " " & cboOneb.Column(0) & " " & lvOne

'Non text field
Case "N"

'Nothing to add
lvOne = txtOne
lsSQL = lsSQL & cboOne.Column(1) & " " & cboOneb.Column(0) & " " & lvOne

End Select

'Check for second criteria
Select Case cboTwo.Column(0)

'Text field
Case "T"

'Add ' ' to whatever the user entered so the database will accept it
lvTwo = "'" & txtTwo & "'"
lsSQL = lsSQL & " AND " & cboTwo.Column(1) & " " & cboTwob.Column(0) & " " & lvTwo

'Non text field
Case "N"

'Nothing to add
lvTwo = txtTwo
lsSQL = lsSQL & " AND " & cboTwo.Column(1) & " " & cboTwob.Column(0) & " " & lvTwo

End Select

'Check for third criteria
Select Case cboThree.Column(0)

'Text field
Case "T"

'Add ' ' to whatever the user entered so the database will accept it
lvThree = "'" & txtThree & "'"
lsSQL = lsSQL & " AND " & cboThree.Column(1) & " " & cboThreeb.Column(0) & " " & lvThree

'Non text field
Case "N"

'Nothing to add
lvThree = txtThree
lsSQL = lsSQL & " AND " & cboThree.Column(1) & " " & cboThreeb.Column(0) & " " & lvThree

End Select

'Check for fourth criteria
Select Case cboFour.Column(0)

'Text field
Case "T"

'Add ' ' to whatever the user entered so the database will accept it
lvFour = "'" & txtFour & "'"
lsSQL = lsSQL & " AND " & cboFour.Column(1) & " " & cboFourb.Column(0) & " " & lvFour

'Non text field
Case "N"

'Nothing to add
lvFour = txtFour
lsSQL = lsSQL & " AND " & cboFour.Column(1) & " " & cboFourb.Column(0) & " " & lvFour

End Select

'Check for fifth criteria
Select Case cboFive.Column(0)

'Text field
Case "T"

'Add ' ' to whatever the user entered so the database will accept it
lvFive = "'" & txtFive & "'"
lsSQL = lsSQL & " AND " & cboFive.Column(1) & " " & cboFiveb.Column(0) & " " & lvFive

'Non text field
Case "N"

'Nothing to add
lvFive = txtFive
lsSQL = lsSQL & " AND " & cboFive.Column(1) & " " & cboFiveb.Column(0) & " " & lvFive

End Select

lsSQL = lsSQL & ";"
q.SQL = lsSQL
q.Close

If MsgBox("Would you like to use the pivot table option?", vbYesNo + vbQuestion, "Which Report?") = vbYes Then

DoCmd.OpenQuery "Result", acViewPivotTable, acReadOnly

Else

DoCmd.OpenReport "Result", acViewPreview

End If

EXIT_btnSearch:
Exit Sub

ERRORH:
MsgBox Error$

End Sub
 
Ok I think I have narrowed down my prob, Its with the Saledate, when I ask for a
saledate > xx/xx/xxxx and
saledate < xx/xx/xxxx

It returns nothing.
But if I leave the date out it works fine. BTW I have checked and DAO 3.6 is referenced
 
If the saledate is not a date/time type field change it's type in the tlLookup table to T.

See how that goes.
 
You must log in or register to reply here.

Similar threads

C
Sub or function not defined
Replies
12
Views
358
Pat Hartman
P
2
Prompting to save a copy of query
Replies
10
Views
880
2RUEXX
2
P
Long-running delete of duplicates
2
Replies
25
Views
967
CJ_London
C
Z
Some values have extra decimals in query
Replies
9
Views
1,426
GPGeorge
GPGeorge
R
Query is fast to run, but very slow to export results
Replies
14
Views
759
Rudolph583
R

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom