Part 2; Combo box in form will not open query

G

Guest

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)
 
G

Guest

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...
 
G

Guest

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.
 
G

Guest

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...
 
G

Guest

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...
 
G

Guest

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...
 
G

Guest

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...
 
G

Guest

:) 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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Klatuu --- 3rd combo statement wont open query 1
Textbox to search Memo Field Will Not Work Properly 6
Changed query, Form went Blank 2
Filter Specific Date in Access. 0
MultiList Select Box Search Form 7
Run select query from VBA code where criteria comes from a multi select ListBox 2
Multiselect List Box 4
Creating Preview button with variable 6

Top