Eliminate time from date

[diegomarino]

Hi
i have a sharepoint list called "calendar" in which i have a date field; this field is just date field, in sharepoint has no time.
i linked this sharepoint list to my access database, and in this database some dates have time to like "13/05/2020 1:00:00"

i want to get rid of this time, i tried with an update query but did not work


this is the table in a new database, please help

[ranman256]

normally all fields would have both date/time.
you can query it and format it for time only: Format([field],"hh:nn:ss")

[diegomarino]

thanks for the flash answer
my first problem is: why some record has time and some other no?
other question: and to format just for the date?

[ranman256]

if no time is there, it is 12 midnite

for date: format([field]","dd/mm/yyyy")

[Gicu]

Have a look at the query qryCalendario, I wrapped the Initial and End dates (Fine calendario) in DateValue to remove the time part.
Cheers,
Vlad

[diegomarino]

thank you very much it was very useful

i got however that my problem with vba filter was the date format, and i had to US format my date picker.
thank you again

[CJ_London]

if you want to eliminate time from the underlying value, run an update query

set myDate=datevalue(myDate)

what you see as a date is just format of a number

?cdbl(now())
43953.3601273148
?cdbl(date())
43953
?cdbl(datevalue(now()))
43953



43953 is the number of days since 31/12/1899 and .3601273148 is the time expressed as number of seconds divided by the number of seconds in a day (86400)

[diegomarino]

thanks is a good idea to convert to number.
put in my tool box

[diegomarino]

sorry to necroposting

If Me.DataInizioFilt <> "" Then      If FiltStr = "" Then
         FiltStr = " CDbl(DateValue([Data inizio]))  >= " & CDbl(DateValue(Me!DataInizioFilt)) & ""
      Else
         FiltStr = FiltStr & " AND cdbl(datevalue([data inizio])) >= " & CDbl(DateValue(Me!DataInizioFilt)) & ""
      End If
   End If

the first part of the filter is not processed

CDbl(DateValue([Data inizio]))

and it returns

CDbl(DateValue([Data inizio])) >= 43950

i can't figure out how to elaborate the first part CDbl(DateValue([Data inizio]))

if you want to eliminate time from the underlying value, run an update query

set myDate=datevalue(myDate)

what you see as a date is just format of a number

?cdbl(now())
43953.3601273148
?cdbl(date())
43953
?cdbl(datevalue(now()))
43953



43953 is the number of days since 31/12/1899 and .3601273148 is the time expressed as number of seconds divided by the number of seconds in a day (86400)

[CJ_London]

not sure what you mean

CDbl(DateValue([Data inizio])) >= 43950

is what I would expect (29th April)

so what does this mean?

i can't figure out how to elaborate the first part CDbl(DateValue([Data inizio]))

and it returns me the string "

[diegomarino]

sorry, i meant that i have an error "kind of data not corresponding"

my string filter is "CDbl(datevalue([Data inizio]))) >= 43950"

both are date field, i can't find the error
it returns a runtime error 3464

[CJ_London]

just realised you have 2 ( and 3 ) in your string

me.filter="CDbl(datevalue([Data inizio]))) >= 43950"

but you are making it overly complex - this should work

me.filter="[data inizio]>=#04/30/2020#"


the fact there is a time element is irrelevant for this sort of comparison

[ArviLaanemets]

I haven't Access available to check currently, but you converted the result of DateValue() to number, and as follows you get a number instead of date.

Probably like
FiltStr = "DateValue([Data inizio]) >= " & DateValue(Me!DataInizioFilt) & ""
or (in case Me!DataInizioFilt is date without time component)
FiltStr = "DateValue([Data inizio]) >= " & CDate(Me!DataInizioFilt) & ""

[diegomarino]

lookès like it is working

" [Data inizio] >= #" & Format(Me!DataInizioFilt, "mm/dd/YYYY") & "#"

cause we have a different date format

i make some other tests but shoud be ok
thanks again