"And" & "Or" function VBA syntax question:

[LeslieJ via AccessMonster.com]

I have a continuous form that acts as a search form for my database. I have
seven search criteria controls that could be used to filter the form. I use
a command button to filter the form, and take the filter off. The form is
based on a query that I made from several different tables in order to get
all the information I need.

Two of the controls are giving me troubles with the VBA. I have declared
strWhere as a string.

The first one, I have two combo boxes called cboFilterStatus1, and
cboFilterStatus2 so that the users can choose to filter the form using two
status types.

The code I have for it is this:
If Not IsNull(Me.cboFilterStatus1) And Not IsNull(Me.cboFilterStatus2) Then
strWhere = strWhere & "[Document Version Information].[Document Status Type]
= " & Me.cboFilterStatus1 & " OR [Document Version Information].
[Document Status Type] = " & Me.cboFilterStatus2
End If
It gives me a Run Time Error 2448 ‘You can’t assign a value to this objectâ€
I have checked with a message box that it's trying to send the right values
to the filter.

The second problem is two text boxes called txtEffStartDate and txtEffEndDate.
I want the users to be able to type in two dates, and only records that fall
between the date range will be shown once filtered.

The code I have for this is:
If Not IsNull(Me.txtEffStartDate) And Not IsNull(Me.txtEffEndDate) Then
strWhere = strWhere & "[Document Version Information].[Effective Date] >= " &
Me.txtEffStartDate & " And [Document Version Information].[Effective Date] <=
" & Me.txtEffEndDate
End If
It also gives me a Run Time Error 2448.

I have double checked that all my table names are correct, and spelling is
correct, and no mistakes in typing.

I know that I’ve just been looking at this for too long, and that is why I am
not seeing what the error is.

This site has been a mind-saver, I thank you for all your help!

 

[mcescher]

I have a continuous form that acts as a search form for my database.  Ihave
seven search criteria controls that could be used to filter the form.  I use
a command button to filter the form, and take the filter off.  The formis
based on a query that I made from several different tables in order to get
all the information I need.

Two of the controls are giving me troubles with the VBA.  I have declared
strWhere as a string.

The first one, I have two combo boxes called cboFilterStatus1, and
cboFilterStatus2 so that the users can choose to filter the form using two
status types.

The code I have for it is this:
If Not IsNull(Me.cboFilterStatus1) And Not IsNull(Me.cboFilterStatus2) Then
strWhere = strWhere & "[Document Version Information].[Document Status Type]
= " &      Me.cboFilterStatus1 & " OR [Document Version Information].
[Document Status Type] = " & Me.cboFilterStatus2
End If
It gives me a Run Time Error 2448 ‘You can’t assign a value to this object”
I have checked with a message box that it's trying to send the right values
to the filter.  

The second problem is two text boxes called txtEffStartDate and txtEffEndDate.
I want the users to be able to type in two dates, and only records that fall
between the date range will be shown once filtered.

The code I have for this is:
If Not IsNull(Me.txtEffStartDate) And Not IsNull(Me.txtEffEndDate) Then
strWhere = strWhere & "[Document Version Information].[Effective Date] >= " &
Me.txtEffStartDate & " And [Document Version Information].[Effective Date] <=
" & Me.txtEffEndDate
End If
It also gives me a Run Time Error 2448.

I have double checked that all my table names are correct, and spelling is
correct, and no mistakes in typing.

I know that I’ve just been looking at this for too long, and that is why I am
not seeing what the error is.

This site has been a mind-saver, I thank you for all your help!

Just a shot in the dark, how is strWhere dimensioned? as what type?

If Not IsNull(Me.cboFilterStatus1) And Not IsNull(Me.cboFilterStatus2) Then
strWhere = strWhere & "[Document Version Information].[Document Status Type]
= " & Me.cboFilterStatus1 & " OR [Document Version Information].
[Document Status Type] = " & Me.cboFilterStatus2
End If

Is there a hard carriage return before the second ocurrance of
"[Document Status Type]" or is that just the browser formatting?

Have you tried doing this as a couple of statements

strWhere = strWhere & "[Document Version Information].[Document Status
Type] > = "
strWhere = strWhere & Me.cboFilterStatus1 & " OR "
strWhere = strWhere & "[Document Version Information].[Document Status
Type] = "
strWhere = strWhere & Me.cboFilterStatus2

This may help you with the debugging process, and then you can shorten
it back up after you get it working again.

Hope this helps,
Chris M.

 

[Allen Browne]

Be very careful how you bracket when mixing ANDs and ORs.

Something like this:

Dim strWhere As String

If IsNull(Me.cboFilterStatus1) Then
If Not IsNull(Me.cboFilterStatus2) Then
strWhere = "([Document Version Information].[Document Status Type] =
" & Me.cboFilterStatus2 & ")"
End If
Else
If IsNull(Me.cboFilterStatus2) Then
strWhere = "([Document Version Information].[Document Status Type] =
" & Me.cboFilterStatus1 & ")"
Else
strWhere = "(([Document Version Information].[Document Status Type]
= " & Me.cboFilterStatus1 & _
") OR ([Document Version Information].[Document Status Type] = "
& Me.cboFilterStatus2 & "))"
End If
End If

We are assuming that the Type field is a Number data type (not a Text
field.)

Regarding the dates, see Method 2 in:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a continuous form that acts as a search form for my database. I
have
seven search criteria controls that could be used to filter the form. I
use
a command button to filter the form, and take the filter off. The form is
based on a query that I made from several different tables in order to get
all the information I need.

Two of the controls are giving me troubles with the VBA. I have declared
strWhere as a string.

The first one, I have two combo boxes called cboFilterStatus1, and
cboFilterStatus2 so that the users can choose to filter the form using two
status types.

The code I have for it is this:
If Not IsNull(Me.cboFilterStatus1) And Not IsNull(Me.cboFilterStatus2)
Then
strWhere = strWhere & "[Document Version Information].[Document Status
Type]
= " & Me.cboFilterStatus1 & " OR [Document Version Information].
[Document Status Type] = " & Me.cboFilterStatus2
End If
It gives me a Run Time Error 2448 ‘You can’t assign a value to this
objectâ€
I have checked with a message box that it's trying to send the right
values
to the filter.

The second problem is two text boxes called txtEffStartDate and
txtEffEndDate.
I want the users to be able to type in two dates, and only records that
fall
between the date range will be shown once filtered.

The code I have for this is:
If Not IsNull(Me.txtEffStartDate) And Not IsNull(Me.txtEffEndDate) Then
strWhere = strWhere & "[Document Version Information].[Effective Date] >=
" &
Me.txtEffStartDate & " And [Document Version Information].[Effective Date]
<=
" & Me.txtEffEndDate
End If
It also gives me a Run Time Error 2448.

I have double checked that all my table names are correct, and spelling is
correct, and no mistakes in typing.

I know that I’ve just been looking at this for too long, and that is why I
am
not seeing what the error is.

This site has been a mind-saver, I thank you for all your help!

 

[Stockwell43]

Although this sounds weird it might be worth trying. I found this site and it
said to change the Tab Control of your textbox to No and the reply was that
it worked. Here is the site:

http://www.utteraccess.com/forums/s...true&where=&Zu=123600&Zd=l&Zn=&Zt=3e8&Zs=&Zy=

Good Luck!

 

[Stockwell43]

If that doesn't work have a gander at this and maybe it might help:

http://support.microsoft.com/kb/208970