Apply filter by text AND Date (1 Viewer)

ECEK · Feb 28, 2019

Can you just help me with this syntax please?

Im trying to filter my form where the field USER = "...to be allocated" AND where the AllDateTU field is less than an unbound date field on my form called AllocationDate
This is what I tried but Im getting a 3175 runtime error which Im stuggling to identify with. thanks in advance.

Code:

DoCmd.ApplyFilter , "(USER = '...to be allocated')AND (AllDateTU <#AllocationDate#)"

Minty · Feb 28, 2019

A good hint for these is to build the filter as a string and examine it as a debug.

Code:

Dim sFilter as String

sFilter = "(USER = '...to be allocated')AND (AllDateTU <#AllocationDate#)"
debug.print sFilter

I suspect you want something like

Code:

sFilter = "([USER] = '...to be allocated') AND ([AllDateTU] < #" &  Me.AllocationDate & "#)"
debug.print sFilter
Me.Filter =  sFilter
Me.FilterOn = True

ECEK · Feb 28, 2019

Hi Minty That's fantastic. It's a much better structure.
I guess I was trying to do it too quickly.
Many thanks, works a treat.

Minty · Feb 28, 2019

You are welcome!

ECEK · Mar 6, 2019

Ot Oh !!!! Something went wrong !!!!

Me.AllocationDate is an unbound textbox set as short date (with datepicker) dd/mm/yyyy
However the date that is entered does not correspond to the the date needed to filter. It's as though it is filtering by US not UK !

I tried a static filter and sure enough if I swap the dd and mm around it filters correctly.

"([AllDateTU] <= #03/08/2019#)" where three is the month and 8 is the day

My thoughts are to format Me.AllocationDate to mm/dd/yyyy but I have no idea how to achieve this within the VBA code?

Minty · Mar 6, 2019

Save this function into a Module (Don't call the module SQLDate , call it modBasicFunctions or something equally obvious!)

Code:

Function SQLDate(varDate As Variant) As String
    'Purpose:    Return a delimited string in the date format used natively by JET SQL.
    'Argument:   A date/time value.
    'Note:       Returns just the date format if the argument has no time component,
    '                or a date/time format if it does.
    'Author:     Allen Browne. allen@allenbrowne.com, June 2006.
    
    If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            SQLDate = Format$(varDate, "\#yyyy\/mm\/dd\#")
        Else
            SQLDate = Format$(varDate, "\#yyyy\/mm\/dd hh\:nn\:ss\#")
        End If
    End If
 
    
End Function

And then simply use it in any SQL constructions where you use a date.

Explained in detail here http://allenbrowne.com/ser-36.html

Your VBA then becomes

Code:

sFilter = "([USER] = '...to be allocated') AND ([AllDateTU] < " &  SQLDate(Me.AllocationDate) & ")"

Note how you no longer need the # # around your date string as the function does that for you.

ECEK · Mar 6, 2019

Think I've fixed it !!!

Dim AllDate As Date
AllDate = Format(Me.AllocationDate, "mm/dd/yyyy")

and then

stFilter = "([AllDateTU] <= #" & AllDate & "#)

Ill confirm in a bit......

Minty · Mar 6, 2019

Use the function provided, it works and will also avoid issues if you upscale to SQL server, as the format provided yyyy-mm-dd is pretty universally accepted.

Another bonus is that it is much easier to debug as you aren't left wondering which format you are dealing with.

Apply filter by text AND Date (1 Viewer)

ECEK

Registered User.

Minty

AWF VIP

ECEK

Registered User.

Minty

AWF VIP

ECEK

Registered User.

Minty

AWF VIP

ECEK

Registered User.

Minty

AWF VIP

Similar threads

Users who are viewing this thread