Forms to input parameters in ADP

[Guest]

I am having difficulty getting it set up to use a form to pass date
parameters back to a stored procedure in my adp. This is my first adp so I
am trying to get used to differences between mdb and adp.

Anyway, I searched and found code like this:

Private Sub Report_Open(Cancel As Integer)
Dim strRecordSource As String
strRecordSource = "Exec [lmprodmerge] '5/16/06','5/16/06'"
Me.RecordSource = strRecordSource

This works perfectly (set on open for report). The problems start when I
try to substitute Forms!frmReports.fromdate (and yhe corresponding end date
in the other spot). I get an error stating problems converting varchar to
datetime. (the form fields are set to short date). I see the record source
is converted to string in the dim but can't figure out what to do with the
dates.

Thanks for any help or direction.

John

 

[Sylvain Lafontaine]

Maybe a regional/localization problem.

Make sure that you have quoted the date(s) between single quote ' and if
this doesn't work, show us the string value of strRecordSource before using
it.

 

[Guest]

What I have been trying is this:
Private Sub Report_Open(Cancel As Integer)
Dim strRecordSource As String
strRecordSource = "Exec [lmprodmerge]
'Forms!frmReports.fromdate','Forms!frmReports.todate'"
Me.RecordSource = strRecordSource

I get the nvarchar conversion to date error. The version below with the
dates in place of the Forms entry works just fine. I just cannot seem to get
it to recognize the form entry as a date. The field is formatted as date.

I also use the dates on the report. I was using the forms as I could not
think of how an input box field could be mapped to appear on a report. I
need a simple method for users to pull reports based on dates as the database
is meant to track amounts of work done on an hourly basis.

Maybe a regional/localization problem.

Make sure that you have quoted the date(s) between single quote ' and if
this doesn't work, show us the string value of strRecordSource before using
it.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

I am having difficulty getting it set up to use a form to pass date
parameters back to a stored procedure in my adp. This is my first adp so
I
am trying to get used to differences between mdb and adp.

Anyway, I searched and found code like this:

Private Sub Report_Open(Cancel As Integer)
Dim strRecordSource As String
strRecordSource = "Exec [lmprodmerge] '5/16/06','5/16/06'"
Me.RecordSource = strRecordSource

This works perfectly (set on open for report). The problems start when I
try to substitute Forms!frmReports.fromdate (and yhe corresponding end
date
in the other spot). I get an error stating problems converting varchar to
datetime. (the form fields are set to short date). I see the record
source
is converted to string in the dim but can't figure out what to do with the
dates.

Thanks for any help or direction.

John

 

[Sylvain Lafontaine]

No, you cannot send form instructions like « Forms!frmReports.fromdate » to
SQL Server because it doesn't know what this mean at the other limit of the
wire. Instead, use something like the following:

strRecordSource = "Exec [lmprodmerge] '" & Forms!frmReports.fromdate &
"','" & Forms!frmReports.todate & "'"

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

What I have been trying is this:
Private Sub Report_Open(Cancel As Integer)
Dim strRecordSource As String
strRecordSource = "Exec [lmprodmerge]
'Forms!frmReports.fromdate','Forms!frmReports.todate'"
Me.RecordSource = strRecordSource

I get the nvarchar conversion to date error. The version below with the
dates in place of the Forms entry works just fine. I just cannot seem to
get
it to recognize the form entry as a date. The field is formatted as date.

I also use the dates on the report. I was using the forms as I could not
think of how an input box field could be mapped to appear on a report. I
need a simple method for users to pull reports based on dates as the
database
is meant to track amounts of work done on an hourly basis.

Maybe a regional/localization problem.

Make sure that you have quoted the date(s) between single quote ' and if
this doesn't work, show us the string value of strRecordSource before
using
it.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

I am having difficulty getting it set up to use a form to pass date
parameters back to a stored procedure in my adp. This is my first adp
so
I
am trying to get used to differences between mdb and adp.

Anyway, I searched and found code like this:

Private Sub Report_Open(Cancel As Integer)
Dim strRecordSource As String
strRecordSource = "Exec [lmprodmerge] '5/16/06','5/16/06'"
Me.RecordSource = strRecordSource

This works perfectly (set on open for report). The problems start when
I
try to substitute Forms!frmReports.fromdate (and yhe corresponding end
date
in the other spot). I get an error stating problems converting varchar
to
datetime. (the form fields are set to short date). I see the record
source
is converted to string in the dim but can't figure out what to do with
the
dates.

Thanks for any help or direction.

John

 

[Guest]

Thank you so very much for your advice and time! The solution was perfect.
I figured I had the syntax wrong on my line. I see the difference in yours
and have learned from it. Thanks again!

John

No, you cannot send form instructions like « Forms!frmReports.fromdate » to
SQL Server because it doesn't know what this mean at the other limit of the
wire. Instead, use something like the following:

strRecordSource = "Exec [lmprodmerge] '" & Forms!frmReports.fromdate &
"','" & Forms!frmReports.todate & "'"

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

What I have been trying is this:
Private Sub Report_Open(Cancel As Integer)
Dim strRecordSource As String
strRecordSource = "Exec [lmprodmerge]
'Forms!frmReports.fromdate','Forms!frmReports.todate'"
Me.RecordSource = strRecordSource

I get the nvarchar conversion to date error. The version below with the
dates in place of the Forms entry works just fine. I just cannot seem to
get
it to recognize the form entry as a date. The field is formatted as date.

I also use the dates on the report. I was using the forms as I could not
think of how an input box field could be mapped to appear on a report. I
need a simple method for users to pull reports based on dates as the
database
is meant to track amounts of work done on an hourly basis.

Maybe a regional/localization problem.

Make sure that you have quoted the date(s) between single quote ' and if
this doesn't work, show us the string value of strRecordSource before
using
it.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


I am having difficulty getting it set up to use a form to pass date
parameters back to a stored procedure in my adp. This is my first adp
so
I
am trying to get used to differences between mdb and adp.

Anyway, I searched and found code like this:

Private Sub Report_Open(Cancel As Integer)
Dim strRecordSource As String
strRecordSource = "Exec [lmprodmerge] '5/16/06','5/16/06'"
Me.RecordSource = strRecordSource

This works perfectly (set on open for report). The problems start when
I
try to substitute Forms!frmReports.fromdate (and yhe corresponding end
date
in the other spot). I get an error stating problems converting varchar
to
datetime. (the form fields are set to short date). I see the record
source
is converted to string in the dim but can't figure out what to do with
the
dates.

Thanks for any help or direction.

John