nulad
asked on
Access 2003 Open Report using unbound form
I have an Access 2003 linked table. The table is linked to a SQL Server 2005 database table. I have created an Access report using the linked table.
I created a unbound form with one unbound text box field. The user is to put a date in the form's unbound text field ("txt_AppDate"). The form's "txt_AppDate" field is formatted using the "Short Date" format.
After a date is entered in the "txt_AppDate" field, the user selects the "view" command button. I want the report to open showing only the records that have a date greater than or equal to the date entered in the form's "txt_AppDate" field.
When I attempted to do this, using the below code in the command button, II receive a "Type Mismatch" error message. What is wrong?
I created a unbound form with one unbound text box field. The user is to put a date in the form's unbound text field ("txt_AppDate"). The form's "txt_AppDate" field is formatted using the "Short Date" format.
After a date is entered in the "txt_AppDate" field, the user selects the "view" command button. I want the report to open showing only the records that have a date greater than or equal to the date entered in the form's "txt_AppDate" field.
When I attempted to do this, using the below code in the command button, II receive a "Type Mismatch" error message. What is wrong?
Private Sub cmd_PrintOnlApps_Click()
On Error GoTo Err_cmd_PrintOnlApps_Click
'Open r_Apps_ONL report using criteria from an unbound Form field
'The User is to enter a date in the unbound form field txt_AppDate
Dim stRptName As String 'Report to open
Dim dtRptField As Date 'The Report's date field
Dim dtFrmDate As Date 'The Form's field containing the user's input date
stRptName = "r_Apps-ONL"
dtRptField = "Reports!" & stRptName & "!date"
dtFrmDate = CDate(Forms!f_PrintAppsFromDate!txt_AppDate)
DoCmd.OpenReport stRptName, acViewPreview, , dtRptField & " >= " & dtFrmDate
Exit_cmd_PrintOnlApps_Click:
Exit Sub
Err_cmd_PrintOnlApps_Click:
MsgBox Err.Description
Resume Exit_cmd_PrintOnlApps_Click
End Sub
On the other hand I really don't know why this is so complicated.
For example:
If the Report is based on the linked table, and the Reports Date Filed is called "OrderDate"
...then you can just do this:
DoCmd.OpenReport stRptName, acViewPreview, , "OrderDate=" & dtRptField & " >= " & dtFrmDate
I really don't see the need to go through all the trouble of creating a variable (that we have to troubleshoot anyway), if the name of the date field won't change.
JeffCoachman
For example:
If the Report is based on the linked table, and the Reports Date Filed is called "OrderDate"
...then you can just do this:
DoCmd.OpenReport stRptName, acViewPreview, , "OrderDate=" & dtRptField & " >= " & dtFrmDate
I really don't see the need to go through all the trouble of creating a variable (that we have to troubleshoot anyway), if the name of the date field won't change.
JeffCoachman
Line 12 is assigning a string value to a Date variable - that's your type mismatch.
If the field in the report's recordsource on which you want to filter is literally named date, you can stop using dtRptField altogether and change line 15:
(was) DoCmd.OpenReport stRptName, acViewPreview, , dtRptField & " >= " & dtFrmDate
(to) DoCmd.OpenReport strRptName, acViewPreview, , "[date] >= " & dtFrmDate
Remember, you want the fourth argument to read like a WHERE clause added to the report's underlying query / table. It shouldn't have a reference to the report's name or even a control on the report -- it needs the *field name*.
Warnings -- [date] is a dangerous name to use for a field because you and Access may disagree on when you mean VBA's Date function
-- You should format the date entered into a format SQL understands --
USian: Format(dtFrmDate, "\#mm\/dd\/yyyy\#")
or
international: Format(dtFrmDate, "\#ddmmmyyyy\#")
HTH,
pT
If the field in the report's recordsource on which you want to filter is literally named date, you can stop using dtRptField altogether and change line 15:
(was) DoCmd.OpenReport stRptName, acViewPreview, , dtRptField & " >= " & dtFrmDate
(to) DoCmd.OpenReport strRptName, acViewPreview, , "[date] >= " & dtFrmDate
Remember, you want the fourth argument to read like a WHERE clause added to the report's underlying query / table. It shouldn't have a reference to the report's name or even a control on the report -- it needs the *field name*.
Warnings -- [date] is a dangerous name to use for a field because you and Access may disagree on when you mean VBA's Date function
-- You should format the date entered into a format SQL understands --
USian: Format(dtFrmDate, "\#mm\/dd\/yyyy\#")
or
international: Format(dtFrmDate, "\#ddmmmyyyy\#")
HTH,
pT
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thanks,
"Always keep it simle"
I always say.
;-)
You could have really split the points, as pteranodon72 posted some really helpfull info as well.
You can click the "Request Attention" button to change this if you like.
;-)
Jeff
"Always keep it simle"
I always say.
;-)
You could have really split the points, as pteranodon72 posted some really helpfull info as well.
You can click the "Request Attention" button to change this if you like.
;-)
Jeff
1. If you type the Criteria directly into the WHERE claues, does it work?
2. If you put this before the Open Report Command, what does it say:
Msgbox dtRptField
3. What is the last part of the Criteria?
If it is a Field or Control named "Date" then this is a bad idea.
"Date" is a reserved word in Access/SQL.
Try changing it to something a bit more descriptive:
SaleDate, OrderDate, SneezeDate...
Keep us posted
JeffCoachman