Hi everybody.

I'm working on this access DB for a customer - I don't do database work, but essentially, it's fallen on me to get this rolling. We changed from a 97 to a 2007 database, so I took access 2003 and changed the files to 2.0, then imported them to 2003 files. most things work (there are several computers running different databases), this one system doesn't work for beans.

I get this data type mismatch in criteria expression on a boatload of functions.

essentially, the main screen has a bunch of functions; show the roster, print reports, etc. Most of the buttons you click all fail with that data type error.

I believe this is the code, though the buttons are named differently in the actual DB itself. I searched to try to find it, but no luck. Maybe I'm missing something? though there are similar names.

here's the code.

Any help is appreciated in advance; I don't want to have to call in a specialist for this, and since its on so many functions, I think its probably something easy.

Thanks again in advance.

Option Compare Database 'Use database order for string comparisons
Option Explicit

Private Sub btnExit_Click()
On Error GoTo Err_btnExit_Click

' Orig was quit - exits access - restored 4/25/97
' Chg to close - closes window, only

DoCmd.Quit

Exit_btnExit_Click:
Exit Sub

Err_btnExit_Click:
MsgBox Error$
Resume Exit_btnExit_Click

End Sub

Private Sub Button10_Click()
On Error GoTo Err_Button10_Click

Dim DocName As String
' Report by DOE
DocName = "RosterMonR_DOE"
DoCmd.ShowToolbar "Print Preview", A_TOOLBAR_YES
DoCmd.OpenReport DocName, A_PREVIEW

Exit_Button10_Click:
Exit Sub

Err_Button10_Click:
MsgBox Error$
Resume Exit_Button10_Click


End Sub

Private Sub Button14_Click()
On Error GoTo Err_Button14_Click

Dim DocName As String
Dim LinkCriteria As String

Me.Visible = False
DocName = "MSB_Table_Edit"
DoCmd.OpenForm DocName, , , LinkCriteria
Exit Sub
Exit_Button14_Click:
DoCmd.Close
Forms![MainSwitchBoard].Visible = True
Exit Sub

Err_Button14_Click:
MsgBox Error$
Resume Exit_Button14_Click

End Sub

Private Sub Button15_Click()
On Error GoTo Err_Button15_Click

Dim DocName As String

DocName = "Test Report"
DoCmd.ShowToolbar "Print Preview", A_TOOLBAR_YES
DoCmd.OpenReport DocName, A_PREVIEW

Exit_Button15_Click:
Exit Sub

Err_Button15_Click:
MsgBox Error$
Resume Exit_Button15_Click

End Sub

Private Sub Button16_Click()
On Error GoTo Err_Button16_Click

Dim DocName As String

DocName = "Admissions"
DoCmd.ShowToolbar "Print Preview", A_TOOLBAR_YES
DoCmd.OpenReport DocName, A_PREVIEW

Exit_Button16_Click:
Exit Sub

Err_Button16_Click:
MsgBox Error$
Resume Exit_Button16_Click

End Sub

Private Sub Button17_Click()
On Error GoTo Err_Button17_Click

Dim DocName As String

DocName = "Departures"
DoCmd.ShowToolbar "Print Preview", A_TOOLBAR_YES
DoCmd.OpenReport DocName, A_PREVIEW

Exit_Button17_Click:
Exit Sub

Err_Button17_Click:
MsgBox Error$
Resume Exit_Button17_Click

End Sub

Private Sub Button18_Click()
On Error GoTo Err_Button18_Click

Dim DocName As String
' Make table for network transfer
' At 11/01/98 - Configured for Shennum Client, ECD Server
DoCmd.SetWarnings False
DoCmd.OpenQuery "D15_Combo_Zap"
DoCmd.OpenQuery "D15MT_Q_ECD"

DocName = "Depart_15R_Msg"
DoCmd.ShowToolbar "Print Preview", A_TOOLBAR_YES
DoCmd.OpenReport DocName, A_PREVIEW


' Dim DocName As String

' Dim LinkCriteria As String
' Me.visible = False
' DocName = "CaddsT_NoLink"
' DoCmd OpenTable DocName
' DoCmd ShowToolbar "Print Preview", A_TOOLBAR_YES
' DoCmd ShowToolbar "Table Datasheet", A_TOOLBAR_NO
' DocName = "TestReport"
' DoCmd OpenReport DocName, A_PREVIEW

Exit_Button18_Click:
Exit Sub

Err_Button18_Click:
MsgBox Error$
Resume Exit_Button18_Click

End Sub

Private Sub Button19_Click()

' 15 Day Departures Report

On Error GoTo Err_Button19_Click

Dim DocName As String

DocName = "Depart_15R"
DoCmd.ShowToolbar "Print Preview", A_TOOLBAR_YES
DoCmd.OpenReport DocName, A_PREVIEW

Exit_Button19_Click:
Exit Sub

Err_Button19_Click:
MsgBox Error$
Resume Exit_Button19_Click


End Sub

Private Sub Button2_Click()
On Error GoTo Err_Button2_Click

Dim DocName As String
Dim LinkCriteria As String

Me.Visible = False
DocName = "AdmDataEntry"
DoCmd.OpenForm DocName, A_NORMAL, , LinkCriteria, A_ADD

Exit_Button2_Click:
Exit Sub

Err_Button2_Click:
MsgBox Error$
Resume Exit_Button2_Click

End Sub

Private Sub Button20_Click()
On Error GoTo Err_Button20_Click

Dim DocName As String
Dim LinkCriteria As String

Me.Visible = False
DocName = "ProgressNotes"
DoCmd.ShowToolbar "Print Preview", A_TOOLBAR_NO
DoCmd.OpenForm DocName, , , LinkCriteria

Exit_Button20_Click:
Exit Sub

Err_Button20_Click:
MsgBox Error$
Resume Exit_Button20_Click

End Sub

Private Sub Button24_Click()

On Error GoTo Err_Button24_Click

Dim DocName As String

DocName = "Yearly_LOS_Fund_SumR"
DoCmd.ShowToolbar "Print Preview", A_TOOLBAR_YES
DoCmd.OpenReport DocName, A_PREVIEW

Exit_Button24_Click:
Exit Sub

Err_Button24_Click:
MsgBox Error$
Resume Exit_Button24_Click

End Sub

Private Sub Button25_Click()

On Error GoTo Err_Button25_Click

Dim DocName As String

DocName = "Yearly by LOS"
DoCmd.ShowToolbar "Print Preview", A_TOOLBAR_YES
DoCmd.OpenReport DocName, A_PREVIEW

Exit_Button25_Click:
Exit Sub

Err_Button25_Click:
MsgBox Error$
Resume Exit_Button25_Click

End Sub

Private Sub Button26_Click()
On Error GoTo Err_Button26_Click

Dim DocName As String

DocName = "Monthly ID_Num"
DoCmd.ShowToolbar "Print Preview", A_TOOLBAR_YES
DoCmd.OpenReport DocName, A_PREVIEW

Exit_Button26_Click:
Exit Sub

Err_Button26_Click:
MsgBox Error$
Resume Exit_Button26_Click

End Sub

Private Sub Button27_Click()
On Error GoTo Err_Button27_Click

Dim DocName As String

DocName = "Yearly ID_Num"
DoCmd.ShowToolbar "Print Preview", A_TOOLBAR_YES
DoCmd.OpenReport DocName, A_PREVIEW

Exit_Button27_Click:
Exit Sub

Err_Button27_Click:
MsgBox Error$
Resume Exit_Button27_Click

End Sub

Private Sub Button28_Click()

On Error GoTo Err_Button28_Click

Dim DocName As String

DocName = "Monthly PD"
DoCmd.ShowToolbar "Print Preview", A_TOOLBAR_YES
DoCmd.OpenReport DocName, A_PREVIEW

Exit_Button28_Click:
Exit Sub

Err_Button28_Click:
MsgBox Error$
Resume Exit_Button28_Click

End Sub

Private Sub Button3_Click()
On Error GoTo Err_Button3_Click

Dim DocName As String
Dim LinkCriteria As String

Me.Visible = False
DocName = "ResDataEntry"
DoCmd.OpenForm DocName, , , LinkCriteria

Exit_Button3_Click:
Exit Sub

Err_Button3_Click:
MsgBox Error$
Resume Exit_Button3_Click

End Sub

Private Sub Button30_Click()

On Error GoTo Err_Button30_Click

Dim DocName As String

DocName = "Monthly PD ID_Num"
DoCmd.ShowToolbar "Print Preview", A_TOOLBAR_YES
DoCmd.OpenReport DocName, A_PREVIEW

Exit_Button30_Click:
Exit Sub

Err_Button30_Click:
MsgBox Error$
Resume Exit_Button30_Click

End Sub

Private Sub Button31_Click()
On Error GoTo Err_Button31_Click

Dim DocName As String

DocName = "MonFunding Ledger"
DoCmd.ShowToolbar "Print Preview", A_TOOLBAR_YES
DoCmd.OpenReport DocName, A_PREVIEW

Exit_Button31_Click:
Exit Sub

Err_Button31_Click:
MsgBox Error$
Resume Exit_Button31_Click

End Sub

Private Sub Button4_Click()
On Error GoTo Err_Button4_Click

Dim DocName As String
Dim LinkCriteria As String

Me.Visible = False
DocName = "RecordSearch"
DoCmd.OpenForm DocName, , , LinkCriteria

Exit_Button4_Click:
Exit Sub

Err_Button4_Click:
MsgBox Error$
Resume Exit_Button4_Click

End Sub

Private Sub Button4_Exit(Cancel As Integer)
DoCmd.OpenForm "MainSwitchBoard"
End Sub

Private Sub Button8_Click()
On Error GoTo Err_Button8_Click

Dim DocName As String

DocName = "Monthly"
DoCmd.ShowToolbar "Print Preview", A_TOOLBAR_YES
DoCmd.OpenReport DocName, A_PREVIEW

Exit_Button8_Click:
Exit Sub

Err_Button8_Click:
MsgBox Error$
Resume Exit_Button8_Click

End Sub

Private Sub Button9_Click()
On Error GoTo Err_Button9_Click

Dim DocName As String

DocName = "Yearly"
DoCmd.ShowToolbar "Print Preview", A_TOOLBAR_YES
DoCmd.OpenReport DocName, A_PREVIEW

Exit_Button9_Click:
Exit Sub

Err_Button9_Click:
MsgBox Error$
Resume Exit_Button9_Click


End Sub

Private Sub Form_Activate()
DoCmd.ShowToolbar "Print Preview", A_TOOLBAR_NO
Forms![MainSwitchBoard]![RDX].Requery
Forms![MainSwitchBoard]![Today].Requery
End Sub

Private Sub Form_Unload(Cancel As Integer)

' Update 15 Day report table
' DoCmd SetWarnings False
' DoCmd OpenQuery "D15MT_Q_ECD"

' Access2
DoCmd.Quit
' Access97
' DoCmd.Quit

End Sub

I don't use either Access 2000 or 2007. I use 2003. One thing that made me suspiscious was this line:

DoCmd.OpenReport DocName, A_PREVIEW

which recurs several times in the code above. In access 2003, the constant isn't named "A_Preview", it would more likely be:

DoCmd.OpenReport DocName, Access.acViewPreview

Also, when the error comes up, if you are given a choice between "End" and "Break" choose "Break", hopefully this will take you to the exact line of code that is failing. Alternatively, try setting a breakpoint in the code (click the lefthand margin to create a brown dot) and then use f8 to step through the code as it runs from the breakpoint. This too could help you pin down which line of code is throwing the error.

I can click 'OK', nothing else.

As I mentioned, this is going up from a 97 to 2007 - client computers are all in office 2007 now.

I will change all the refs to what you mentioned, and see if that changes anything.

Thanks, and any further suggestions would be greatly appreciated.

Actually, you don't need to preface with Access.

DoCmd.OpenReport DocName, acViewPreview

will do just fine.

There's another constant that looks suspicious too - I didn't notice it because I don't work much with toolbars:

DoCmd.ShowToolbar "Print Preview", A_TOOLBAR_YES

this should probably be:

acToolbarYes

Not to mention this one:

DoCmd.OpenForm DocName, A_NORMAL, , LinkCriteria, A_ADD

Not to mention this one:

DoCmd.OpenForm DocName, A_NORMAL, , LinkCriteria, A_ADD

Looks like whoever built this created their own constants instead of using the built-in ones (for what reason - who knows).

I don't know why he coded his own items -- essentially, this is 1 site for a company with multiple sites, and this database was tweaked.

so I tried modifying thusly:

Dim DocName As String
' Report by DOE
DocName = "RosterMonR_DOE"
Rem DoCmd.ShowToolbar "Print Preview", A_TOOLBAR_YES
DoCmd.OpenReport DocName, acViewPreview

I know, apparently noone adds 'rem' statements anymore, but I figured it was worthwhile to try.

Essentially, there are a bunch of buttons.

monthly, yearly, roster, partial name, multiple ID#s.. all of these are broken / lacking functionality. Data type mismatch.

It looks like each button is just named button, contains a subroutine, and for whatever reason they fail.

Anybody who can lend a hand, all thanks would be to you.

i had a similar problem in A2007

i traced to a criteria test in a query that was testing a text field with

is not null and <>"", which worked fine in earlier verisons, but not in A2007

I would try opening the underlying query to the report, as its obviously nothing to do directly with the docmd (or any) of the statements. You are likely to get a more informative message that way