Help with runtime error 3075

[croth68 via AccessMonster.com]

I have a form that allows me to search my table using a number of key words,
I also included a button where I could take the search results and put them
in a report format for easy printing. Initially the date would not print so I
added this code strCriteria = "[Date Out] Between # " & Me![Text48] & " # And
# " & Me![Text50] & "#" courtesy of another user. It works fine if the date
field is filled in, however if the date field is not filled in I get a
runtime error 3075 with the DoCmd.OpenReport strReportName, acViewPreview, ,
strCriteria highlighted. Any help you can provide is greatly appreciated.

Thanks

Private Sub Command61_Click()

Dim strReportName As String
Dim strCriteria As String

If NewRecord Then
MsgBox "This record contains no data." _
, vbInformation, "Invalid Action"
Exit Sub
Else

strReportName = "Table1"
strCriteria = "[Record Number]= " & Me![Record Number]
strCriteria = "[Date Out] Between # " & Me![Text48] & " # And # " &
Me![Text50] & "#"


DoCmd.OpenReport strReportName, acViewPreview, , strCriteria

End If
End Sub

 

[suguy]

You could try setting your date variables to a date type..
dim mydate as date

date = [Text48].value

or evaluate weather the string is of null value..

 

[croth68 via AccessMonster.com]

Thanks for replying, I tried setting the date variables but it gave me an
error 13, unless I missed the boat when i wrote the code which is completely
possible. How do I evaluate weather the string is null or not, this sounds
like it may help as I am only having the problem when the date fields are
empty.

You could try setting your date variables to a date type..
dim mydate as date

date = [Text48].value

or evaluate weather the string is of null value..

I have a form that allows me to search my table using a number of key words,
I also included a button where I could take the search results and put them

[quoted text clipped - 27 lines]

End If
End Sub

 

[John Spencer]

I'm not sure if you want to select records based
-- on record number and date range (at the same time)
OR
-- on record number only if date range is blank
OR
-- on date range only if date range has a value

If you want to only use one or the other criteria then use the code below.
If you want to use both record number and date range (if both are available
otherwise use only record number) then a slight modification to the code is
needed.

strCriteria = strCriteria & " AND " & _
"[Date Out] Between # " & Me![Text48] & _
" # And # " & Me![Text50] & "#"

Private Sub Command61_Click()
Dim strReportName As String
Dim strCriteria As String

If NewRecord Then
MsgBox "This record contains no data." _
, vbInformation, "Invalid Action"
Exit Sub
Else

strReportName = "Table1"
strCriteria = "[Record Number]= " & Me![Record Number]

'If Dates are entered then ignore the record number
'and use the date range
If IsDate(me.Text48) and IsDate(Me.Text50) Then
strCriteria & _
"[Date Out] Between # " & Me![Text48] & _
" # And # " & Me![Text50] & "#"
End If

DoCmd.OpenReport strReportName, acViewPreview, , strCriteria

End If

End Sub

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[croth68 via AccessMonster.com]

John,

Your the greatest, I ended up using the second one, and it works awesome. The
code example also helped me to figure out the next issue which was it would
only show one record on the form, and that was record one, unless the date
field was filled in then it would show all the records that met the date
criteria. The second set of code helped me to figure out to use a If Not
IsNull Then statement to show all the records in the report that met any of
the criteria. The next thing I have to figure out is how to use check boxes
to only include certain fields in the report instead of all the fields.

I'm not sure if you want to select records based
-- on record number and date range (at the same time)
OR
-- on record number only if date range is blank
OR
-- on date range only if date range has a value

If you want to only use one or the other criteria then use the code below.
If you want to use both record number and date range (if both are available
otherwise use only record number) then a slight modification to the code is
needed.

strCriteria = strCriteria & " AND " & _
"[Date Out] Between # " & Me![Text48] & _
" # And # " & Me![Text50] & "#"

Private Sub Command61_Click()
Dim strReportName As String
Dim strCriteria As String

If NewRecord Then
MsgBox "This record contains no data." _
, vbInformation, "Invalid Action"
Exit Sub
Else

strReportName = "Table1"
strCriteria = "[Record Number]= " & Me![Record Number]

'If Dates are entered then ignore the record number
'and use the date range
If IsDate(me.Text48) and IsDate(Me.Text50) Then
strCriteria & _
"[Date Out] Between # " & Me![Text48] & _
" # And # " & Me![Text50] & "#"
End If

DoCmd.OpenReport strReportName, acViewPreview, , strCriteria

End If

End Sub

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

I have a form that allows me to search my table using a number of key words,
I also included a button where I could take the search results and put them

[quoted text clipped - 27 lines]

End If
End Sub