Date or Text field?

[davesmith202]

I have a client with a text field with the month written in it. But they want to create a Query where they can search for records over 4 months old.

What do you suggest I do?

Thanks,

Dave

 

[namliam]

If the text is english, you can "simply" use CDate to convert the month/text to a real date then search on that...

 

[midmented]

You can put this function in a module and call it from the query column. You'll have to come up with the criteria.

Function MonthConvert(strMonth As String)
    Dim intNum As Integer
    MonthConvert = 0
    On Error GoTo Err_MonthConvert
    If strMonth Like "Oct*" Or strMonth Like "Nov*" Or strMonth Like "Dec*" Then
        intNum = 2
    Else
        intNum = 1
    End If
    MonthConvert = Left(Format(DateValue("1/" & strMonth), "m/dd"), intNum)
Err_MonthConvert:
End Function

 

[namliam]

Surely if you just want the month number, you can better use Month(datevalue())

Beyond that, for what the OP is asking this function is utterly useless (dont mean to step on anyones feelings, but useless indeed)

 

[davesmith202]

Let me clarify what they want after further conversation. The field will just show the month. e.g. January, February etc. The field is "Month Contract Expires". And I think they want to find out which are due in 4 months time. They look like annual contracts.

 

[namliam]

Then there needs to be a year someplace too?? How else can you identify a Januari that has already passed from a Januari that is yet to come?

datevalue or cdate the Month + year and you have a 'proper' date that you can do anything with.

 

[davesmith202]

Well, maybe I need to do something in code perhaps? e.g. if the current date was November, I would need to filter by November+4=March. So maybe Select Case statements that return the month to filter by and have a function in the query criteria. Would that work?

 

[midmented]

Maybe this would work?
MyMonth: CDate([Month Contract Expires] & "/01/" & Year(Date()))

For the query field.

 

[namliam]

No that wont work because you want to return Dec - march, anything < march (or < 3) needs to be shown, which doesnt include Dec (12)

Also what year is march referening to?? Can you guarantee the month name is always in the future?? or will March "2010" still be there next year?

If you can guarantee the "future" bit you can maybe do something like:
IIF (Month already passed, 12, 0) + Month number from string

That way march would become 15 (12 + 3) and you search anything < 15 which in turn should show up 12 as well.

Eitherway you need a "real date" to work it all out

 

[davesmith202]

What about the method I suggested. Can you see any reason why that would not work?

 

[midmented]

Well, he said +4 months so a criteria like:
>DateAdd("m",+4,Date())
should do that.
The only issue I foresee in this is "WHEN" they run this. IN that such case, using a Between criteria with DateAdd should solve it.

I agree that that field should have been an established date and not just a text month.

 

[namliam]

Since your simply not listening and/or not answering my questions ...
I guess you could try hard coding it..

Month(date()) will return the current month number (11)
Then in the criteria for your worded "IN ('December', ..., 'March')

Then the or will be for 12 and IN (Jan...Apr)
etc. for all 12 months

This is ugly and hard to maintain if it ever need to change but yes should work

 

[namliam]

Well, he said +4 months so a criteria like:
>DateAdd("m",+4,Date())
should do that.
The only issue I foresee in this is "WHEN" they run this. IN that such case, using a Between criteria with DateAdd should solve it.

This is working with real dates, which the OP doesnt seem to want to understand/do

 

[davesmith202]

namliam, I didn't go for your original idea because as you stated, it needs a real date. But the client has only the month as text. Therefore, my suggested solution would work but yours wouldn't, unless I changed the field or data entered. But I appreciate all help given.

 

[Brianwarnock]

If these are Annual Contracts the year doesn't matter, a March contract is a March contract.

Why not give your idea a go.

Brian

 

[davesmith202]

I have done this, but get a null error. How can I prevent that?

Query criteria: =MonthIn4([Month Contract Expires])

Public Function MonthIn4(txtMonth)

    Select Case txtMonth

    Case "January"
        MonthIn4 "May"

    Case "February"
        MonthIn4 "June"

    Case "March"
        MonthIn4 "July"

    Case "April"
        MonthIn4 "August"

    Case "May"
        MonthIn4 "September"

    Case "June"
        MonthIn4 "October"

    Case "July"
        MonthIn4 "November"

    Case "August"
        MonthIn4 "December"

    Case "September"
        MonthIn4 "January"

    Case "October"
        MonthIn4 "February"

    Case "November"
        MonthIn4 "March"

    Case "December"
        MonthIn4 "April"

    End Select

End Function

 

[Brianwarnock]

How is this query going to work?
If it is run this month, November, do you just want the March expirers?

Is the month run the driving factor or will it be parameter driven?

Brian

 

[davesmith202]

It is just those who are expiring 4 months ahead. So yes, at this point in time, I just want March expirers. At least that is what the client is currently saying! lol

 

[Brianwarnock]

In the criteria for your txtDate put
Format(DateAdd("m",4,Date()),"mmmm")

Brian

 

[davesmith202]

I'm not getting much joy with that. Are you aware that it is a text field? Do you mean put...

=Format(DateAdd("m",4,Date()),"mmmm")

...in the criteria field?