Forms to input parameters in ADP

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

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
 
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.
 
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.


Sylvain Lafontaine said:
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


JohnK said:
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
Click to expand...
Click to expand...
 
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


JohnK said:
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.


Sylvain Lafontaine said:
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


JohnK said:
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
Click to expand...
Click to expand...
Click to expand...
 
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

Sylvain Lafontaine said:
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


JohnK said:
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.


Sylvain Lafontaine said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top