Part 2; Combo box in form will not open query

A

accessdesigner

Ok, I am hoping that if I can get this one correct, then I will be able to
get the other combo boxes to work.... as it now stands, the first if
statement works, but the second if statement will not open the query...

Dim strwhere As String

If Not IsNull(Me.combo1) Then
strwhere = strwhere & "[queryname.datefield]Between " & Me.BegYR & " And
" & Me.EndYR
End If

If Not IsNull(Me.combo2) Then
If Len(strwhere) > 0 Then
strwhere = strwhere & " And "
End If
strwhere = "[queryname.field]=" & Me.combo2
End If
DoCmd.OpenReport "Report", acPreview, , strwhere
 
D

Douglas J. Steele

What's the data type of queryname.field? If it's text, you need quotes
around the value:

strwhere = "[queryname.field]=" & Chr$(34) & Me.combo2 & Chr$(34)
 
A

accessdesigner

it's text, but why do u use chr$(34)

Douglas J. Steele said:
What's the data type of queryname.field? If it's text, you need quotes
around the value:

strwhere = "[queryname.field]=" & Chr$(34) & Me.combo2 & Chr$(34)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


accessdesigner said:
Ok, I am hoping that if I can get this one correct, then I will be able to
get the other combo boxes to work.... as it now stands, the first if
statement works, but the second if statement will not open the query...

Dim strwhere As String

If Not IsNull(Me.combo1) Then
strwhere = strwhere & "[queryname.datefield]Between " & Me.BegYR & "
And
" & Me.EndYR
End If

If Not IsNull(Me.combo2) Then
If Len(strwhere) > 0 Then
strwhere = strwhere & " And "
End If
strwhere = "[queryname.field]=" & Me.combo2
End If
DoCmd.OpenReport "Report", acPreview, , strwhere
Click to expand...
Click to expand...
 
K

Klatuu

Okay, I found it. Don't know what you mean by "open the query". None of
this code opens any query. The problem may be that you are not including the
filter from the first combo. You have too string them together. See
corrections in your code below.
 
A

accessdesigner

omg... ty....Douglas, and Klatuu... but what does chr$(34) mean? I think I
see the pattern now, but what does chr$(34) mean, and do I have to change the
#34?

strwhere = "[queryname.field]=" & Chr$(34) & Me.combo2 & Chr$(34)

Klatuu said:
Okay, I found it. Don't know what you mean by "open the query". None of
this code opens any query. The problem may be that you are not including the
filter from the first combo. You have too string them together. See
corrections in your code below.

accessdesigner said:
Ok, I am hoping that if I can get this one correct, then I will be able to
get the other combo boxes to work.... as it now stands, the first if
statement works, but the second if statement will not open the query...

Dim strwhere As String

If Not IsNull(Me.combo1) Then
strwhere = strwhere "[queryname.datefield]Between " & Me.BegYR & " And
" & Me.EndYR
End If

If Not IsNull(Me.combo2) Then
If Len(strwhere) > 0 Then
strwhere = strwhere & " And "
End If
strwhere = strWhere & "[queryname.field]=" & Me.combo2
End If
DoCmd.OpenReport "Report", acPreview, , strwhere
Click to expand...
Click to expand...
 
K

Klatuu

It depends on the data type.
Chr$(34) is the same as "
It is just another way to put quotes around the value if it is a text value.
Is is the same as
strwhere = "[queryname.field]= """ & Me.combo2 & """"

accessdesigner said:
omg... ty....Douglas, and Klatuu... but what does chr$(34) mean? I think I
see the pattern now, but what does chr$(34) mean, and do I have to change the
#34?

strwhere = "[queryname.field]=" & Chr$(34) & Me.combo2 & Chr$(34)

Klatuu said:
Okay, I found it. Don't know what you mean by "open the query". None of
this code opens any query. The problem may be that you are not including the
filter from the first combo. You have too string them together. See
corrections in your code below.

accessdesigner said:
Ok, I am hoping that if I can get this one correct, then I will be able to
get the other combo boxes to work.... as it now stands, the first if
statement works, but the second if statement will not open the query...

Dim strwhere As String

If Not IsNull(Me.combo1) Then
strwhere = strwhere "[queryname.datefield]Between " & Me.BegYR & " And
" & Me.EndYR
End If

If Not IsNull(Me.combo2) Then
If Len(strwhere) > 0 Then
strwhere = strwhere & " And "
End If
strwhere = strWhere & "[queryname.field]=" & Me.combo2
End If
DoCmd.OpenReport "Report", acPreview, , strwhere
Click to expand...
Click to expand...
Click to expand...
 
A

accessdesigner

ok, but Combo1 or Combo2 will open separately, but if I choose Combo1 AND
Combo2, it ignores Combo1 date range


Klatuu said:
It depends on the data type.
Chr$(34) is the same as "
It is just another way to put quotes around the value if it is a text value.
Is is the same as
strwhere = "[queryname.field]= """ & Me.combo2 & """"

accessdesigner said:
omg... ty....Douglas, and Klatuu... but what does chr$(34) mean? I think I
see the pattern now, but what does chr$(34) mean, and do I have to change the
#34?

strwhere = "[queryname.field]=" & Chr$(34) & Me.combo2 & Chr$(34)

Klatuu said:
Okay, I found it. Don't know what you mean by "open the query". None of
this code opens any query. The problem may be that you are not including the
filter from the first combo. You have too string them together. See
corrections in your code below.

:

Ok, I am hoping that if I can get this one correct, then I will be able to
get the other combo boxes to work.... as it now stands, the first if
statement works, but the second if statement will not open the query...

Dim strwhere As String

If Not IsNull(Me.combo1) Then
strwhere = strwhere "[queryname.datefield]Between " & Me.BegYR & " And
" & Me.EndYR
End If

If Not IsNull(Me.combo2) Then
If Len(strwhere) > 0 Then
strwhere = strwhere & " And "
End If
strwhere = strWhere & "[queryname.field]=" & Me.combo2
End If
DoCmd.OpenReport "Report", acPreview, , strwhere
Click to expand...
Click to expand...
Click to expand...
 
K

Klatuu

Lets keep this to one thread, please.

accessdesigner said:
ok, but Combo1 or Combo2 will open separately, but if I choose Combo1 AND
Combo2, it ignores Combo1 date range


Klatuu said:
It depends on the data type.
Chr$(34) is the same as "
It is just another way to put quotes around the value if it is a text value.
Is is the same as
strwhere = "[queryname.field]= """ & Me.combo2 & """"

accessdesigner said:
omg... ty....Douglas, and Klatuu... but what does chr$(34) mean? I think I
see the pattern now, but what does chr$(34) mean, and do I have to change the
#34?

strwhere = "[queryname.field]=" & Chr$(34) & Me.combo2 & Chr$(34)

:

Okay, I found it. Don't know what you mean by "open the query". None of
this code opens any query. The problem may be that you are not including the
filter from the first combo. You have too string them together. See
corrections in your code below.

:

Ok, I am hoping that if I can get this one correct, then I will be able to
get the other combo boxes to work.... as it now stands, the first if
statement works, but the second if statement will not open the query...

Dim strwhere As String

If Not IsNull(Me.combo1) Then
strwhere = strwhere "[queryname.datefield]Between " & Me.BegYR & " And
" & Me.EndYR
End If

If Not IsNull(Me.combo2) Then
If Len(strwhere) > 0 Then
strwhere = strwhere & " And "
End If
strwhere = strWhere & "[queryname.field]=" & Me.combo2
End If
DoCmd.OpenReport "Report", acPreview, , strwhere
Click to expand...
Click to expand...
Click to expand...
 
A

accessdesigner

:) ok....

Klatuu said:
Lets keep this to one thread, please.

accessdesigner said:
ok, but Combo1 or Combo2 will open separately, but if I choose Combo1 AND
Combo2, it ignores Combo1 date range


Klatuu said:
It depends on the data type.
Chr$(34) is the same as "
It is just another way to put quotes around the value if it is a text value.
Is is the same as
strwhere = "[queryname.field]= """ & Me.combo2 & """"

:

omg... ty....Douglas, and Klatuu... but what does chr$(34) mean? I think I
see the pattern now, but what does chr$(34) mean, and do I have to change the
#34?

strwhere = "[queryname.field]=" & Chr$(34) & Me.combo2 & Chr$(34)

:

Okay, I found it. Don't know what you mean by "open the query". None of
this code opens any query. The problem may be that you are not including the
filter from the first combo. You have too string them together. See
corrections in your code below.

:

Ok, I am hoping that if I can get this one correct, then I will be able to
get the other combo boxes to work.... as it now stands, the first if
statement works, but the second if statement will not open the query...

Dim strwhere As String

If Not IsNull(Me.combo1) Then
strwhere = strwhere "[queryname.datefield]Between " & Me.BegYR & " And
" & Me.EndYR
End If

If Not IsNull(Me.combo2) Then
If Len(strwhere) > 0 Then
strwhere = strwhere & " And "
End If
strwhere = strWhere & "[queryname.field]=" & Me.combo2
End If
DoCmd.OpenReport "Report", acPreview, , strwhere
Click to expand...
Click to expand...
Click to expand...
 
Top