WHERE String ? Please Help

N

niuginikiwi

I have a combo box and two list boxes with values plus two text boxes for a
Date Range that will supply criteria for a report that is based on a select
query.
It worked fine with the combo and the list boxes until i added in the Date
Range bit. It says Compile Error. Object Required and from there I'm lost.

Can someone look through the code below and let me know where I am goin
wrong? I don't have much programming experience. Thanks.

Private Sub btnEdit_Click()
Dim cbo As ComboBox
Dim lst As ListBox
Dim strFromDate As Date 'Name of criteria start date field.
Dim strToDate As Date 'Name of criteria end date field.
Const conDateFormat = "\#mm\/dd\/yyyy\#"
Dim strWhere As String
Dim iLen As Integer

'CropName Combo
Set cbo = Me.cboCrop
If Not IsNull(cbo) Then
strWhere = strWhere & "(CropName = """ & cbo.Column(1) & """) AND "
End If

'GrowerName List
Set lst = Me.lstGrower
If Not IsNull(lst) Then
strWhere = strWhere & "(GrowerName = """ & lst.Column(1) & """) AND "
End If

'VarietyName List
Set lst = Me.lstVariety
If Not IsNull(lst) Then
strWhere = strWhere & "(VarietyName = """ & lst.Column(1) & """) AND "
End If

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

'Similar for other list, combo or text boxes here.

'Did we get anything?
iLen = Len(strWhere) - 5 'Without trailing " AND ".
If iLen < 1 Then
MsgBox "Opps! Must supply at least one criteria"
Else
strWhere = Left$(strWhere, iLen)

'Use it for this form.
Me.Filter = strWhere
Me.FilterOn = True

'Open the report and apply filter.
DoCmd.OpenReport "rptSowing", acViewPreview, , strWhere


End If

Set cbo = Nothing
Set lst = Nothing
Me.lstVariety = Null
Me.lstGrower = Null
End Sub
 
J

Jason Martin

Const conDateFormat = "\#mm\/dd\/yyyy\#":

- remove the \# , should be "mm\/dd\/yyyy"

Add the # signs to your strWhere statement:

- strWhere = strWhere & "(DateSown <= #" & Format(strToDate,
 
N

niuginikiwi

Jason,
I did like you said but I think I am not sure where to put place all the #
in their appropirate places and it keeps on throwing me out.
Can you or someone please give me a more detailed help on how to tame that
Date Range Bit. I have been spending hours on this and still can't get it to
work.
Below is the same code again if it helps.
Thanks in advance.


Private Sub btnEdit_Click()
Dim cbo As ComboBox
Dim lst As ListBox
Dim strFromDate As Date 'Name of criteria start date field.
Dim strToDate As Date 'Name of criteria end date field.
Const conDateFormat = "\#mm\/dd\/yyyy\#"
Dim strWhere As String
Dim iLen As Integer

'CropName Combo
Set cbo = Me.cboCrop
If Not IsNull(cbo) Then
strWhere = strWhere & "(CropName = """ & cbo.Column(1) & """) AND "
End If

'GrowerName List
Set lst = Me.lstGrower
If Not IsNull(lst) Then
strWhere = strWhere & "(GrowerName = """ & lst.Column(1) & """) AND "
End If

'VarietyName List
Set lst = Me.lstVariety
If Not IsNull(lst) Then
strWhere = strWhere & "(VarietyName = """ & lst.Column(1) & """) AND "
End If

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

'Similar for other list, combo or text boxes here.

'Did we get anything?
iLen = Len(strWhere) - 5 'Without trailing " AND ".
If iLen < 1 Then
MsgBox "Opps! Must supply at least one criteria"
Else
strWhere = Left$(strWhere, iLen)

'Use it for this form.
Me.Filter = strWhere
Me.FilterOn = True

'Open the report and apply filter.
'DoCmd.OpenReport "rptSowing", acViewPreview, , strWhere
DoCmd.OpenForm "frmSowUpdate", acFormDS, , strWhere

End If

Set cbo = Nothing
Set lst = Nothing
Me.lstVariety = Null
Me.lstGrower = Null
End Sub
 
D

Douglas J Steele

Whatever for? There's absolutely nothing wrong with including the \# in the
format statement.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


"Jason Martin" <jason[dot]martin(remove this)[at]triatechnology[dot]com>
wrote in message Const conDateFormat = "\#mm\/dd\/yyyy\#":

- remove the \# , should be "mm\/dd\/yyyy"

Add the # signs to your strWhere statement:

- strWhere = strWhere & "(DateSown <= #" & Format(strToDate,
 
D

Douglas J Steele

Don't know whether or not it's the cause, but you don't have any spaces
around the ORs in your date handling bit (nor around the final AND)

Try using

Debug.Print strWhere

after you've built the entire string. Go the Immediate Window (Ctrl-G) to
see what the string looks like.
 
N

niuginikiwi

Hi Doug,
I have just put spaces infront of the two ORs and the final AND and added in
the Debug.Printer strWhere line but I am still gettin the same error message
which is Compile Error: Object Required and highlights the strFrom variable
on the line
Set strFrom=Me.txtStartDate.
I don't know what Object is talkin about and I am stuck at this point.
should I stick to this or is there another way I can do this? Can you look
thru my code and test it if you can to see what the problem is? Do I have to
include any other objects from the Library including DOA 3.6.... this is just
a wild guess seeing it says object required.
Below is the code again. Thanks.

Private Sub btnEdit_Click()
Dim cbo As ComboBox
Dim lst As ListBox
Dim strFrom As Date 'Name of criteria start date field.
Dim strTo As Date 'Name of criteria end date field.
Const conDateFormat = "\#mm\/dd\/yyyy\#"
Dim strWhere As String
Dim iLen As Integer

'CropName Combo
Set cbo = Me.cboCrop
If Not IsNull(cbo) Then
strWhere = strWhere & "(CropName = """ & cbo.Column(1) & """) AND "
End If

'GrowerName List
Set lst = Me.lstGrower
If Not IsNull(lst) Then
strWhere = strWhere & "(GrowerName = """ & lst.Column(1) & """) AND "
End If

'VarietyName List
Set lst = Me.lstVariety
If Not IsNull(lst) Then
strWhere = strWhere & "(VarietyName = """ & lst.Column(1) & """) AND "
End If

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

'Similar for other list, combo or text boxes here.

'Did we get anything?
iLen = Len(strWhere) - 5 'Without trailing " AND ".
If iLen < 1 Then
MsgBox "Opps! Must supply at least one criteria"
Else
strWhere = Left$(strWhere, iLen)

'Use it for this form.
Me.Filter = strWhere
Me.FilterOn = True

'Open the report and apply filter.
'DoCmd.OpenReport "rptSowing", acViewPreview, , strWhere
Debug.Print strWhere
DoCmd.OpenForm "frmSowUpdate", acFormDS, , strWhere

End If

Set cbo = Nothing
Set lst = Nothing
Me.lstVariety = Null
Me.lstGrower = Null
End Sub
 
D

Douglas J. Steele

Get rid of the Set keywords. You don't use them with variables, just with
objects.

You still need spaces after those two ORs and after the final AND.
 
D

Dirk Goldgar

niuginikiwi said:
Hi Doug,
I have just put spaces infront of the two ORs and the final AND and
added in the Debug.Printer strWhere line but I am still gettin the
same error message which is Compile Error: Object Required and
highlights the strFrom variable on the line
Set strFrom=Me.txtStartDate.
Click to expand...

strFrom is a simple (non-object) variable -- although it's confusing
that you use the "str" prefix for a variable of type Date. You only use
the "Set" keyword with object variables. Your assignments to strFrom
and strTo should just be

strFrom = Me.txtStartDate
strTo = Me.txtEndDate

No "Set" (though you could use "Let" if you wanted to be old-fashioned).

Note that these further statements:
If IsNull(strFrom) Then
If Not IsNull(strTo) Then 'End date, but no start.
Click to expand...

are meaningless if strFrom and strTo are Date variables, because Date
variables can never be Null. Only Variants can be Null.
 
N

niuginikiwi

Thanks Dirk and Doug,
It's working fine for me at the moment .....
I may need some error trapping code added around the date range fields to
check if FromDate greater than ToDate then a custome msgbox for the user to
chage the date and clear the txtbox and vice versa with the ToDate txtbox
..... but I don't know how.
Can I get some hand on this please?
PS: Below is the onClick Event code for the button.
Thanks

Private Sub btnPreview_Click()
On Error GoTo Err_btnPreview_Click


Dim cbo As ComboBox
Dim lst As ListBox
'Dim strDateField As String 'Name of your criteria date field.
Const conDateFormat = "\#mm\/dd\/yyyy\#"
Dim strWhere As String
Dim iLen As Integer

'CropName Combo
Set cbo = Me.cboCrop
If Not IsNull(cbo) Then
strWhere = strWhere & "(CropName = """ & cbo.Column(1) & """) AND "
End If

'GrowerName List
Set lst = Me.lstGrower
If Not IsNull(lst) Then
strWhere = strWhere & "(GrowerName = """ & lst.Column(1) & """) AND "
End If

'VarietyName List
Set lst = Me.lstVariety
If Not IsNull(lst) Then
strWhere = strWhere & "(VarietyName = """ & lst.Column(1) & """) AND "
End If

'Date field example. Use the format string to add the # delimiters and get
the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([DateSown] >= " & Format(Me.txtStartDate,
conDateFormat) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([DateSown] < " & Format(Me.txtEndDate + 1,
conDateFormat) & ") AND "
End If

'Similar for other list, combo or text boxes here.

'Did we get anything?
iLen = Len(strWhere) - 5 'Without trailing " AND ".
If iLen < 1 Then
MsgBox "Sorry! MUST supply one/more criteria", vbInformation, "Pedro
Says..."
Else
strWhere = Left$(strWhere, iLen)

'Use it for this form.
Me.Filter = strWhere
Me.FilterOn = True

'Open the report and apply filter.
DoCmd.OpenReport "rptSowingList", acViewPreview, , strWhere
End If

Set cbo = Nothing
Set lst = Nothing
'Me.lstVariety = Null
'Me.lstGrower = Null
'Me.cboCrop = Null
'Me.txtStartDate = Null
'Me.txtEndDate = Null

'Code below generated by MS Access Wizzard
Exit_btnPreview_Click:
Exit Sub

Err_btnPreview_Click:
MsgBox "Operation can not be executed, Try again"
Resume Exit_btnPreview_Click

End Sub
 
D

Douglas J Steele

Something along the lines of:

Dim booError As Boolean
Dim strMessage As String

booError = False
If IsNull(FromDate) Then
strMessage = strMessage & _
"You must enter a From date." & vbCrLf
End If
If IsNull(ToDate) Then
strMessage = strMessage & _
"You must enter a To date." & vbCrLf
End If
If Nz(FromDate) >= Nz(ToDate) Then
strMessage = strMessage & _
"From date must be earlier than To date." & vbCrLf
End If

If booError Then
MsgBox strMessage
Else

' put your existing code here

End If

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


niuginikiwi said:
Thanks Dirk and Doug,
It's working fine for me at the moment .....
I may need some error trapping code added around the date range fields to
check if FromDate greater than ToDate then a custome msgbox for the user to
chage the date and clear the txtbox and vice versa with the ToDate txtbox
.... but I don't know how.
Can I get some hand on this please?
PS: Below is the onClick Event code for the button.
Thanks

Private Sub btnPreview_Click()
On Error GoTo Err_btnPreview_Click


Dim cbo As ComboBox
Dim lst As ListBox
'Dim strDateField As String 'Name of your criteria date field.
Const conDateFormat = "\#mm\/dd\/yyyy\#"
Dim strWhere As String
Dim iLen As Integer

'CropName Combo
Set cbo = Me.cboCrop
If Not IsNull(cbo) Then
strWhere = strWhere & "(CropName = """ & cbo.Column(1) & """) AND "
End If

'GrowerName List
Set lst = Me.lstGrower
If Not IsNull(lst) Then
strWhere = strWhere & "(GrowerName = """ & lst.Column(1) & """) AND "
End If

'VarietyName List
Set lst = Me.lstVariety
If Not IsNull(lst) Then
strWhere = strWhere & "(VarietyName = """ & lst.Column(1) & """) AND "
End If

'Date field example. Use the format string to add the # delimiters and get
the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([DateSown] >= " & Format(Me.txtStartDate,
conDateFormat) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([DateSown] < " & Format(Me.txtEndDate + 1,
conDateFormat) & ") AND "
End If

'Similar for other list, combo or text boxes here.

'Did we get anything?
iLen = Len(strWhere) - 5 'Without trailing " AND ".
If iLen < 1 Then
MsgBox "Sorry! MUST supply one/more criteria", vbInformation, "Pedro
Says..."
Else
strWhere = Left$(strWhere, iLen)

'Use it for this form.
Me.Filter = strWhere
Me.FilterOn = True

'Open the report and apply filter.
DoCmd.OpenReport "rptSowingList", acViewPreview, , strWhere
End If

Set cbo = Nothing
Set lst = Nothing
'Me.lstVariety = Null
'Me.lstGrower = Null
'Me.cboCrop = Null
'Me.txtStartDate = Null
'Me.txtEndDate = Null

'Code below generated by MS Access Wizzard
Exit_btnPreview_Click:
Exit Sub

Err_btnPreview_Click:
MsgBox "Operation can not be executed, Try again"
Resume Exit_btnPreview_Click

End Sub
Click to expand...
 
D

Douglas J Steele

Oops, let's try that again:

Dim strMessage As String

strMessage = vbNullString

If IsNull(FromDate) Then
strMessage = strMessage & _
"You must enter a From date." & vbCrLf
End If
If IsNull(ToDate) Then
strMessage = strMessage & _
"You must enter a To date." & vbCrLf
End If
If Nz(FromDate) >= Nz(ToDate) Then
strMessage = strMessage & _
"From date must be earlier than To date." & vbCrLf
End If

If Len(strMessage) > 0 Then
MsgBox strMessage
Else

' put your existing code here

End If


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Douglas J Steele said:
Something along the lines of:

Dim booError As Boolean
Dim strMessage As String

booError = False
If IsNull(FromDate) Then
strMessage = strMessage & _
"You must enter a From date." & vbCrLf
End If
If IsNull(ToDate) Then
strMessage = strMessage & _
"You must enter a To date." & vbCrLf
End If
If Nz(FromDate) >= Nz(ToDate) Then
strMessage = strMessage & _
"From date must be earlier than To date." & vbCrLf
End If

If booError Then
MsgBox strMessage
Else

' put your existing code here

End If

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


niuginikiwi said:
Thanks Dirk and Doug,
It's working fine for me at the moment .....
I may need some error trapping code added around the date range fields to
check if FromDate greater than ToDate then a custome msgbox for the user to
chage the date and clear the txtbox and vice versa with the ToDate txtbox
.... but I don't know how.
Can I get some hand on this please?
PS: Below is the onClick Event code for the button.
Thanks

Private Sub btnPreview_Click()
On Error GoTo Err_btnPreview_Click


Dim cbo As ComboBox
Dim lst As ListBox
'Dim strDateField As String 'Name of your criteria date field.
Const conDateFormat = "\#mm\/dd\/yyyy\#"
Dim strWhere As String
Dim iLen As Integer

'CropName Combo
Set cbo = Me.cboCrop
If Not IsNull(cbo) Then
strWhere = strWhere & "(CropName = """ & cbo.Column(1) & """) AND "
End If

'GrowerName List
Set lst = Me.lstGrower
If Not IsNull(lst) Then
strWhere = strWhere & "(GrowerName = """ & lst.Column(1) & """) AND "
End If

'VarietyName List
Set lst = Me.lstVariety
If Not IsNull(lst) Then
strWhere = strWhere & "(VarietyName = """ & lst.Column(1) & """) AND "
End If

'Date field example. Use the format string to add the # delimiters and get
the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([DateSown] >= " & Format(Me.txtStartDate,
conDateFormat) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([DateSown] < " & Format(Me.txtEndDate + 1,
conDateFormat) & ") AND "
End If

'Similar for other list, combo or text boxes here.

'Did we get anything?
iLen = Len(strWhere) - 5 'Without trailing " AND ".
If iLen < 1 Then
MsgBox "Sorry! MUST supply one/more criteria", vbInformation, "Pedro
Says..."
Else
strWhere = Left$(strWhere, iLen)

'Use it for this form.
Me.Filter = strWhere
Me.FilterOn = True

'Open the report and apply filter.
DoCmd.OpenReport "rptSowingList", acViewPreview, , strWhere
End If

Set cbo = Nothing
Set lst = Nothing
'Me.lstVariety = Null
'Me.lstGrower = Null
'Me.cboCrop = Null
'Me.txtStartDate = Null
'Me.txtEndDate = Null

'Code below generated by MS Access Wizzard
Exit_btnPreview_Click:
Exit Sub

Err_btnPreview_Click:
MsgBox "Operation can not be executed, Try again"
Resume Exit_btnPreview_Click

End Sub
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

error 3464 data type mismatch in criteria expression 0
Multiple search criteria (combo, list & text )? 2
Error Handling; Allen Browne 2
Help Needed: Run combo row source query 1
runtime error 3075 2
Create Report On the Fly 1
Error 3464 Data type mismatch in criteria expression for between 2 dates look up 3
Allen Browne's Search Form - Debugging help needed 2

Top