Issue creating a report from criteria

[Scott Harris]

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.

 

[Allen Browne]

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]

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

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

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

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.

 

[Allen Browne]

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.

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

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.

 

[Scott Harris]

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

_____________________________________________________________________

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.

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

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.

 

[Allen Browne]

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.

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

_____________________________________________________________________

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

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