Search Query based on form

[Prashant]

Hi,

I am unable to get multiple search output using the below code in queries, the search text is based on form :

Search form name : Find
Query name : Search
Table name : Sheet1

[forms]![find]![city]

The above works fine if it is only used in one of the fields, but as soon as I have it in multiple fields the output is null.

I am aware that i can used Like "*" code, but the thing I want to find records which are perfectly matching based on the form.

Can anybody let me know, what is wrong. I am pretty sure the code I have used above is correct.

Please help.

 

[namliam]

What you are trying to do is very complex to do with direct form searches, instead you want to custom build your query using a technique called "Filter by form" which is simply building your where clause on demand and running the query with it.

have a look at this thread and the linked sample, see if you can get the idea of how to do it.
http://www.access-programmers.co.uk/forums/showthread.php?p=1300908#post1300908

 

[Prashant]

Hi,


I have modified the code as per my requirement, but once i click the command button after selecting one of the combo boxes, it is poping up a window asking to "Enter Parameter value" if I skip this blank records are thrown, whereas if I enter the parameter value the same text what is there in the combox, returns me the desired result. Can you please help me to get rid of the same.

One more thing, instead of report, i want to use query and the user should be able to edit the same, here is the code that i have used, but something seems wrong and giving an error as "Type Mismatch" :

DoCmd.OpenQuery stDocName, acedit, stWhere

Also below is the entire code :

Private Sub Command12_Click()
On Error GoTo Err_cmdGenerateReport_Click
Dim stDocName As String
Dim stWhere As String
Dim stDates As String
Dim blnTrim As Boolean

If Not IsNull(Me.ID) Then
stWhere = "[ATM_ID]=" & Me.ID & " And "
blnTrim = True
End If
If Not IsNull(Me.Citycombo) Then
stWhere = stWhere & "[City]=" & Me.Citycombo & " And "
blnTrim = True
End If
If Not IsNull(Me.Depotscombo) Then
stWhere = "[Depots]=" & Me.Depotscombo & " And "
blnTrim = True
End If
If Not IsNull(Me.vendorcombo) Then
stWhere = stWhere & "[Vendor]=" & Me.vendorcombo & " And "
blnTrim = True
End If

If IsNull(Me.Start) And Me.Start = "" Then
If Not IsNull(Me.End) And Me.End <> "" Then
stWhere = stWhere & "[Date] <=" & Me.End & "#"
blnTrim = False
End If
Else
If IsNull(Me.End) And Me.End = "" Then
If Not IsNull(Me.Start) And Me.Start <> "" Then
stWhere = stWhere & "[Date]>=" & Me.Start
blnTrim = False
End If
Else
If (Not IsNull(Me.Start) And Me.Start <> "") And (Not IsNull(Me.End) Or Me.End <> "") Then
stWhere = stWhere & "[Date] Between #" & Me.Start & "# And #" & Me.End & "#"
blnTrim = False
End If
End If
End If
If blnTrim Then
stWhere = Left(stWhere, Len(stWhere) - 5)
End If
stDocName = "Search"
'DoCmd.OpenReport stDocName, acPreview, , stWhere
DoCmd.OpenQuery stDocName, acEdit, stWhere
Exit_cmdGenerateReport_Click:
Exit Sub
Err_cmdGenerateReport_Click:
MsgBox Err.Description
Resume Exit_cmdGenerateReport_Click
End Sub


Awaiting your reply.


Thanks

 

[Prashant]

Hi,

I forgot to mention one more thing, I have one combo box named Vendorcombo, where the vendor combo list has a space between name for e.g. "ISS SDB", for these I am getting an error "Syntax error (missing operator) in query expression '[Vendor]=ISS SDB'."

These errors are repeated whereever there is space between the names.

I would be thankful if you can help me resolve the three issue mentioned :

1) Asking for Parameter Value
2) Instead of Report I should be able to open query (in Edit mode)
3) Combo names with spaces should be able to populate report

Thanks in advance

 

[Mihail]

stWhere = stWhere & "[Vendor]= '" & Me.vendorcombo & "' And "
Hope you see the red (semi quotes - I don't know the word in English) that should be added in SQL string whenever a field can store data with spaces.

 

[Prashant]

Hi Mihail,

Thanks for your solution.

I was wondering if you can help me on openning a query in edit mode, presently the code mentioned below is for report, i want it to make for query :

DoCmd.OpenReport stDocName, acPreview, , stWhere

I am getting an error for the query code mentioned below :

DoCmd.OpenQuery stDocName, acEdit, stWhere

Can you please help.

Thanks

 

[Mihail]

I never show a query or a table to the user.
So you shouldn't.

Any way, the acEdit mod say to me that you wish to open the query in order to edit it. That means, in Design View. And in Design View stWhere has no reason.

So you should open the query in other mode like acView (not very sure about syntax but Access will help you with a drop down list)
DoCmd.OpenQuery stDocName, acView, stWhere

 

[namliam]

Like I said in the linked post,

number field:     stWhere = "[EnteredBy] =" & Me.cboCdc 
Text field (m) :  stWhere = "[EnteredBy] =" & """" & Me.cboCdc & """"
Text field (alt): stWhere = "[EnteredBy] =" & "'" & Me.cboCdc & "'"
Date field:       stWhere = "[EnteredBy] =" & "#" & Me.cboCdc & "#"

 

[Prashant]

I want to open a query output in edit mode, presently the code below is used for report, I want it for query i.e. to open table in edit mode

stDocName = "Search"
DoCmd.OpenReport stDocName, acViewReport, , stWhere

I am unable to open the query using the below code, it gives me an error
DoCmd.OpenQuery stDocName, acEdit, stWhere

This is basically a search form - stwhere is the value the user inserts in the search form

I am aware I shouldn't show a query or a table, but the time demands so.

Thanks

 

[namliam]

what is the stwhere?
what is the error?
what is the query?

 

[Prashant]

Please find attached database, the code is published on the command button of the "Find" form, however here goes the entire code

Private Sub cmdGenerateReport_Click()
On Error GoTo Err_cmdGenerateReport_Click
Dim stDocName As String
Dim stWhere As String
Dim stDates As String
Dim blnTrim As Boolean

If Not IsNull(Me.ID) Then
stWhere = "[ATMID]= '" & Me.ID & "' And "
blnTrim = True
End If
If Not IsNull(Me.Citycombo) Then
stWhere = stWhere & "[City]= '" & Me.Citycombo & "' And "
blnTrim = True
End If
If Not IsNull(Me.Depotscombo) Then
stWhere = "[Depots]= '" & Me.Depotscombo & "' And "
blnTrim = True
End If
If Not IsNull(Me.vendorcombo) Then
stWhere = stWhere & "[Vendor]= '" & Me.vendorcombo & "' And "
blnTrim = True
End If

If IsNull(Me.Start) And Me.Start = "" Then
If Not IsNull(Me.End) And Me.End <> "" Then
stWhere = stWhere & "[Date] <=" & Me.End & "#"
blnTrim = False
End If
Else
If IsNull(Me.End) And Me.End = "" Then
If Not IsNull(Me.Start) And Me.Start <> "" Then
stWhere = stWhere & "[Date]>=" & Me.Start
blnTrim = False
End If
Else
If (Not IsNull(Me.Start) And Me.Start <> "") And (Not IsNull(Me.End) Or Me.End <> "") Then
stWhere = stWhere & "[Date] Between #" & Me.Start & "# And #" & Me.End & "#"
blnTrim = False
End If
End If
End If
If blnTrim Then
stWhere = Left(stWhere, Len(stWhere) - 5)
End If
stDocName = "Search"
DoCmd.OpenReport stDocName, acViewPreview, , stWhere
'DoCmd.OpenQuery stDocName, acEdit, stWhere


Exit_cmdGenerateReport_Click:
Exit Sub
Err_cmdGenerateReport_Click:
MsgBox Err.Description
Resume Exit_cmdGenerateReport_Click
End Sub


Thanks

 

[Mihail]

Somewhere is some misunderstanding in this threat.
Forget for a while about codes, acEdits, acViews etc and explain us what you wish to do.
Something like:
I wish to show, in a report, only those records that much a value from my current record
and
I wish to open a query where the user to do... something
and
......

 

[Prashant]

Sorry for confusing you. I want a simple search form where the user opts for a particular search string (the above code, helped me to filter out the data), now what I need is to show the filtered multiple records in the datasheet view, so the user can make the changes. Presently the code throws the output in preview mode of the report. I want this to be changed to datasheet view (editable).

I hope this time I am clear.

 

[namliam]

Editting is done in Forms not in Reports try building a quick form based on the query and opening the form with the where clause

As far as I know the DoCmd.Openquery wouldnt allow for a stWhere 'clause' to be send to it, which is probably the error you are getting. Instead you would have to modify the SQL inside the query directly by doing something like:
Currentdb.Querydefs("Search").SQL = "insert sql here and where clause to be addapted"
Then open the query.

 

[Prashant]

I will make things pretty simple, I have a form name "Find", in which there are 6 options for the user to select. Whatever the user selects out of these 6 options, I need the output (the filtered table) in the datasheet view (Whatever the user has selected in the "Find" form.

I am now totally confused and don't know how to get the result. I don't want to use Like "*" codes, I want the query to do the exact search.

Thanks.

 

[Mihail]

SQL for your query:

SELECT Sheet1.Date, Sheet1.ATMID, Sheet1.City, Sheet1.State, Sheet1.Depots, Sheet1.VENDOR, Sheet1.[Recom 100], Sheet1.[Recom 500], Sheet1.[Recom 1000], Sheet1.Total, Sheet1.[Forecasted Dispensed Day 0], Sheet1.[Forecasted Dispensed Day 1], Sheet1.[New Recom 100], Sheet1.[New Recom 500], Sheet1.[New Recom 1000], Sheet1.[New Total], Sheet1.Comments, Sheet1.[Additional Comments]
FROM Sheet1
WHERE (((Sheet1.Date)>=[Forms]![Find]![Start] And (Sheet1.Date)<=[Forms]![Find]![End]) AND ((Sheet1.ATMID)=[Forms]![Find]![ID]) AND ((Sheet1.City)=[Forms]![Find]![Citycombo]) AND ((Sheet1.Depots)=[Forms]![Find]![Depotscombo]) AND ((Sheet1.VENDOR)=[Forms]![Find]![vendorcombo]));

Will work only if ALL "fields" in the form "Find" are filled.

 

[Prashant]

The SQL statement works out fine, thanks for the same. But is there any way around where there is no need to fill all the options in the form, and still we get the data for those which has been filled.

I know i am asking too much

Thanks

 

[Mihail]

The SQL statement works out fine, thanks for the same. But is there any way around where there is no need to fill all the options in the form, and still we get the data for those which has been filled.

I know i am asking too much

Thanks

Yes, it is. But I know only one way: To use an external function.
Are you ready for VBA ?

 

[Prashant]

I am not too good or very familiar with VBA (know some Basics), but with your guidance I can do that too. I need to get the desired output, that its, whatever the user opts for needs to be the output in datasheet view (even if the user doesn't select all the 6 options).

Thanks.

 

[Mihail]

Test this (see attachment).

Unfortunately I have very bad news for you.
Because your DB is far away from a normalized one you will end, soon or later but for sure, to fail.

I'll never understand why, the smart guys know that should learn a lot of math before starting to solve complicated problems but they think that a bit of knowledge about how to enter a formula in Excel, how to navigate on the WWW and how to use Skype is enough to start to design complicated DBs.

Google a bit. Key: How to design a database.
After few hours I am sure that you will understand why what you name now "database" is NOT a database. And never never ever will work as a database.

Sorry for this, but you look like a guy that can learn.

Good luck !