An I doing this correctly?

  • Thread starter Thread starter Eric
  • Start date Start date
E

Eric

Hello everyone.

I have a report made that has a control source as =FindQTR
([Reports]![FSR Report by Plant]![Date Recieved]) which
calls this Report module:

Function FindQtr(QtrDate As Date) As String

If QtrDate >= "1/1/" & Right(Me.Date_Recieved, 4) And
QtrDate <= "6/31/" & Right(Me.Date_Recieved, 4) Then
Me.txtQtr.Value = "1Q" & Right(Me.Date_Recieved, 2)
ElseIf QtrDate >= "4/1/" & Right(Me.Date_Recieved, 4) And
QtrDate <= "6/30/" & Right(Me.Date_Recieved, 4) Then
Me.txtQtr.Value = "2Q" & Right(Me.Date_Recieved, 2)
ElseIf QtrDate >= "7/1/" & Right(Me.Date_Recieved, 4) And
QtrDate <= "9/30/" & Right(Me.Date_Recieved, 4) Then
Me.txtQtr.Value = "3Q" & Right(Me.Date_Recieved, 2)
ElseIf QtrDate >= "10/1/" & Right(Me.Date_Recieved, 4) And
QtrDate <= "12/31/" & Right(Me.Date_Recieved, 4) Then
Me.txtQtr.Value = "4Q" & Right(Me.Date_Recieved, 2)
End If

End Function

....but I keep getting a error stating Type Mismatch 13
with the report showing #Error in the cell I am trying to
refrence. What am I doing wrong?
 
Wrap the date text strings with CDate function so that you are comparing a
date variable to a date value, not a string. For example,

QtrDate >= CDate("1/1/" & Right(Me.Date_Recieved, 4)) And
 
If I understand what you are doing, you should only need to set the control
source of txtQtr to the field [Date_Recieved] and set the format property to
Format: q\Qyy
There should be no reason to add any code.

Also, you have "6/31" where I think it should be "3/31".
Also, I would never rely on expressions like "1/1/" & Right(somedate,4)
since you convert back and forth from text to date and your regional date
settings may cause issues. Consider using DateSerial(Year(somedate), 1,1)
Also, If-ElseIf-ElseIf-ElseIf-End If is very difficult to maintain and
should be replaced by
Select Case
Case
Case
Case
Case Else
End Select
 
I recommend against this type of expression since if your Windows settings
uses a short date format of m/d/yy then Right([YourDate],4) may return
something like "3/04" rather than "2004".

--
Duane Hookom
MS Access MVP
--

Ken Snell said:
Wrap the date text strings with CDate function so that you are comparing a
date variable to a date value, not a string. For example,

QtrDate >= CDate("1/1/" & Right(Me.Date_Recieved, 4)) And


--

Ken Snell
<MS ACCESS MVP>

Eric said:
Hello everyone.

I have a report made that has a control source as =FindQTR
([Reports]![FSR Report by Plant]![Date Recieved]) which
calls this Report module:

Function FindQtr(QtrDate As Date) As String

If QtrDate >= "1/1/" & Right(Me.Date_Recieved, 4) And
QtrDate <= "6/31/" & Right(Me.Date_Recieved, 4) Then
Me.txtQtr.Value = "1Q" & Right(Me.Date_Recieved, 2)
ElseIf QtrDate >= "4/1/" & Right(Me.Date_Recieved, 4) And
QtrDate <= "6/30/" & Right(Me.Date_Recieved, 4) Then
Me.txtQtr.Value = "2Q" & Right(Me.Date_Recieved, 2)
ElseIf QtrDate >= "7/1/" & Right(Me.Date_Recieved, 4) And
QtrDate <= "9/30/" & Right(Me.Date_Recieved, 4) Then
Me.txtQtr.Value = "3Q" & Right(Me.Date_Recieved, 2)
ElseIf QtrDate >= "10/1/" & Right(Me.Date_Recieved, 4) And
QtrDate <= "12/31/" & Right(Me.Date_Recieved, 4) Then
Me.txtQtr.Value = "4Q" & Right(Me.Date_Recieved, 2)
End If

End Function

...but I keep getting a error stating Type Mismatch 13
with the report showing #Error in the cell I am trying to
refrence. What am I doing wrong?
Click to expand...
Click to expand...
 
For one thing, dates are not strings and using string
functions on them can return invalid results. Use the Year
() function or DatePart() function to obtain the year part
of a date rather than using the Right() function. Also,
you don't need this nested If at all! Use the DatePart()
function to return the quarter. Look up the syntax in
help so you can make this very useful function part of
your repertoire.

If you want to refer to a field in the current report, use
the following syntax:
=indQTR([Date Recieved])

If the field reference is to a different report than the
current one, find another way. The referenced report
would need to be open and if you were to be able to
retrieve a data value at all, it would most likely be for
the last record in the report.
 
You're correct, Duane. Thanks.
--

Ken Snell
<MS ACCESS MVP>



Duane Hookom said:
I recommend against this type of expression since if your Windows settings
uses a short date format of m/d/yy then Right([YourDate],4) may return
something like "3/04" rather than "2004".

--
Duane Hookom
MS Access MVP
--

Ken Snell said:
Wrap the date text strings with CDate function so that you are comparing a
date variable to a date value, not a string. For example,

QtrDate >= CDate("1/1/" & Right(Me.Date_Recieved, 4)) And


--

Ken Snell
<MS ACCESS MVP>

Eric said:
Hello everyone.

I have a report made that has a control source as =FindQTR
([Reports]![FSR Report by Plant]![Date Recieved]) which
calls this Report module:

Function FindQtr(QtrDate As Date) As String

If QtrDate >= "1/1/" & Right(Me.Date_Recieved, 4) And
QtrDate <= "6/31/" & Right(Me.Date_Recieved, 4) Then
Me.txtQtr.Value = "1Q" & Right(Me.Date_Recieved, 2)
ElseIf QtrDate >= "4/1/" & Right(Me.Date_Recieved, 4) And
QtrDate <= "6/30/" & Right(Me.Date_Recieved, 4) Then
Me.txtQtr.Value = "2Q" & Right(Me.Date_Recieved, 2)
ElseIf QtrDate >= "7/1/" & Right(Me.Date_Recieved, 4) And
QtrDate <= "9/30/" & Right(Me.Date_Recieved, 4) Then
Me.txtQtr.Value = "3Q" & Right(Me.Date_Recieved, 2)
ElseIf QtrDate >= "10/1/" & Right(Me.Date_Recieved, 4) And
QtrDate <= "12/31/" & Right(Me.Date_Recieved, 4) Then
Me.txtQtr.Value = "4Q" & Right(Me.Date_Recieved, 2)
End If

End Function

...but I keep getting a error stating Type Mismatch 13
with the report showing #Error in the cell I am trying to
refrence. What am I doing wrong?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top