Date Filter not working

[ChrisLeicester]

Hi All

I have a simple continuous form that lists sales transactions with the following field, [SalesTransactionID], [TransactionDate], [CustomerName] and [TransTotal]

In the form header I have two unbound text boxes, [DatePicked] and [TranPicked] with a search button next to each box. The idea being the user can enter a date in the date box and the continuous form will filter to the records for that date, or filter for the transaction number in the other box. The transaction number box works perfectly and brings up the correct record. The datepicked box does nothing, it brings up no records at all. The transaction date field in the table is formatted as a short date, and so is the datepicked unbound text box, so it shouldnt be that.

Dim strSQL As String

strSQL = "TransactionDate = " & Format(Me.DatePicked, "m/d/yyyy")


Me.Filter = strSQL

Me.FilterOn = True

I did think the issue might be that here in the uk our dates are dd/mm/yyyy and access may be looking for m/d/yyyy so I tried to format the me.datepicked but it is the same, all previous records disappear and not are shown.

What am I doing wrong, the similar code works for the other box that is a number and not the date,

thanks

 

[DHookom]

Try add the date delimiters

Dim strSQL As String

strSQL = "TransactionDate = #" & Format(Me.DatePicked, "m/d/yyyy") & "#"

Me.Filter = strSQL

Me.FilterOn = True

 

[ChrisLeicester]

thank you, works perfect now

 

[Gasman]

Strings need to be surrounded by single quotes ' unless it contains a single quote, then triple double quotes works, I think?

Date literals with # and in mm/dd/yyyy or yyyy-mm-dd format

Numbers do not need anything
Also for anything other than one item of criteria, I tend to put the the criteria into a string variable and then debug.print it, until correct then also use that in the code.

Added benefit is, if you still cannot see the error, you can copy and paste back here the output from the debug.print for someone else to spot it.

Example:

tt="Eg'g"

? dlookup("FoodID","tblFoods","FoodName = """ & tt & """")