Optional search criteria (1 Viewer)

P

pwalter83

Registered User.
Local time
Yesterday, 20:44
Joined
Dec 19, 2011
Messages
31
Hi,

On the access form I have designed , I need to give optional search option to the users. I have the following search options on the form (screenshot attached):

From Date
To Date
Port
Vessel
Voyage
---------------------
The query is as follows:
SELECT dbo_VESSEL.VESSEL_NAME, dbo_VESSEL.VESSEL_CD, dbo_VESSEL.VOYAGE_NUM, dbo_VESSEL.PORT_CD, dbo_VESSEL.DEPART_ACTUAL_DT, dbo_VESSEL.DIVISION_CD
FROM dbo_VESSEL
WHERE (
(dbo_VESSEL.VESSEL_CD Like [Forms]![VESSDAT].[Form]![txtvessel]) OR
(dbo_VESSEL.VOYAGE_NUM Like [Forms]![VESSDAT].[Form]![txtvoyage]) OR
(dbo_VESSEL.PORT_CD Like [Forms]![VESSDAT].[Form]![txtport]) OR
(dbo_VESSEL.DEPART_ACTUAL_DT BETWEEN [Forms]![VESSDAT].[Form]![txtfromdept] And [Forms]![VESSDAT].[Form]![txttodept]));
----------------------

However, the form still prompts me to enter values for all the boxes and still doesnt show the correct data.

Can somebody please tell what I need to change for this to work ?

Thanks.
 

Attachments

  • screen.png
    screen.png
    16 KB · Views: 92
C

CJ_London

Super Moderator
Staff member
Local time
Today, 04:44
Joined
Feb 19, 2013
Messages
16,668
Assuming your controls are on the main form and not a subform, you've got an additional [Form] - easy to resolve if you use the expression builder in the query design window

[Forms]![VESSDAT].[Form]![txtvessel]

should be

[Forms]![VESSDAT]![txtvessel]

I assume that txtfromdept is the from date?

Also suggest you change your criteria as below (for all parts of the criteria) so if the user does not complete a field in the form the record will still be returned (unless you don't want this)

Code: 
(dbo_VESSEL.VESSEL_CD Like nz([Forms]![VESSDAT].[Form]![txtvessel],dbo_VESSEL.VESSEL_CD))
or

Code: 
(dbo_VESSEL.VESSEL_CD Like [Forms]![VESSDAT].[Form]![txtvessel] OR [Forms]![VESSDAT].[Form]![txtvessel] is null)
 
Last edited:
P

pwalter83

Registered User.
Local time
Yesterday, 20:44
Joined
Dec 19, 2011
Messages
31
CJ_London said:
Assuming your controls are on the main form and not a subform, you've got an additional [Form] - easy to resolve if you use the expression builder in the query design window

[Forms]![VESSDAT].[Form]![txtvessel]

should be

[Forms]![VESSDAT]![txtvessel]

I assume that txtfromdept is the from date?

Also suggest you change your criteria as below (for all parts of the criteria) so if the user does not complete a field in the form the record will still be returned (unless you don't want this)

Code: 
(dbo_VESSEL.VESSEL_CD Like nz([Forms]![VESSDAT].[Form]![txtvessel],dbo_VESSEL.VESSEL_CD))
or

Code: 
(dbo_VESSEL.VESSEL_CD Like [Forms]![VESSDAT].[Form]![txtvessel] OR [Forms]![VESSDAT].[Form]![txtvessel] is null)
Click to expand...

Thanks but its not working. This is a sub-form and I have tried to modify the query as below but its doesnt work and still prompts me to complete all the textbox values (You must enter search criteria). The txtfromdept is the 'from date' and txttodate is the 'to date'. Also, if the user is using the date criteria to retrive data, than both From date and To Date values should be mandatory.

--------------------

SELECT dbo_VESSEL.VESSEL_NAME, dbo_VESSEL.VESSEL_CD, dbo_VESSEL.VOYAGE_NUM, dbo_VESSEL.PORT_CD, dbo_VESSEL.DEPART_ACTUAL_DT, dbo_VESSEL.DIVISION_CD
FROM dbo_VESSEL
WHERE (
(dbo_VESSEL.VESSEL_CD Like nz( [Forms]![VESSDAT].[Form]![txtvessel])) OR
(dbo_VESSEL.VOYAGE_NUM Like nz([Forms]![VESSDAT].[Form]![txtvoyage])) OR
(dbo_VESSEL.PORT_CD Like nz([Forms]![VESSDAT].[Form]![txtport])) OR nz(
(dbo_VESSEL.DEPART_ACTUAL_DT BETWEEN [Forms]![VESSDAT].[Form]![txtfromdept] And [Forms]![VESSDAT].[Form]![txttodept])));
--------------------------------

Do you have any more ideas ?
 
C

CJ_London

Super Moderator
Staff member
Local time
Today, 04:44
Joined
Feb 19, 2013
Messages
16,668
The code you are quoting as using
(dbo_VESSEL.VESSEL_CD Like nz( [Forms]![VESSDAT].[Form]![txtvessel]))
Click to expand...
is not the code I suggested you use

Forms]![VESSDAT]![txtvessel]
Click to expand...
This is a sub-form
Click to expand...
I'm getting confused. If you mean that txtVessel etc are on a subform then you need

Code: 
[Forms]![VESSDAT]![[COLOR=red]MySubformname[/COLOR]]![Form]![txtvessel]


You also have other inconsistencies
... txttodate is the 'to date'
... And [Forms]![VESSDAT].[Form]![txttodept])));
Click to expand...
 
P

pwalter83

Registered User.
Local time
Yesterday, 20:44
Joined
Dec 19, 2011
Messages
31
CJ_London said:
The code you are quoting as using

is not the code I suggested you use


I'm getting confused. If you mean that txtVessel etc are on a subform then you need

Code: 
[Forms]![VESSDAT]![[COLOR=red]MySubformname[/COLOR]]![Form]![txtvessel]


You also have other inconsistencies
Click to expand...

Sorry for the incorrect references. On the main form there are 3 tabs and I have to amend one of the tabs. is that supposed to be a subform ?

also txttodept is the 'todate' and txtfromdept is the 'from date'.

Please find the screenshot attached.

Thanks.
 

Attachments

  • form.png
    form.png
    5.8 KB · Views: 96
Last edited:
N

namliam

The Mailman - AWF VIP
Local time
Today, 05:44
Joined
Aug 11, 2003
Messages
11,695
pwalter83 said:
also txttodept is the 'todate' and txtfromdept is the 'from date'.
Click to expand...
And you would say that this is naming your controls logically ??? :eek:

If you have your query in the designer you can do "Right click > Build"
And from there browse to your form(s) and get the control you need.... Make sure to walk thru the main form to the subform to prevent any problems.
 
P

pwalter83

Registered User.
Local time
Yesterday, 20:44
Joined
Dec 19, 2011
Messages
31
namliam said:
And you would say that this is naming your controls logically ??? :eek:

If you have your query in the designer you can do "Right click > Build"
And from there browse to your form(s) and get the control you need.... Make sure to walk thru the main form to the subform to prevent any problems.
Click to expand...


What is this ??? Did you read my query at all ??? Do not give unnecessary / absurd answers as you block the post from getting genuine answers from others.
 
N

namliam

The Mailman - AWF VIP
Local time
Today, 05:44
Joined
Aug 11, 2003
Messages
11,695
What is what, yes I read your query and gave you a proper answer, thank you very much.
The fact you cant see the forest for the tree's isnt my problem.

You seem to be having trouble finding the proper syntax to get data from your form, I gave you an easy way to get the proper syntax....
 
nanscombe

nanscombe

Registered User.
Local time
Today, 04:44
Joined
Nov 12, 2011
Messages
1,082
How to use the Expression Builder, as suggested by namliam, (step by step)

(Yes, I know I'm complicating it again but if the OP doesn't know what it is, let alone how to use it ...)

The process is more intuitive and less complicated than it looks when written down.

1) Open the form in design mode.
2) Open your query in design mode.
3) Place the cursor in the criteria box of the field you are interested in.
4) Click the Right mouse button and select the Build option.
5) In the Expression Elements pane click the + by the name of your database.
6) Click the + by Forms
7) Click the + Loaded Forms
8) Click the name of your form (VESDAT)
9) Select the name of your subform
10) Double the name of your control from the Expression Categories pane.

This will give you the correct syntax ie,
Forms![VESSDAT]![mySubform].Form![myControl]

You can then edit it to add the like
Like nz(Forms![VESSDAT]![mySubform].Form![myControl])

11) Click the OK button and the text will now appear in the selected criteria field.
 
Last edited:
jdraw

jdraw

Super Moderator
Staff member
Local time
Yesterday, 23:44
Joined
Jan 23, 2006
Messages
15,394
pwalter83,

People are trying genuinely to help you. Posters are often intimately familiar with their situations, but for one reason or another are not communicating clearly with the readers.

In addition to the comments of others, I see a "misuse" of the Like operator. It would appear that you do not have an asterisk "*", before or after your Like condition.

Like "*" & your text -- or
Like Your text & "*" -- or
Like "*" & your text & "*"

see http://www.techonthenet.com/access/queries/like.php
 
P

pwalter83

Registered User.
Local time
Yesterday, 20:44
Joined
Dec 19, 2011
Messages
31
nanscombe said:
How to use the Expression Builder, as suggested by namliam, (step by step)

(Yes, I know I'm complicating it again but if the OP doesn't know what it is, let alone how to use it ...)

The process is more intuitive and less complicated than it looks when written down.

1) Open the form in design mode.
2) Open your query in design mode.
3) Place the cursor in the criteria box of the field you are interested in.
4) Click the Right mouse button and select the Build option.
5) In the Expression Elements pane click the + by the name of your database.
6) Click the + by Forms
7) Click the + Loaded Forms
8) Click the name of your form (VESDAT)
9) Select the name of your subform
10) Double the name of your control from the Expression Categories pane.

This will give you the correct syntax ie,
Forms![VESSDAT]![mySubform].Form![myControl]

You can then edit it to add the like
Like nz(Forms![VESSDAT]![mySubform].Form![myControl])

11) Click the OK button and the text will now appear in the selected criteria field.
Click to expand...

thanks for your reply, Nigel. Could you help me with another query please ?

Ther MS access form I have designed is a search form with optional search. This means that the user can leave the textboxes blank but the from date and to date option should be used together as that would be a date range.

The issue I am facing is that I still get the prompt 'You must enter search criteria' if I leave out the textbox blank. Could you please tell what I need to change for this to work correctly ? Please find the screenshot of the form attached.

Please find my query below:

---------------------------------------------------

SELECT dbo_VESSEL.VESSEL_NAME, dbo_VESSEL.VESSEL_CD, dbo_VESSEL.VOYAGE_NUM, dbo_VESSEL.PORT_CD, dbo_VESSEL.DEPART_ACTUAL_DT, dbo_VESSEL.DIVISION_CD
FROM dbo_VESSEL
WHERE (((dbo_VESSEL.VESSEL_CD) Like [Forms]![VESSDAT].[Form]![txtvessel]) or [Forms]![VESSDAT].[Form]![txtvessel] is null) OR (((dbo_VESSEL.VOYAGE_NUM) Like [Forms]![VESSDAT].[Form]![txtvoyage]) or [Forms]![VESSDAT].[Form]![txtvoyage] is null) OR (((dbo_VESSEL.PORT_CD) Like [Forms]![VESSDAT].[Form]![txtport]) or [Forms]![VESSDAT].[Form]![txtport] is null) OR (((dbo_VESSEL.DEPART_ACTUAL_DT) Between [Forms]![VESSDAT].[Form]![txtfromdept] And [Forms]![VESSDAT].[Form]![txttodept]));

-------------------------------------------------------

Thanks.
 

Attachments

  • form.png
    form.png
    5.8 KB · Views: 69
S

Simon_MT

Registered User.
Local time
Today, 04:44
Joined
Feb 26, 2007
Messages
2,177
Here is an example what you can do with searches:

Code: 
Private Function ClientsCriteria() As String

    With CodeContextObject
        If .[Search] = "A" Then
            ClientsCriteria = "[Client Address1] like '" & "*" & .[Field2] & "*" & "' or [Client Address2] like '" & "*" & .[Field2] & "*" & "'"
        ElseIf .[Search] = "C" Then
            ClientsCriteria = "[Client Town] like '" & "*" & .[Field2] & "*" & "'"
        ElseIf .[Search] = "E" Then
            ClientsCriteria = "[Client Email] like '" & "*" & .[Field2] & "*" & "' [Client Web] like '" & "*" & .[Field2] & "*" & "'"
        ElseIf .[Search] = "I" Then
            ClientsCriteria = "[Client] like '" & .[Field2] & "*" & "'"
        ElseIf .[Search] = "N" Then
            If IsNull(.[Field2]) Then
                ClientsCriteria = "[Client Surname] like '" & .[Field1] & "*" & "' or [Client Surname Alt] like '" & .[Field1] & "*" & "'"
            ElseIf IsNull(.[Field1]) Then
                ClientsCriteria = "[Client First Name] like '" & "*" & .[Field2] & "*" & "' or [Client First Name Alt] like '" & "*" & .[Field2] & "*" & "'"
            Else
                ClientsCriteria = "[Client Surname] like '" & .[Field1] & "*" & "' and [Client First Name] like '" & "*" & .[Field2] & "*" & "' or [Client Surname Alt] like '" & .[Field1] & "*" & "' and [Client First Name Alt] like '" & "*" & .[Field2] & "*" & "'"
            End If
        ElseIf .[Search] = "M" Then
            ClientsCriteria = "[Client Surname] like '" & .[Field1] & "*" & "' and [Client First Name] like '" & "*" & .[Field2] & "*" & "' and [Client Master]=True"
        ElseIf .[Search] = "O" Then
            ClientsCriteria = "[Client Co Name] like '" & .[Field2] & "*" & "'"
        ElseIf .[Search] = "P" Then
            ClientsCriteria = "[Client Postcode] like '" & .[Field2] & "*" & "'"
        ElseIf .[Search] = "T" Then
            ClientsCriteria = "[Client Tele] like '" & "*" & .[Field2] & "*" & "' or [Client Tele2] like '" & "*" & .[Field2] & "*" & "' or [Client Fax] like '" & "*" & .[Field2] & "*" & "'"
        ElseIf .[Search] = "T" Then
            ClientsCriteria = "[Client Tele] like '" & "*" & .[Field2] & "*" & "' or [Client Tele2] like '" & "*" & .[Field2] & "*" & "' or [Client Fax] like '" & "*" & .[Field2] & "*" & "'"
        End If
    End With

End Function

Code: 
Function ClientsReview() As String

    DoCmd.OpenForm "Clients Review", acNormal, "", ClientsCriteria, acFormEdit, acWindowNormal

End Function

Simon
 
C

CJ_London

Super Moderator
Staff member
Local time
Today, 04:44
Joined
Feb 19, 2013
Messages
16,668
you can also refer back to my original post #2 which addresses this problem
 
P

pwalter83

Registered User.
Local time
Yesterday, 20:44
Joined
Dec 19, 2011
Messages
31
CJ_London said:
you can also refer back to my original post #2 which addresses this problem
Click to expand...

Thanks for your reply. However, it still doesnt work as intended. I am not an MS access (or VB) programmer and have never worked on this before and this is just a one off assignment. I would really appreciate if you could please help me on this.

On the form there are 3 textboxes:

txtport
txtvessel
txtvoyage

and 2 calendar controls:

txtfromdept - which refers to the 'from date'
txttodept - which refers to the 'to date'

My requirement is that the user should enter values for at least one of the textboxes or at least the values for both the calendar controls should be entered.

Could you please be able to tell from the codes below what part I need to change ?
I am stuck on this for a long time now. Please find the SQL query and VB code below:

SQL query:
-----------------------------------------
SELECT dbo_VESSEL.VESSEL_NAME, dbo_VESSEL.VESSEL_CD, dbo_VESSEL.VOYAGE_NUM, dbo_VESSEL.PORT_CD, dbo_VESSEL.DEPART_ACTUAL_DT, dbo_VESSEL.DIVISION_CD
FROM dbo_VESSEL
WHERE (((dbo_VESSEL.VESSEL_CD) Like [Forms]![VESSDAT].[Form]![txtvessel]) or [Forms]![VESSDAT].[Form]![txtvessel] is null) OR (((dbo_VESSEL.VOYAGE_NUM) Like [Forms]![VESSDAT].[Form]![txtvoyage]) or [Forms]![VESSDAT].[Form]![txtvoyage] is null) OR (((dbo_VESSEL.PORT_CD) Like [Forms]![VESSDAT].[Form]![txtport]) or [Forms]![VESSDAT].[Form]![txtport] is null) OR (((dbo_VESSEL.DEPART_ACTUAL_DT) Between [Forms]![VESSDAT].[Form]![txtfromdept] And [Forms]![VESSDAT].[Form]![txttodept]));
---------------------------------------
VB code:

---------------------------------------
Private Sub cmdSearch_Click()
'User must enter a value in the text box
If IsNull(txtvessel) = True Or IsNull(txtvoyage) = True Or (IsNull(txtfromdept) = True And IsNull(txttodept) = True) Or IsNull(txtport) = True Then
MsgBox "You must enter search criteria."

'Open query using search criteria entered
Else
DoCmd.OpenQuery "vslvoy", acViewNormal
DoCmd.SelectObject acQuery, "vslvoy"
Screen.ActiveDatasheet.Requery
End If
If DCount("*", "vslvoy") = 0 Then
MsgBox "No Records Found"
End If
End Sub
----------------------------------

Thanks.
 

Attachments

  • form.png
    form.png
    5.8 KB · Views: 61
Last edited:
N

namliam

The Mailman - AWF VIP
Local time
Today, 05:44
Joined
Aug 11, 2003
Messages
11,695
I hope this will give you a good starting point.

Code: 
Private Sub cmdSearch_Click()
dim myQRY as string
dim myWhere as string

myqry = " SELECT dbo_VESSEL.VESSEL_NAME, dbo_VESSEL.VESSEL_CD       , dbo_VESSEL.VOYAGE_NUM " & _
        "      , dbo_VESSEL.PORT_CD    , dbo_VESSEL.DEPART_ACTUAL_DT, dbo_VESSEL.DIVISION_CD " & _
        " FROM dbo_VESSEL " & _
        " WHERE 1=1 "

'User must enter a value in the text box

If IsNull(txtvessel) = True Or IsNull(txtvoyage) = True Or (IsNull(txtfromdept) = True And IsNull(txttodept) = True) Or IsNull(txtport) = True Then
    MsgBox "You must enter search criteria."

    'Open query using search criteria entered
Else
    if not isnull(txtVessel) then 
	myWhere = myWhere & " AND dbo_VESSEL.VESSEL_CD like ""*" & txtVessel & "*"" "
    endif
    if not (IsNull(txtfromdept) And IsNull(txttodept) ) Then
        myWhere = myWhere & " AND dbo_VESSEL.DEPART_ACTUAL_DT between #" & Format(txtFromDept, "MM/DD/YYYY") & "# " & _ 
                                                                " AND #" & Format(txtToDept  , "MM/DD/YYYY") & "# "
    elseif not IsNull(txtfromdept) then 
        myWhere = myWhere & " AND dbo_VESSEL.DEPART_ACTUAL_DT >= #" & Format(txtFromDept, "MM/DD/YYYY") & "# " 
    elseif ... <add more options> 
... <add more options>
    endif
.... <add more options>


    currentdb.querydefs("vslvoy").sql = myQRY & myWhere


    
    DoCmd.OpenQuery "vslvoy", acViewNormal
    DoCmd.SelectObject acQuery, "vslvoy"
    Screen.ActiveDatasheet.Requery
End If
If DCount("*", "vslvoy") = 0 Then
MsgBox "No Records Found"
End If
End Sub
 
P

pwalter83

Registered User.
Local time
Yesterday, 20:44
Joined
Dec 19, 2011
Messages
31
namliam said:
I hope this will give you a good starting point.

Code: 
Private Sub cmdSearch_Click()
dim myQRY as string
dim myWhere as string
 
myqry = " SELECT dbo_VESSEL.VESSEL_NAME, dbo_VESSEL.VESSEL_CD       , dbo_VESSEL.VOYAGE_NUM " & _
        "      , dbo_VESSEL.PORT_CD    , dbo_VESSEL.DEPART_ACTUAL_DT, dbo_VESSEL.DIVISION_CD " & _
        " FROM dbo_VESSEL " & _
        " WHERE 1=1 "
 
'User must enter a value in the text box
 
If IsNull(txtvessel) = True Or IsNull(txtvoyage) = True Or (IsNull(txtfromdept) = True And IsNull(txttodept) = True) Or IsNull(txtport) = True Then
    MsgBox "You must enter search criteria."
 
    'Open query using search criteria entered
Else
    if not isnull(txtVessel) then 
    myWhere = myWhere & " AND dbo_VESSEL.VESSEL_CD like ""*" & txtVessel & "*"" "
    endif
    if not (IsNull(txtfromdept) And IsNull(txttodept) ) Then
        myWhere = myWhere & " AND dbo_VESSEL.DEPART_ACTUAL_DT between #" & Format(txtFromDept, "MM/DD/YYYY") & "# " & _ 
                                                                " AND #" & Format(txtToDept  , "MM/DD/YYYY") & "# "
    elseif not IsNull(txtfromdept) then 
        myWhere = myWhere & " AND dbo_VESSEL.DEPART_ACTUAL_DT >= #" & Format(txtFromDept, "MM/DD/YYYY") & "# " 
    elseif ... <add more options> 
... <add more options>
    endif
.... <add more options>
 
 
    currentdb.querydefs("vslvoy").sql = myQRY & myWhere
 
 
 
    DoCmd.OpenQuery "vslvoy", acViewNormal
    DoCmd.SelectObject acQuery, "vslvoy"
    Screen.ActiveDatasheet.Requery
End If
If DCount("*", "vslvoy") = 0 Then
MsgBox "No Records Found"
End If
End Sub
Click to expand...

Thanks very much, your code worked !!

Thanks.
 
Last edited:
You must log in or register to reply here.

Similar threads

S
hide a record while it's being edited in a form
2 3
Replies
50
Views
2,481
ebs17
E
E
Solved Listbox search not working with aggregated query
Replies
7
Views
343
elannesse
E
M
Solved Search and find records on a continuous subform
2
Replies
33
Views
1,353
Gasman
Gasman
D
Search String to include variants
Replies
6
Views
386
arnelgp
arnelgp
kstaelens
Search (if not found: add) in a table on a subform
Replies
6
Views
557
jamesave
J

Users who are viewing this thread

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