Solved Query date criteria (1 Viewer)

[ubi]

Hello!!

im trying to filter out a table based on a date range but it doesnt work right (check pics)

here is the query im using:

"INSERT INTO visua_ft_lvl1 ( [num taglio], [data creazione], [data fine taglio], stato, completato, Quantita, utente, [cod modello], [cod stampa], [num lab], laboratorio, [num carico], descrizione, tessuto1 ) " & _
"SELECT taglio.[num taglio], taglio.[data creazione], taglio.[data fine taglio], taglio.stato, taglio.completato, taglio.Quantita, taglio.utente, taglio.[cod modello], taglio.[cod stampa], taglio.[num lab], taglio.laboratorio, taglio.[num carico], taglio.descrizione, taglio.tessuto1 " & _
"FROM taglio " & _
"WHERE (((taglio.[data creazione]) Between #" & CDate(Me.startdate) & "# And #" & CDate(Me.enddate) & "#) AND " & sql_stato & " AND " & sql_completato & " AND ((taglio.[cod modello]) Like '*" & Me.Combo01 & "*'));"

I think it has something to do with the date format but idk how to fix it, help please !

 

[Minty]

You need to use the date format access understands - which is US mm/dd/yyyy or easier to read (if your normal format is dd/mm/yyyy ) when you are debugging yyyy-mm-dd

So this line needs to be

"WHERE (((taglio.[data creazione]) Between #" & Format(Me.startdate,"yyyy-mm-dd") & "# And #" & Format(Me.enddate ,"yyyy-mm-dd") & "#)

Edit : I would add a function to format the dates this is a modified version of Allen Brownes http://allenbrowne.com/ser-36.html

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.
    'Changes:    Modified for yyyy format 2012
    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

 

[ubi]

Great that fixed the problem! Thanks for fast reply !