Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Access 2003 Open Report using unbound form

Posted on 2009-05-18
5
Medium Priority
?
815 Views
Last Modified: 2013-11-28
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?
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

Open in new window

0
Comment
Question by:nulad
  • 4
5 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24415607
Frirst things first.

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
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24415691
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

0
 
LVL 14

Expert Comment

by:pteranodon72
ID: 24415776
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
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 2000 total points
ID: 24415893
Here is a very simple example without all the Pomp and Circumstance

;-)

JeffCoachman
db1.mdb
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24416311
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
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question