Adding where command or filter into code??

[evilcowstare via AccessMonster.com]

Hi I have a form that runs a report based on the dates entered into two text
boxes, i need to add a Where statement like I have in my macro but need it
written into the code for the dates function.

Basically I want to add a drop-down box field as an additional filter, so it
finds in this case all the records within the two dates for a particular
client, selected in the drop down.

The drop down is called "clientdatecombo" and the form it is in is called
"clientanddate"
The combo is on the same form as the txt boxes for the date.

Ok here is the date code

Private Sub OK_Click()
Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "DateReport"
strField = "DateJobReceived"

If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere = strField & " <= " & Format(Me.txtEndDate,
conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere = strField & " >= " & Format(Me.txtStartDate,
conDateFormat)
Else 'Both start and end dates.
strWhere = strField & " Between " & Format(Me.txtStartDate,
conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If
End If

Debug.Print strWhere 'For debugging purposes only.

DoCmd.OpenReport strReport, acViewPreview, , strWhere
End Sub


If anyone knows of a way to add the filter/Where into this so at the same
time it just shows the records for that one client selected it would be
really appreciated.

I really an a novice when it comes to code so please be gentle )

Thank You For Your Help !!!!

 

[Carl Rapson]

After your date code, add the client to the end of the Where clause:

strWhere = strWhere & " AND Client='" & clientdatecombo & "'"

Note the single quotes surrounding the client value. This assumes the value
from the combo box is a string; if it's numeric, omit the extra quotes:

strWhere = strWhere & " AND Client=" & clientdatecombo

Carl Rapson

 

[evilcowstare via AccessMonster.com]

Hi Carl, thank you for replying!
I have one slight problem, I cant get it to work. Maybe Im putting it in the
wrong place but so far it either makes it do nothing or when I try to run it
it asks for the parameter of Client?

Should I put it just before the very last End Sub, Ive copy and pasted the
code you put and so far have tried just above the last End Sub and just above
the last End If. Sorry I know im probably being thick :-/
The value in the combo box is just txt not a number.

Thank You for your help !

After your date code, add the client to the end of the Where clause:

strWhere = strWhere & " AND Client='" & clientdatecombo & "'"

Note the single quotes surrounding the client value. This assumes the value
from the combo box is a string; if it's numeric, omit the extra quotes:

strWhere = strWhere & " AND Client=" & clientdatecombo

Carl Rapson

Hi I have a form that runs a report based on the dates entered into two
text

[quoted text clipped - 50 lines]

Thank You For Your Help !!!!

 

[Carl Rapson]

You should put it right after the last End If, just before you call
OpenReport. I just made up the name of the Client field; you'll need to put
in your actual field name.

Carl Rapson

Hi Carl, thank you for replying!
I have one slight problem, I cant get it to work. Maybe Im putting it in
the
wrong place but so far it either makes it do nothing or when I try to run
it
it asks for the parameter of Client?

Should I put it just before the very last End Sub, Ive copy and pasted the
code you put and so far have tried just above the last End Sub and just
above
the last End If. Sorry I know im probably being thick :-/
The value in the combo box is just txt not a number.

Thank You for your help !

After your date code, add the client to the end of the Where clause:

strWhere = strWhere & " AND Client='" & clientdatecombo & "'"

Note the single quotes surrounding the client value. This assumes the
value
from the combo box is a string; if it's numeric, omit the extra quotes:

strWhere = strWhere & " AND Client=" & clientdatecombo

Carl Rapson

Hi I have a form that runs a report based on the dates entered into two
text

[quoted text clipped - 50 lines]

Thank You For Your Help !!!!

 

[evilcowstare via AccessMonster.com]

Hi Carl, Thank You
Ive put it where you said, Im pressuming that the Client Field is that name
of the field from the main table that the report is created from
In the main table the client field is called ....Client Name
So I am putting in
......
End If

Debug.Print strWhere 'For debugging purposes only.

strWhere = strWhere & " AND Client Name ='" & clientdatecombo & "'"

DoCmd.OpenReport strReport, acViewPreview, , strWhere

I keep getting the error message...

Run Time Error 3075
Syntax error (missing operator) in query expression '(DateJobReceivedBetween
#01/10/2000# And #01/01/2020# AND Client Name = 'Barracuda Ltd')'.

The date is what i put in to test, Barracuda is what I selected from the drop-
down box.

When I click Debug, it highlights in yellow the DoCmd... Line

Any Ideas?

Thanks Again for all your help I really appreciate it !!

You should put it right after the last End If, just before you call
OpenReport. I just made up the name of the Client field; you'll need to put
in your actual field name.

Carl Rapson

Hi Carl, thank you for replying!
I have one slight problem, I cant get it to work. Maybe Im putting it in

[quoted text clipped - 28 lines]

 

[Carl Rapson]

If what you pasted is accurate, you're missing a space between
DateJobReceived and Between. Just for clarity, I'd also put parentheses
around the date clause:

(DateJobReceived Between #1/10/2000# And #1/1/2020#) And Client='Barracuda
Ltd'

Carl Rapson

Hi Carl, Thank You
Ive put it where you said, Im pressuming that the Client Field is that
name
of the field from the main table that the report is created from
In the main table the client field is called ....Client Name
So I am putting in
.....
End If

Debug.Print strWhere 'For debugging purposes only.

strWhere = strWhere & " AND Client Name ='" & clientdatecombo & "'"

DoCmd.OpenReport strReport, acViewPreview, , strWhere

I keep getting the error message...

Run Time Error 3075
Syntax error (missing operator) in query expression
'(DateJobReceivedBetween
#01/10/2000# And #01/01/2020# AND Client Name = 'Barracuda Ltd')'.

The date is what i put in to test, Barracuda is what I selected from the
drop-
down box.

When I click Debug, it highlights in yellow the DoCmd... Line

Any Ideas?

Thanks Again for all your help I really appreciate it !!

You should put it right after the last End If, just before you call
OpenReport. I just made up the name of the Client field; you'll need to
put
in your actual field name.

Carl Rapson

Hi Carl, thank you for replying!
I have one slight problem, I cant get it to work. Maybe Im putting it in

[quoted text clipped - 28 lines]

Thank You For Your Help !!!!

 

[evilcowstare via AccessMonster.com]

Hi Carl, thanks
I dont really see where the code they are giving the error for is in the code
that is written.
Im guessing it is just shortening it down to something simple but dont
understand what bit in the actual code it is finding the error in and what
needs to be changed???

Sorry for all the hassle, I appreciate the help !!!

This is the full code, followed by the error.

Private Sub OK_Click()
Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "DateReport"
strField = "DateJobReceived"

If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere = strField & " <= " & Format(Me.txtEndDate,
conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere = strField & " >= " & Format(Me.txtStartDate,
conDateFormat)
Else 'Both start and end dates.
strWhere = strField & " Between " & Format(Me.txtStartDate,
conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If
End If
strWhere = strWhere & " AND clientname = '" & clientdatecombo & " '"
Debug.Print strWhere 'For debugging purposes only.



DoCmd.OpenReport strReport, acViewPreview, , strWhere

End Sub


Error Message gives this....
Run Time Error 3075
Syntax error (missing operator) in query expression '(DateJobReceivedBetween
#01/10/2000# And #01/01/2020# AND Client Name = 'Barracuda Ltd')'.


I cant see where it relates to the main code to know what it is asking to be
changed in the main bit?

If what you pasted is accurate, you're missing a space between
DateJobReceived and Between. Just for clarity, I'd also put parentheses
around the date clause:

(DateJobReceived Between #1/10/2000# And #1/1/2020#) And Client='Barracuda
Ltd'

Carl Rapson

Hi Carl, Thank You
Ive put it where you said, Im pressuming that the Client Field is that

[quoted text clipped - 40 lines]

 

[Carl Rapson]

The code from the error message is what is contained in your strWhere
variable. You should see the same code in the Immediate window as a result
of your Debug.Print statement. As for the problem with the code, that's what
we have to track down.

As I said, unless you typed the error message wrong you are missing a space
between the words DateJobReceived and Between in strWhere. Make sure you
lave a space before and after the word Between when you are building the
strWhere string.

If that's not the problem, then I'm not sure what the problem is. Here's one
thing to try: when the value of strWhere appears in the Immediate window (or
you can copy it from the Error message window), copy it and paste it into a
new, blank Query opened in SQL View. Note: don't re-type what you think is
in the string; copy it from the program directly. At the beginning of the
pasted string, add the appropriate SELECT and FROM clauses to make a
complete SQL statement, something like:

SELECT * FROM

WHERE ...

(your strWhere code follows WHERE). Table should be the name of the table or
query your report is based on. Now run the query and see if it executes
successfully or if you get an error. That error might give you a little more
information about what the problem is. Once you can get the query working in
the SQL View window, you can modify your VBA code to produce the same
results.

One other thing you could try is to use double quotes (") instead of single
quotes (') around the value of the Client Name. To do this, replace the
single quotes in your string-building code with TWO double quotes:

strWhere = strWhere & " AND clientname = """ & clientdatecombo & """"

Count the number of double quotes above to make sure you get them right;
each place you want a double quote in your strWhere string, you need to put
two double quotes.

Aside from those two suggestions, I don't have any other ideas right now.

Carl Rapson

Hi Carl, thanks
I dont really see where the code they are giving the error for is in the
code
that is written.
Im guessing it is just shortening it down to something simple but dont
understand what bit in the actual code it is finding the error in and what
needs to be changed???

Sorry for all the hassle, I appreciate the help !!!

This is the full code, followed by the error.

Private Sub OK_Click()
Dim strReport As String 'Name of report to open.
Dim strField As String 'Name of your date field.
Dim strWhere As String 'Where condition for OpenReport.
Const conDateFormat = "\#mm\/dd\/yyyy\#"

strReport = "DateReport"
strField = "DateJobReceived"

If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End date, but no start.
strWhere = strField & " <= " & Format(Me.txtEndDate,
conDateFormat)
End If
Else
If IsNull(Me.txtEndDate) Then 'Start date, but no End.
strWhere = strField & " >= " & Format(Me.txtStartDate,
conDateFormat)
Else 'Both start and end dates.
strWhere = strField & " Between " & Format(Me.txtStartDate,
conDateFormat) _
& " And " & Format(Me.txtEndDate, conDateFormat)
End If
End If
strWhere = strWhere & " AND clientname = '" & clientdatecombo & " '"
Debug.Print strWhere 'For debugging purposes only.



DoCmd.OpenReport strReport, acViewPreview, , strWhere

End Sub


Error Message gives this....
Run Time Error 3075
Syntax error (missing operator) in query expression
'(DateJobReceivedBetween
#01/10/2000# And #01/01/2020# AND Client Name = 'Barracuda Ltd')'.


I cant see where it relates to the main code to know what it is asking to
be
changed in the main bit?

<snipped>

 

[evilcowstare via AccessMonster.com]

Hi Carl, thank you very much I'll give it a go and see what happens. I've
really appreciated your help with this !!!!
Thank you loads !!!!

All the best
Jay

The code from the error message is what is contained in your strWhere
variable. You should see the same code in the Immediate window as a result
of your Debug.Print statement. As for the problem with the code, that's what
we have to track down.

As I said, unless you typed the error message wrong you are missing a space
between the words DateJobReceived and Between in strWhere. Make sure you
lave a space before and after the word Between when you are building the
strWhere string.

If that's not the problem, then I'm not sure what the problem is. Here's one
thing to try: when the value of strWhere appears in the Immediate window (or
you can copy it from the Error message window), copy it and paste it into a
new, blank Query opened in SQL View. Note: don't re-type what you think is
in the string; copy it from the program directly. At the beginning of the
pasted string, add the appropriate SELECT and FROM clauses to make a
complete SQL statement, something like:

SELECT * FROM

WHERE ...

(your strWhere code follows WHERE). Table should be the name of the table or
query your report is based on. Now run the query and see if it executes
successfully or if you get an error. That error might give you a little more
information about what the problem is. Once you can get the query working in
the SQL View window, you can modify your VBA code to produce the same
results.

One other thing you could try is to use double quotes (") instead of single
quotes (') around the value of the Client Name. To do this, replace the
single quotes in your string-building code with TWO double quotes:

strWhere = strWhere & " AND clientname = """ & clientdatecombo & """"

Count the number of double quotes above to make sure you get them right;
each place you want a double quote in your strWhere string, you need to put
two double quotes.

Aside from those two suggestions, I don't have any other ideas right now.

Carl Rapson

Hi Carl, thanks
I dont really see where the code they are giving the error for is in the

[quoted text clipped - 48 lines]

be
changed in the main bit?

<snipped>