Re: combo choose 1 or many

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

I think I'm 99% there - this code returns data based on the answer to the two
combo boxes. BUT, it does not consider the answer to the MsgBox.

The DoCmd shows everything for "stCriteria" but how do I include the
"stExpedited"

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.cmbstatus)) Then
stCriteria = "[TR_Status]= '" & Me.cmbstatus & "'"

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

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



"Klatuu" wrote:

Caught my typo, thanks, actually, it is text and should be:

strCriteria = strCriteria & "[TR_DECISION] = '" & Me.DecCode & "'"


"Douglas J Steele" wrote:

The last statement needs to be:

strCriteria = strCriteria & "[TR_DECISION] = Me.DecCode"


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Dan @BCBS" <DanBCBS@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:49CB9213-AE58-4253-9937-4C6A14E5E2F0@xxxxxxxxxxxxxxxx
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





.

Relevant Pages

  • RE: combo choose 1 or many
    ... Text - Enclose in single or double quotes ' or " ... "Dan @BCBS" wrote: ... if you are building a Where String it would be something like: ...
    (microsoft.public.access.formscoding)
  • Re: combo choose 1 or many
    ... "Dan @BCBS" wrote: ... Dim stDocName As String ... Dim stLinkCriteria As String ...
    (microsoft.public.access.formscoding)
  • Re: combo choose 1 or many
    ... "Dan @BCBS" wrote: ... Dim stDocName As String ... Dim stLinkCriteria As String ...
    (microsoft.public.access.formscoding)
  • RE: combo choose 1 or many
    ... your open report may still be using stLinkCriteria. ... "Dan @BCBS" wrote: ... if you are building a Where String it would be something like: ...
    (microsoft.public.access.formscoding)
  • RE: Calculations in Reports
    ... "Dan @BCBS" wrote: ... "Klatuu" wrote: ... Dave Hargis, Microsoft Access MVP ...
    (microsoft.public.access.reports)