1 or the other parameters

[mindbender]

I currently have a query that when executed asks for a product code- UCase$([Enter Product Code])



I have a separate query that when executed asks for a carrier #- UCase$([Enter Carrier ID])

Is there a way to create this within 1 query so they can either enter a Product Code OR A Carrier# or BOTH if they have both?

Thanks in advance for your thoughts

[ranman256]

no.
use a form, put 2 text boxes, txtCode, txtCarrier
then use vb code to open the query you want

the queries do not have the param , instead it looks at the form textbox...
qsFindCode= select * from table where [ProductCode]=forms!myForm!txtCode

sub btnOpen_click()

select case true
    case isNull(me.txtCode) and not isNull(me.txtCarrier)
          docmd.openquery "qsFindCarrier"

    case not isNull(me.txtCode) and isNull(me.txtCarrier)
          docmd.openquery "qsFindCode"

    case not isNull(me.txtCode) and not isNull(me.txtCarrier)
          docmd.openquery "qsFindBoth"
end select
end sub

[pbaldy]

Or use:

http://www.theaccessweb.com/queries/qry0001.htm

[CJ_London]

yes - depends on the output you are expecting (you haven't said what that is) something like

SELECT *
FROM myTable
WHERE ProductCode=[Enter Product Code] OR Carrier=[Enter Carrier ID]

Note you do not need UCase - Access is not case sensitive. And if you do decide you need it, drop the $ - the code will fail if you do not enter anything

[mindbender]

no.
use a form, put 2 text boxes, txtCode, txtCarrier
then use vb code to open the query you want

the queries do not have the param , instead it looks at the form textbox...
qsFindCode= select * from table where [ProductCode]=forms!myForm!txtCode

sub btnOpen_click()

select case true
    case isNull(me.txtCode) and not isNull(me.txtCarrier)
          docmd.openquery "qsFindCarrier"

    case not isNull(me.txtCode) and isNull(me.txtCarrier)
          docmd.openquery "qsFindCode"

    case not isNull(me.txtCode) and not isNull(me.txtCarrier)
          docmd.openquery "qsFindBoth"
end select
end sub

Right now it is a form that is opened and the form is populated by the query. So for this method They would get to the switchboard and see 2 fields. They would enter 1 or the other or both....is there a way to open the form results using the vba?

[ranman256]

the form will show ALL records, then filter the list when the user enters text and clicks the Filter button:


'----------------
sub btnFilter_click()
'----------------
dim sWhere as string

sWhere = "1=1"
if not IsNUll(me.txtCode) then sWhere = sWhere & " and [Code]='" & me.txtCode & "'"
if not IsNUll(me.txtCarrier) then sWhere = sWhere & " and [Carrier]='" & me.txtCarrier & "'"

If sWhere = "1=1" Then
Me.FilterOn = False
Else
Me.Filter = sWhere
Me.FilterOn = True
End If
end sub

[mindbender]

the form will show ALL records, then filter the list when the user enters text and clicks the Filter button:


'----------------
sub btnFilter_click()
'----------------
dim sWhere as string

sWhere = "1=1"
if not IsNUll(me.txtCode) then sWhere = sWhere & " and [Code]='" & me.txtCode & "'"
if not IsNUll(me.txtCarrier) then sWhere = sWhere & " and [Carrier]='" & me.txtCarrier & "'"

If sWhere = "1=1" Then
Me.FilterOn = False
Else
Me.Filter = sWhere
Me.FilterOn = True
End If
end sub

This is awesome. I am well on my way. Thank you very much.

[mindbender]

This is awesome. I am well on my way. Thank you very much.

Weird - it worked the first time then it stopped working.



I attached a picture of the design view form with the txt fields used for searching...txtCode, txtCarrier and txtMCode

When the Filter button is clicked, this runs

Private Sub Command36_Click()Dim sWhere As String


sWhere = "1=1"
If Not IsNull(Me.txtCode) Then sWhere = sWhere & " and [PROD_CD]='" & Me.txtCode & "'"
If Not IsNull(Me.txtCarrier) Then sWhere = sWhere & " and [SUB_LOC_CD]='" & Me.txtCarrier & "'"
If Not IsNull(Me.txtMCode) Then sWhere = sWhere & " and [MKT_CD]='" & Me.txtMCode & "'"


If sWhere = "1=1" Then
Me.FilterOn = False
Else
Me.Filter = sWhere
Me.FilterOn = True
End If
End Sub

This is not returning any data at all. It did the first time I ran it but not any time after

The unfilter button onclick runs this:

Private Sub Command40_Click()
Me.FilterOn = False
Me.txtCode.Value = ""
Me.txtCarrier.Value = ""
Me.txtMCode.Value = ""
Me.Refresh
End Sub

I am so close...thanks for the help

[pbaldy]

Hint: Null is not the same as "".

[mindbender]

Hint: Null is not the same as "".

I changed it to Null and the unfilter button works but the initial filter button still returns no data

[mindbender]

AHA....I did not need the .VALUE

This works like a charm

Private Sub Command40_Click()Me.FilterOn = False
Me.txtCode = Null
Me.txtCarrier = Null
Me.txtMCode = Null
Me.Refresh End Sub