Why do I get a Text Result? 1

[acjim]

Hi,

I've put together the following simple vB function to convert regular months to financial months for a yr on yr comparison report. It seems to work fine but the results are shown as text, this then leads to problems in getting the Minimum and Maximum etc.

Obviously I can convert the results to Integers but (using CInt) but this slows up an already lengthy process.

Why is the result text? (I've declared the intMth and intResultMth as Integers at the Option Explicit stage)

If you know a better way to complete the month to fiscal month conversion, don't be shy!

Many Thanks

Jim

Public Function FinancialMth(psmth As Integer) As Integer

intMth = psmth

Select Case intMth

Case 4
intResultMth = 1
Case 5
intResultMth = 2
Case 6
intResultMth = 3
Case 7
intResultMth = 4
Case 8
intResultMth = 5
Case 9
intResultMth = 6
Case 10
intResultMth = 7
Case 11
intResultMth = 8
Case 12
intResultMth = 9
Case 1
intResultMth = 10
Case 2
intResultMth = 11
Case 3
intResultMth = 12
Case Else
intResultMth = 12
End Select

FinancialMth = intResultMth

End Function

 

[nicsin]

Hi acjim,

where are the results shown as texts? I guess you use this function somewhere and then you get a string somehow. Can you also post this code?

 

[acjim]

Hi nicsin,

The results are posted as records in a ms.Access query field.

I haven't any code as such for this, but the field box in the query grid looks like this:

Fin_MONTH: FinancialMth(Month([fcependdate]))

I've checked the "Month" function and it returns a number. The data in [fcependdate] are dates.

Cheers
Jim

 

[nicsin]

>the results are shown as text

Where? How do you know?

 

[acjim]

When the query finishes running the records are shown with left align, all the numbers are shown with right align.

Also when I try to find the minimum Fin_Month the minimum is chosen by the text method of sorting i.e. 1,10,11,12,2,3 etc. This is fixed when I add in the CInt conversion but is v.slow

hope that made sense!

 

[nicsin]

Yes it did. Can you also post the query where you use this function?

 

[acjim]

Here you go, it's a bit messy...


PARAMETERS [Enter Start Month (mm), April as 1, March as 12] Short, [Enter End Month (mm), April as 1, March as 12] Short;
SELECT tblINP_DB_FULL.START_SITE, IIf([START_SITE]="RV820","NPH",IIf([START_SITE]="RV831","CMH",IIf([START_SITE]="RV899","ACAD",IIf([START_SITE]="RV8E2","EDG",IIf([START_SITE]="RV8M2","STM&quot)))) AS SITE, tblINP_DB_FULL.ADMISSION, [FULL_SPECIALTY] & "/" & [specdesc] AS SPEC, tblINP_DB_FULL.YEAR, tblINP_DB_FULL.FCEPENDDATE, Format([FCEPENDDATE],"mm&quot AS [MONTH], tblINP_DB_FULL.PATID, tblINP_DB_FULL.FFCE, tblINP_DB_FULL.CENSUS_DATE, tblINP_DB_FULL.FULL_SPECIALTY, FinancialMth(Month([fcependdate])) AS Fin_MONTH
FROM tblINP_DB_FULL LEFT JOIN Specs ON tblINP_DB_FULL.FULL_SPECIALTY = Specs.SubSpec
WHERE (((tblINP_DB_FULL.YEAR)=2002 Or (tblINP_DB_FULL.YEAR)=2003) AND ((tblINP_DB_FULL.FFCE)=&quot;Y&quot AND ((tblINP_DB_FULL.FULL_SPECIALTY)<>42058 And (tblINP_DB_FULL.FULL_SPECIALTY)<>42059 And (tblINP_DB_FULL.FULL_SPECIALTY)<>42060 And (tblINP_DB_FULL.FULL_SPECIALTY) Not Like &quot;501*&quot; And (tblINP_DB_FULL.FULL_SPECIALTY)<>180 And (tblINP_DB_FULL.FULL_SPECIALTY) Not Like &quot;560*&quot AND ((FinancialMth(Month([fcependdate]))) Between [Enter Start Month (mm), April as 1, March as 12] And [Enter End Month (mm), April as 1, March as 12]))
ORDER BY tblINP_DB_FULL.FCEPENDDATE;

 

[nicsin]

I haven't been able to reproduce your problem. I set up a database with a date field and a function that would accept and return an integer. then used

muFun(Month([myDateField]))

and the results were integers, ie right aligned!

 

[acjim]

Oh that's a shame, must be something odd going on here.

Thanks for your time
Jim

 

[nicsin]

I suggest you write a simple select query of the form:

select top 1 FinancialMth(1) as finMonth from tblINP_DB_FULL

Then run it and observe if the result is an integer.

 

[MichaelRed]

Public Function basFinMnth(Optional ByVal dtIn As Variant) As Integer

    'Sample Usage: _
    ? basFinMnth
    'basFinMnth
    ' 10

    '? basFinMnth
    'basFinMnth(#4/15/04#)
    ' 1

    Dim MyMnth As Integer

    If (IsMissing(dtIn)) Then
        dtIn = Date
    End If

    MyMnth = DatePart("m", dtIn)

    Select Case MyMnth
        Case Is >= 4
            basFinMnth = MyMnth - 3

        Case Else
            basFinMnth = MyMnth + 9
    End Select


End Function

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[acjim]

Thanks for all your messages and help - I'll definately use Michaels' function in a future report.
For the moment I have had to persevere with the original function and just hang around on tek-tips whilst it's running!

Thanks again guys
jim

 

[johnwm]

Or even:

fnFinMonth = 1 + ((myMonth + 2) Mod 12)

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'If we're supposed to work in Hex, why have we only got A fingers?'