VBA Date will not be read. Date parameter as Date defined, still no progress!

[Susy]

Hi there,

I am having really problems with one piece of code... The other two filters are working fine, but the date filter is not working.



In the main table, the ActionDate field is defined as Date/Time (Short Date) as well as in the form. Still I am not able to make it run....

I get the following error message at this line:

Me.subfrmReport.Form.RecordSource = task

This is what the form looks like:


I used the idea and code from austin72406 https://www.youtube.com/watch?v=choPri7y_o4

Function SearchCriteria()


Dim StatusName, TypeID, ActionDate As String
Dim task, strCriteria As String


    If IsNull(Me.cboStatus) Then
        StatusName = "[StatusName] like '*'"
    Else
        StatusName = "[StatusName] = '" & Me.cboStatus & "'"
    End If


    If IsNull(Me.cboType) Then
    TypeID = "[TypeID] like '*'"
        
    Else
        TypeID = "[TypeID] = " & Me.cboType
    End If
    
    
     If IsNull(Me.cboActionDate) Then
        ActionDate = "[ActionDate] like '*'"
    Else
        ActionDate = "[ActionDate] = #" & Me.cboActionDate & "#"
    End If
    
    
    
    
    strCriteria = StatusName & " AND " & TypeID & " AND " & ActionDate
        task = "SELECT * FROM tblReport WHERE " & strCriteria
        Me.subfrmReport.Form.RecordSource = task
        Me.subfrmReport.Form.Requery
    


End Function

Can someone see what I am doing wrong.... I don't know what to do.

Regards

[ranman256]

If the field is null,don't put it in the SQL.
the error box is wrong because the date has periods, and no closing #.

[Susy]

but... what would be the solution?

ActionDate = "[ActionDate] = #" & Me.cboActionDate & "#"

[andy49]

My initial advice would be to change the variables to

strStatusName
strTypeID
strActionDate

to confirm that variables weren't being mixed with fields

[CJ_London]

when requiring sql to convert a string to a date using the # chars, the date needs to be presented in US format. So try

ActionDate = "[ActionDate] = #" & format(Me.cboActionDate,"mm/dd/yyyy") & "#"

Also generally not a good idea to have variables with the same name as fields/controls -it can confuse access


And declaring variables like this

Dim StatusName, TypeID, ActionDate As String
Dim task, strCriteria As String


Only ActionDate and strCriteria are typed as string, the others will be typed as variant - and variants can be slow because time is required for the compiler to determine what type the variant is.

declare as

Dim StatusName As String, TypeID As String, ActionDate As String
Dim task As String, strCriteria As String


or

Dim StatusName As String
dim TypeID As String
Dim ActionDate As String
Dim task As String
dim strCriteria As String

[Susy]

Thanks, I did as recommended but... the same error

the new code:


I also wrote...

strActDate = "[ActionDate] = #" & Format(Me.cboActionDate, "dd/mm/yyyy") & "#"

but same error but with month and day replaced...

[andy49]

is me.cboactiondate returning a date value or a text version of the date?

[andy49]

And I'd add the line

msgbox stractiondate


after the line it's defined in to check whether the string actually has picked up the final #

[Susy]

here some snapshots...




I renamed the variables... the change the format of the date... ???

[CJ_London]

what I don't see is you say you have used the format function - so should be 04/12/2017 but it is displaying 04.12.2017

[andy49]

try


ActionDate = "[ActionDate] = #" & format(Me.cboActionDate,"mm\/dd\/yyyy") & "#"

the v shape is \ followed by /

[Susy]

Thanks andy49 and everyone....

It works like a charm.... you made my day!

In case... Happy Easter!

[andy49]

No problem

The short date separators are defined within local settings on your PC

Not even the Format (date) function can change them into /

So the \ forces the computer to use the next value (ie /)

All hale AllenBrowne!!

[Susy]

Thanks again and thanks for the information

[andy49]

FYI Susy

That website has a nice function will might prove useful.

If local settings are indeed set as . for dates then this wont be the last time you hit this issue.

And Happy Easter