RE: combo choose 1 or many

If both are text fields, they need to be enclosed in quotes.
stCriteria = "[TR_DECISION]='" & Me.DecCode & "'"
stCriteria = "[TR_STATUS]= '" & Me.cmbstatus & "'"

The where condition for OpenReport and OpenForm and all Domain Aggragate
functions are really SQL and follow these rules for passing the parameters:

Text - Enclose in single or double quotes ' or "
Date - Enclose in pound signs #
Numeric - No Enclosure.
"Dan @BCBS" wrote:

1. I added the space after AND.
2. [TR_DECISION] and [S_STATUS] are both text fields.
3. I had noticed the stCriteria and changed it in the OpenReport.

The first IF works fine, even if I change the fields to decision, I will get
the correct decision:
If Not (IsNull(Me.cmbstatus)) Then
stCriteria = "[TR_STATUS]= '" & Me.cmbstatus & "'

The problem seems to be in the second IF, the results completly ecnore this
combo box and the return is all of them.

If Not (IsNull(Me.DecCode)) Then
If Len(strCriteria) > 0 Then
strCriteria = strCriteria & " AND "
strCriteria = "[TR_DECISION] = Me.DecCode"

HELP







"Klatuu" wrote:

Change " AND" to " AND " It should have a space after it.
Be sure [TR_DECISION] is a text field
Be sure [S_STATUS] is a numeric field

The other part is probably my fault. I used stCriteria, which you copied;
however, your open report may still be using stLinkCriteria. Be sure the
variable name you are using to build the Where string is the same you are
using in the OpenReport.

"Dan @BCBS" wrote:

You have me going in the right direction, but I am getting a data return of
all records, not just the ones specific to the combo boxes.
Here are the combinations I have tried:
A.
If Not (IsNull(Me.DecCode)) Then
stCriteria = "[TR_DECISION]='" & Me.DecCode & "'"
End If

If Not (IsNull(Me.cmbstatus)) Then
If Len(strCriteria) > 0 Then
strCriteria = strCriteria & " AND"
End If
strCriteria = strCriteria & "[S_STATUS] = Me.cmbstatus"
End If

Results: I get results but not specific to the combo boxes..

B.
If Not (IsNull(Me.cmbstatus)) Then
stCriteria = "[TR_STATUS]='" & Me.cmbstatus & "'"

End If
If Not (IsNull(Me.DecCode)) Then
If Len(strCriteria) > 0 Then
strCriteria = strCriteria & " AND"
strCriteria = "[TR_DECISION] = Me.DecCode"
End If
End If

Results: Same as A.



"Klatuu" wrote:

I' having a little trouble understanding your code. I notice you are adding
stexpedited to more than one field. Can you tell me how stexpedited really
works. The syntax below has stExpedited after the word AND with no
comparision.
Another problem is you are adding " AND " to stLink Critera without knowing
whether you will be adding any other criteria.
If Not (IsNull(Me.InqType)) Then
stLinkCriteria = "[TR_INQUIRYTYPE]=" & "'" & Me.InqType & "' AND "
& stExpedited

Normally, if you are building a Where String it would be something like:

If Not IsNull(Me.FirstCombo) Then
strCriteria = "[SomeField] = '" & Me.FirstCombo & "'"
End If

If Not isNull(Me.SecondCombo) Then
If Len(strCriteria) > 0 Then
strCriteria = strCriteria & " AND"
End If
strCriteria = strCriteria & "[AnotherField] = "Me.SecondCombo"
End If


"Dan @BCBS" wrote:

Ok, although I learned alot from the reading, I still cannot get it to do
what I need.
Let me re-ask my question in it's simpliest form:
One command button.
Three Combo boxes.

Could you please show me the simpliest code, when I click the command
button, it returns the choices made from the 3 combo boxes.

If I can understand this I will be able to tweek it from there, for what I
need.

Thank you for the help.


"Klatuu" wrote:

The "tick marks" would be correct provided [TR_EXPEDITED] is a boolean data
type.

I don't know what you mean by "get no return" With a list box, you have to
use the ItemsSelected collection and handle each selected item independantly.
Read up on the ItemsSelected property in VBA Help.

"Dan @BCBS" wrote:

Ok, I did that and I can select a list of items, but I get no return of
anything when I click it... Here is my code - please see my note about 1/2
way down....


Dim stDocName As String
Dim stLinkCriteria As String
Dim Answer As String
Dim stExpedited As String

If IsNull(Me.StartDate) Or IsNull(Me.EndDate) Then
MsgBox "You must enter a Start & Stop date."
Else

Answer = MsgBox("Expedited Case??", vbQuestion + vbYesNo, "Question")
If Answer = vbYes Then
stExpedited = "[TR_EXPEDITED]=True"
Else
stExpedited = "[TR_EXPEDITED]=False"
End If

'If Not (IsNull(Me.InqType)) Then
' stLinkCriteria = "[TR_INQUIRYTYPE]=" & "'" & Me.InqType & "' AND "
& stExpedited
'''' If I remove the ticks above and put them below (for status) the job works

If Not (IsNull(Me.status)) Then
stLinkCriteria = "[TR_STATUS]=" & "'" & Me.status & "' AND " &
stExpedited

If Not (IsNull(Me.DecCode)) Then
stLinkCriteria = "[TR_DECISION]=" & "'" & Me.DecCode & "' AND " &
stExpedited
End If

stDocName = "r_GroupBasic"
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria





"Klatuu" wrote:

You cannot choose more than one selection from a combo box. For this
capability, you need a List Box with the Multi Select property set to either
Simple or Extended. Once you have made the selections, the ItemsSelected
collection of the ListBox will contain a list of the selections.

"Dan @BCBS" wrote:

Sorry, this is what I have, but it only allows me to choose one from the
combo box...

If Not (IsNull(Me.InqType)) Then
stLinkCriteria = "[TR_INQUIRYTYPE]=" & "'" & Me.InqType & "' AND " &
stExpedited


If Not (IsNull(Me.DecCode)) Then
stLinkCriteria = "[TR_DECISION]=" & "'" & Me.DecCode & "' AND " &
stExpedited


End If

stDocName = "r_GroupBasic"
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria





"Dan @BCBS" wrote:

Could you please help me get started, I'm just getting more confused, when I
read.

On a form, I have a combo box and a command button.
I need to be able to choose 1 or many from the combo box then click the
command for the results of the query.

Thank you


.