Type Mismatch

[speakers_86]

I am getting this error, even though everything should be considered string. I suppose it is probably a problem with quotes. The error is at SER= in the public function.

Public Function FullSer()
Dim SER As String
SER = "000-" And Suffix And "Q" And vyear
MsgBox FullSer
End Function


'Private Sub Form_Current()
'Me.Combo2.DefaultValue.ItemData (0)
'End Sub

Private Sub Text5_Click()
'Setfocus so that the cursor doesnt stay on the control
Me.Combo2.SetFocus
'Determine the year for purposes of determining the final ser number
Dim vyear As String
If Nz(Me.Combo2, Right(Date, 4)) = Right(Date, 4) Then
    vyear = ""
    Else
    vyear = "(" & Right(Me.Combo2, 2) & ")"
End If
'If necessary, creates a new series.  One series for each year.
If IsNull(DLookup("[serseries]", "qryQuarterlySerCheck", "[SeriesDate] =" & [Combo2])) Then
    Call SeriesCheck
End If
'Determine if new suffix needs to be created
Suffix = "01"
Call FullSer
If IsNull(DLookup("[fullser]", "qryser2", "[fullser]='" & "ser" & "'")) Then
    Call SuffixCheck
End If
'Open frmSER
'DoCmd.OpenForm "frmser", , , "[fullser]= '000-01Q'" & vyear
DoCmd.OpenForm "frmser", , , "[fullser]= '" & "ser" & "'"
'Sets combo 144 to only quarterly
Call ReportSQL
[Forms]![frmser].TabCtl176.Pages.Item("page186").SetFocus

End Sub

 

[stopher]

I am getting this error, even though everything should be considered string. I suppose it is probably a problem with quotes. The error is at SER= in the public function.

SER = "000-" And Suffix And "Q" And vyear

I don't see how this line makes any sense. I suspect because of the AND it is trying to interpret the statement as a boolean.

Are you trying to concatenate? In which case try:

SER = "000-" & Suffix & "Q" & vyear

hth
Chris

 

[speakers_86]

Thats exactly what I was doing. Thanks.

 

[speakers_86]

If I put a msgbox FullSer right after that line, the message box is empty.

edit- nevermind. It's the simple stuff...

edit- What am I doing wrong with the Suffix variable? I simplified it to this

Public Function FullSer()
Dim SER As String
SER = Suffix
MsgBox SER
End Function

and it returns nothing.

I think I got it. I changed it to

Public Suffix As String

and it works. I don't usually use public, or functions. I am trying to now to become a better programmer. It seems to make it easier when things need to get used over and over, too.

 

[stopher]

It returns nothing because the code does not know anything about Suffix. I see you have defined Suffix in the Text5_Click subroutine. However, that variable is private to that subroutine i.e. it can't be seen by other other code outside that subroutine.

If you want to make Suffix available to the FullSer function then pass it as an argument:

Public Function FullSer(Suffix as string)
Dim SER As String
SER = Suffix
MsgBox SER
End Function

Then call it with:

Call FullSer(Suffix)


By the way, functions are generally used to return values. You could just used a Sub instead since you are not interested in returning a value.

hth
Chris

 

[stopher]

I think I got it. I changed it to

Public Suffix As String

and it works. I don't usually use public, or functions. I am trying to now to become a better programmer. It seems to make it easier when things need to get used over and over, too.

Yes there's more than one way to skin a cat.

Note though that you should only make variables public if you truly want they to be public. The thing is, you make a variable public in good faith, but somewhere down the line you write some code using the same variable name (without realising you were already using it as a public variable. What's worse is you might not get any errors, instead, different bits of code are using that variable to their own end.

Chris

 

[speakers_86]

I don't know if you saw my last edit, but I changed it to Public Suffix as String. Does that have the same effect? Isn't the variable available everywhere now?

So my public function FullSER, I should change to Public Sub? Like I said, I usually keep all of my code inside the private subs, so this is new to me.

 

[stopher]

I don't know if you saw my last edit, but I changed it to Public Suffix as String. Does that have the same effect? Isn't the variable available everywhere now?

Yes but hopefully my last post highlights the risks of public variables.

So my public function FullSER, I should change to Public Sub?

You don't have to. Bob Larson put forward quite a compelling case that you only ever need to use functions because a subroutine is just a function with nothing returned. The only counter argument I could make was that it wasn't the norm i.e. it is generally accepted that if you want to return a variable then use a function. If you just want to execute some code but don't want to return a value then use a subroutine.

 

[speakers_86]

Reset!!

Option Compare Database

Public Sub Variable()
Dim Suffix As String
End Sub

Public Function FullSER() As String
Dim SER As String
SER = "000-" & Suffix & "Q" & vyear

End Function

Public Function ReportSQL() As String
Dim SQL As String
SQL = "SELECT [tblReportType].ReportTypeID, [tblReportType].ReportTypeText, [tblReportType].ReportTypeAbbr FROM tblReportType WHERE (((tblReportType.ReportTypeText)='Quarterly')) ORDER BY [ReportTypeID]; "
[Forms]![frmser]![Combo144].RowSource = SQL
[Forms]![frmser]![Combo144].Requery
[Forms]![frmser]![Page177].Enabled = False
[Forms]![frmser]![Page178].Enabled = False
[Forms]![frmser]![Page226].Enabled = False
[Forms]![frmser]![Page189].Enabled = False
[Forms]![frmser]![Page209].Enabled = False
[Forms]![frmser]![Page188].Enabled = False
End Function

Public Function SeriesCheck()
    'Creates a new ser series for quarterly (one series for each year)
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Set db = CurrentDb
    Set rst = db.OpenRecordset("tblserseries")
    rst.AddNew
    rst("serseries") = 0
    rst("seropen") = 0
    rst("issue") = "Quarterly"
    rst("siteid") = 1
    rst.Update
    Set rst = Nothing
    Set db = Nothing
End Function



Public Function SuffixCheck()
    'Creates a new ser series for quarterly (one series for each year)
    Dim db1 As DAO.Database
    Dim rst1 As DAO.Recordset
    Set db1 = CurrentDb
    Set rst1 = db1.OpenRecordset("tblsersuffix")
    rst1.AddNew
    rst1("serseriesid") = DLookup("[serseriesid]", "qryQuarterlySerCheck", "[SeriesDate] =" & [Combo2])
    rst1("sersuffix") = Suffix
    rst1("reporttypeid") = 6
    rst1.Update
    Set rst1 = Nothing
    Set db1 = Nothing
End Function


'Public Function Variable()
'Dim Suffix As String
'End Function


'Private Sub Form_Current()
'Me.Combo2.DefaultValue.ItemData (0)
'End Sub

Private Sub Text5_Click()
'Setfocus so that the cursor doesnt stay on the control
Me.Combo2.SetFocus
'Determine the year for purposes of determining the final ser number
Dim vyear As String
If Nz(Me.Combo2, Right(Date, 4)) = Right(Date, 4) Then
    vyear = ""
    Else
    vyear = "(" & Right(Me.Combo2, 2) & ")"
End If
'If necessary, creates a new series.  One series for each year.
If IsNull(DLookup("[serseries]", "qryQuarterlySerCheck", "[SeriesDate] =" & [Combo2])) Then
    Call SeriesCheck
End If
'Determine if new suffix needs to be created
Suffix = "01"
Call FullSER
If IsNull(DLookup("[fullser]", "qryser2", "[fullser]='" & SER & "'")) Then
    Call SuffixCheck
    MsgBox "test"
End If
'Open frmSER
'DoCmd.OpenForm "frmser", , , "[fullser]= '000-01Q'" & vyear
DoCmd.OpenForm "frmser", , , "[fullser]= '" & "ser" & "'"
'Sets combo 144 to only quarterly
Call ReportSQL
[Forms]![frmser].TabCtl176.Pages.Item("page186").SetFocus

End Sub

Did I write Public Function FullSER right?
Am I using Public Sub Variable right?
I have a data type error at

If IsNull(DLookup("[fullser]", "qryser2", "[fullser]='" & SER & "'")) Then

Is that just a problem with the quotes?

 

[speakers_86]

Looks like I got it sorted out. Thanks for helping.