G
Guest
Combo1 or Combo2 will open separately, but if I choose Combo1 AND Combo2, it
ignores COmbo1 date range
ignores COmbo1 date range
Klatuu said:
G
Guest
Check you code to be sure you are concatenating strWhere, not replacing it.
Then run your code in Debug mode with a breakpoint on the openreport line.
Look at the value of strWhere and post that back.
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
Then run your code in Debug mode with a breakpoint on the openreport line.
Look at the value of strWhere and post that back.
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
accessdesigner said:
G
Guest
one last question.... how do create a combo box to allow
the user to type in keywords that is connected to a memo field within a
query? could u show me an example of one that works... using the strwhere
statement, but how is the combo setup, and its property fields?
the user to type in keywords that is connected to a memo field within a
query? could u show me an example of one that works... using the strwhere
statement, but how is the combo setup, and its property fields?
Klatuu said:I can't find the post you are referring to.
accessdesigner said:Klatuu, please see post it: Part 2, Combo box in form will not open
query,..... the 2nd if option wont open the query at all....
accessdesigner said:You, seriously, are a life saver!... You beat me to the punch because I then
had to figure out how to make the beggining dates not work when if it is
higher than the end dates (laughing frustratively)... omg...
I used me.visible = false so that the query prompt box would not show, but I
guess I dont need it any more since there are no criteria in the query...
well, now I'm on my way towards attempting to add the other if statements for
the remaining combo boxes,... (I'll create a new POST IT stream...
tyvm....
:
Use the actual name of the field in the query. Since I don't know your field
names, I am using examples.
queryname.datefield is an example.
It the field actually a Date/Time data type?
Also, as to the range checks for your year text boxes. I would do those in
the BeforeUpdate event of the check boxes to ensure the user has entered
acceptable values.
PLEASE NOTE - ALL NAMES USED IN THE EXAMPLES ARE PROBABLY MADE UP AND NEED
TO BE REPLACES WITH THE ACTUAL NAMES YOU USE!!!!!
Private Function BegYr_BeforeUpdate(Cancel As Integer)
If Me.BegYr < 1990 Or Me.BegYr > 2015 Then
MsgBox "Year must be between 1990 and 2015"
Cancel = True
End If
If Not IsNull(Me.EndYr) And Me.BegYr < Me.EndYr Then
MsgBox "Beginning Year Must be Less than or Equal to the End Year"
Cancel = True
End If
End Function
(same for the End year text box, with the names changed where appropriate.)
Now, we can simplfy the code:
strWhere = strWhere & "Year([queryname.datefield]) Between " & Me.BegYR
& " And " & Me.EndYR
Me.Visible = False 'Why are we making the form hidden?
DoCmd.OpenReport "Report", acPreview, , strwhere
:
It's not reading the date field correctly...
If Not IsNull(Me.BegYR) Then
If Me!BegYR >= 1990 Then
If Me!BegYR <= 2015 Then
If Me!EndYR >= 1990 Then
If Me!EndYR <= 2015 Then
If Len(strwhere) > 0 Then
strwhere = "Year([queryname.datefield]) Between " &
Me.BegYR & " And " & Me.EndYR
Me.Visible = False
Else
End If
End If
End If
End If
End If
End If
DoCmd.OpenReport "Report", acPreview, , strwhere
:
The where argument of the OpenReport method requires the syntax you would use
in SQL (a query) to determine which rows will be included in the recordset.
Because the value to be compared to the value in the field has to be enclosed
in either single or double quotes, those have to be included in the string
you build.
"[Field8] = '" & me.Combo8 & "'"
can also be written as
"[Field8] = """ & me.Combo8 & """"
Assuming the value in Combo 8 is "Foo", In the first case, the string will
return
[Field8] = 'Foo'
And in the second case it will return
[Field8] = "Foo"
The only real difference is that if there is a chance the data could contain
single qoutes, it will be a problem. For example, when searching on a name
field and you have anything like O'Reilly, you need to use the second form.
You do not use literally [Field8]. You use the name of the field you want
to filter on.
Assuming the field you want to use in the Between 1990 and 2015 is a date
field, you would need to separate the dates and use:
strWhere = "Year([MyDateField]) Between 1990 And 2015"
is the years will always be the same. If you have text boxes to select the
start and end years it would be like this:
strWhere = "Year([MyDateField]) Between " & Me.txtStartYear & " And " &
Me.txtEndYear
:
ok, (laughing), I already know I need to take an Access programming class,
but this database is due before the class begins, and attaching the combos to
the query is all left that I have to do.... please explain what the strWhere
= "[Field8] = '" & me.Combo8 & "'" statement does, and why do you have single
and double quotes; and since field8 is in a query, do I precisely use...
[queryname.field8] = ' ,, also how do i attach "between 1990 and 2015" date
criteria in the date example (help!!!)
:
For characters (text field in table)
If Not IsNull(Me.Combo8) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
strWhere = "[Field8] = '" & Me.Combo8 & "'"
End If
A four digit year by itself would be a numeric field and need no
deilimiters; however, for a date data type, the syntax would be:
If Not IsNull(Me.Combo8) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
strWhere = "[Field8] = #" & Me.Combo8 & "#"
End If
Remember, you have to use the delimiter appropriate to the data type of the
field in the table.
If you are still having problems, post back with your version of the code,
an error number, if you get one, and on which line it occurs. Or if you are
not getting an error, describe what is and is not happening.
:
your program looks easy enough, but when i tried to use it, it didnt work,...
could you give me an example of just two if statements using delimiters. One
for a 4 digit year date field and the other for characters?
:
You need the query to retrieve the data. Don't put any criteria in it. The
criteria will be passed to the report which will then do the filtering. It
is much like filtering a form.
:
That's pretty good,.... so then are you saying that I would not need to use a
query, or... do not put any criterias into the query?
:
If what you are trying to do is build a multi field filter, it would be
easier to just use 8 combos, one for each field. The coding to build the row
source for the one combo using a list box of fields from the query, would be
significant.
I would have the 8 combos, and a command button to run the report. Then in
the Click event of the command button, I would build a Where string to filter
the report based on the selection is the boxes. I would assume that if no
selection were made in a combo, then that field would not be filtered. Here
is how I would would the string:
dim strWhere As String
If Not IsNull(Me.Combo1) Then
strWhere = "[Field1] = " & Me.Combo1
End If
If Not IsNull(Me.Combo2) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
strWhere = "[Field2] = " & Me.Combo2
End If
....... I left out 3 through 7, but you get the idea, also, I did not
include any delimiters, because I don't know the field data types.
If Not IsNull(Me.Combo8) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " And "
End If
strWhere = "[Field8] = " & Me.Combo8
End If
Docmd.OpenReport "My ReportName", acPreview, , strWhere
Of course, you will not want any filtering in the query. The above will
work whether you choose no options or all of them.
:
all i can say is that, yes i created a multi select list box, that, instead
of selecting one option at a time, the user can select up to 8 options at one
time... my report printpriew and query works when i select one at a time, it
used to work when i selected two at a time, not sure what i ruined while
fiddling with it, ok, maybe not select up to 8, but select at least the date
alone, or the date with one or two other selections, but 8 choices,... all
the form is required to do is pull from the query and open a report in
printpreview/print...
if I could get up to 3 option choices to work then I'm fine with that:
select [date]
or
select [date] AND [file#]
or
select [date] AND [file#] AND [org]
---------------------------------------------------------------------
:
You are getting very complicated here. Are you aware that to do what you
want, you will have to change the number of columns, the columns widths, the
bound column, etc each time you do this? In addition, you would probably
need a multi select list box with all the options to select, unless you are
doing that programmatically.
How about describing what the purpose is? There may be an easier way.
:
That works, but I have more than one select option...
select [date]
or
select [file#]
or
[org]
or
select [date] AND [file#]
or
select [date] AND [file#] AND [org]
or etc...
or etc..........
etc.... up to 8 full select options....
:
Your terminology is confusing. What do you mean by 8 unattahed option
controls?
To get a combo box to list what is in a query, you need to set the rowsource
type to table/query and the rowsource to the name of the query.
:
How do I get my unbound combo boxes to read a select query that has up to 8
unattached option controls?
