vba datediff function written inside string

Question

So I have a text box, and when the user enters a value into this text box I am filtering a form. The user enters a "7" inside the text box and a form opens with all records updated in the past 7 days. In order to do this I am using

docmd.openform "myform", , , stringfilter

stringfilter is a string I created where I am trying to use the datediff function. The name of my field that has the last edited date is called [timestamp]. So I thought this would work but for some reason it does not:

stringfilter = "datediff( " & "d," & "#timestamp#," & "now" & ") < " & mytextbox.value

I expect the output of this string to be the following, but i don't believe it is:

datediff("d",timestamp,now) < 7

Anyone know where I am going wrong?

Krish Krish · Accepted Answer · 2014-08-27T14:20:15

I'm not sure how you are using the filter string in SQL query but you can try this:

Dim iD As Integer
iD = Val(mytextbox.value)
'Any err trapping goes here if userinput is valid before opening your form

stringFilter = "Datediff('d','" & VBA.Format$([DateField], "dd/mm/yyyy") & "','" & VBA.Format$(Date, "dd/mm/yyyy") & "') < " & iD

in above method you are converting both dates to one format for more accurate result. you can forgive #tags within Datediff command. Having said that Datediff command can recognize different date-formats

Alternative1: just add another column to your query as:

DateDiff("d",[updated_date],Now()) as lastUpdatedInDays

and you can open the form with where condition like

docmd.OpenForm formname,acNormal,,"[lastUpdatedInDays]<" & id

vba datediff function written inside string

2 Answers