Issue creating a report from criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form that I have unbound fields to choose a beginning and ending
date to get a report to print After pressing a button. Below is the code I
have written to accomplsh the task. Unfortunately I get a run-time error
'2498': An expression you entered is the wrong data type for one of the
arguments.

____________________________________________________________________
Public Sub MPD_Monthly_Click()

Dim stReportCriter As String
Dim MPDstart As Date
Dim MPDend As Date
Dim getdate As String

MPDstart = [Forms]![MDP Main]![ReportStart]
MPDend = [Forms]![MDP Main]![ReportEnd]
getdate = MPDDate

stReportCriter = getdate & " between " & MPDstart & " And " & MPDend
Me!TempField.Value = stReportCriter
DoCmd.OpenReport [MPD Entry], acViewPreview, , stReportCriter
End Sub

________________________________________________________________
Can someone please give me a hand with what may be causing this? Thank you
in advance.
 
Thanks for your help. Im getting real close. To help me diagnose the
problem Im displaying the WHERE clause back in my form to help me find where
I might be going wrong. That's the reason for this line.

Me!TempField.Value = stReportCriter

Since the changes you helped me with, it returns the value :

between #10/31/2007# And #11/30/2007#

Any Ideas where to do next?

Thanks again.

___________________________________________________________

Allen Browne said:
You need to delimit the literal date values with # when you concatenate them
into the WhereCondition string.

More details:
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.

Scott Harris said:
I have a form that I have unbound fields to choose a beginning and ending
date to get a report to print After pressing a button. Below is the code
I
have written to accomplsh the task. Unfortunately I get a run-time error
'2498': An expression you entered is the wrong data type for one of the
arguments.

____________________________________________________________________
Public Sub MPD_Monthly_Click()

Dim stReportCriter As String
Dim MPDstart As Date
Dim MPDend As Date
Dim getdate As String

MPDstart = [Forms]![MDP Main]![ReportStart]
MPDend = [Forms]![MDP Main]![ReportEnd]
getdate = MPDDate

stReportCriter = getdate & " between " & MPDstart & " And " & MPDend
Me!TempField.Value = stReportCriter
DoCmd.OpenReport [MPD Entry], acViewPreview, , stReportCriter
End Sub

________________________________________________________________
Can someone please give me a hand with what may be causing this? Thank you
in advance.
Click to expand...
Click to expand...
 
The filter string should have a field name as well, e.g.:
[MyDate] between #10/31/2007# And #11/30/2007#

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

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

Scott Harris said:
Thanks for your help. Im getting real close. To help me diagnose the
problem Im displaying the WHERE clause back in my form to help me find
where
I might be going wrong. That's the reason for this line.

Me!TempField.Value = stReportCriter

Since the changes you helped me with, it returns the value :

between #10/31/2007# And #11/30/2007#

Any Ideas where to do next?

Thanks again.

___________________________________________________________

Allen Browne said:
You need to delimit the literal date values with # when you concatenate
them
into the WhereCondition string.

More details:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

Scott Harris said:
I have a form that I have unbound fields to choose a beginning and
ending
date to get a report to print After pressing a button. Below is the
code
I
have written to accomplsh the task. Unfortunately I get a run-time
error
'2498': An expression you entered is the wrong data type for one of the
arguments.

____________________________________________________________________
Public Sub MPD_Monthly_Click()

Dim stReportCriter As String
Dim MPDstart As Date
Dim MPDend As Date
Dim getdate As String

MPDstart = [Forms]![MDP Main]![ReportStart]
MPDend = [Forms]![MDP Main]![ReportEnd]
getdate = MPDDate

stReportCriter = getdate & " between " & MPDstart & " And " & MPDend
Me!TempField.Value = stReportCriter
DoCmd.OpenReport [MPD Entry], acViewPreview, , stReportCriter
End Sub

________________________________________________________________
Can someone please give me a hand with what may be causing this? Thank
you
in advance.
Click to expand...
Click to expand...
Click to expand...
 
I thought I had put that reference in the code. See below.

Public Sub MPD_Monthly_Click()

Dim stReportCriter As String
Dim MPDstart As Date
Dim MPDend As Date
Dim getdate As Date
Const conDateFormat = "\#mm\/dd\/yyyy\#"

getdate = [MPD Entry]!MPDDate <-------------------------

stReportCriter = getdate & " between " & Format(Me!ReportStart,
conDateFormat) & " And " & Format(Me!ReportEnd, conDateFormat)
Me!TempField.Value = stReportCriter
DoCmd.OpenReport [MPD Entry], acViewPreview, , stReportCriter
End Sub

Public Sub ReportEnd_MouseDown(Button As Integer, Shift As Integer, X As
Single, Y As Single)
EndMPDReportCal.Visible = True
EndMPDReportCal.SetFocus
If Not IsNull(FormDate) Then
EndMPDReportCal.Value = ReportEnd.Value
Else
EndMPDReportCal.Value = Date
End If
End Sub

_____________________________________________________________________

Allen Browne said:
The filter string should have a field name as well, e.g.:
[MyDate] between #10/31/2007# And #11/30/2007#

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

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

Scott Harris said:
Thanks for your help. Im getting real close. To help me diagnose the
problem Im displaying the WHERE clause back in my form to help me find
where
I might be going wrong. That's the reason for this line.

Me!TempField.Value = stReportCriter

Since the changes you helped me with, it returns the value :

between #10/31/2007# And #11/30/2007#

Any Ideas where to do next?

Thanks again.

___________________________________________________________

Allen Browne said:
You need to delimit the literal date values with # when you concatenate
them
into the WhereCondition string.

More details:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

I have a form that I have unbound fields to choose a beginning and
ending
date to get a report to print After pressing a button. Below is the
code
I
have written to accomplsh the task. Unfortunately I get a run-time
error
'2498': An expression you entered is the wrong data type for one of the
arguments.

____________________________________________________________________
Public Sub MPD_Monthly_Click()

Dim stReportCriter As String
Dim MPDstart As Date
Dim MPDend As Date
Dim getdate As String

MPDstart = [Forms]![MDP Main]![ReportStart]
MPDend = [Forms]![MDP Main]![ReportEnd]
getdate = MPDDate

stReportCriter = getdate & " between " & MPDstart & " And " & MPDend
Me!TempField.Value = stReportCriter
DoCmd.OpenReport [MPD Entry], acViewPreview, , stReportCriter
End Sub

________________________________________________________________
Can someone please give me a hand with what may be causing this? Thank
you
in advance.
Click to expand...
Click to expand...
Click to expand...
 
You need the field name in the string.

For example, if the field name is "MPDDate", use:
stReportCriter = "MPDDate Between " & ...

For debugging purposes, include:
Debug.Print stReportCriter
to see if it looks right.

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

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

Scott Harris said:
I thought I had put that reference in the code. See below.

Public Sub MPD_Monthly_Click()

Dim stReportCriter As String
Dim MPDstart As Date
Dim MPDend As Date
Dim getdate As Date
Const conDateFormat = "\#mm\/dd\/yyyy\#"

getdate = [MPD Entry]!MPDDate <-------------------------

stReportCriter = getdate & " between " & Format(Me!ReportStart,
conDateFormat) & " And " & Format(Me!ReportEnd, conDateFormat)
Me!TempField.Value = stReportCriter
DoCmd.OpenReport [MPD Entry], acViewPreview, , stReportCriter
End Sub

Public Sub ReportEnd_MouseDown(Button As Integer, Shift As Integer, X As
Single, Y As Single)
EndMPDReportCal.Visible = True
EndMPDReportCal.SetFocus
If Not IsNull(FormDate) Then
EndMPDReportCal.Value = ReportEnd.Value
Else
EndMPDReportCal.Value = Date
End If
End Sub

_____________________________________________________________________

Allen Browne said:
The filter string should have a field name as well, e.g.:
[MyDate] between #10/31/2007# And #11/30/2007#

Scott Harris said:
Thanks for your help. Im getting real close. To help me diagnose the
problem Im displaying the WHERE clause back in my form to help me find
where
I might be going wrong. That's the reason for this line.

Me!TempField.Value = stReportCriter

Since the changes you helped me with, it returns the value :

between #10/31/2007# And #11/30/2007#

Any Ideas where to do next?

Thanks again.

___________________________________________________________

:

You need to delimit the literal date values with # when you
concatenate
them
into the WhereCondition string.

More details:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

message
I have a form that I have unbound fields to choose a beginning and
ending
date to get a report to print After pressing a button. Below is the
code
I
have written to accomplsh the task. Unfortunately I get a run-time
error
'2498': An expression you entered is the wrong data type for one of
the
arguments.

____________________________________________________________________
Public Sub MPD_Monthly_Click()

Dim stReportCriter As String
Dim MPDstart As Date
Dim MPDend As Date
Dim getdate As String

MPDstart = [Forms]![MDP Main]![ReportStart]
MPDend = [Forms]![MDP Main]![ReportEnd]
getdate = MPDDate

stReportCriter = getdate & " between " & MPDstart & " And " & MPDend
Me!TempField.Value = stReportCriter
DoCmd.OpenReport [MPD Entry], acViewPreview, , stReportCriter
End Sub
Click to expand...
Click to expand...
Click to expand...
 
Back
Top