hi all,

i am trying to pull from a table where the start date is greater than/equal to a specified 'from', and the start date is less than/equal to a specified 'to'.

problem is, it is comparing it in dd/mm/yyyy format, so obviously this will not work...

i have the table column set as date/time format, how can I compare the entries as yyyy-mm-dd format? Does it happen in the sql statement or is ther something else I have to do?

thanks

Patrick

Visit my site: http://www.drybonesuk.com

__________________
Visit my site: http://www.drybonesuk.com

Have you tried this and it is not working?

Why is it dd/mm/yyyy in one location, and yyyy-mm-dd in another?

In any event, you can create two hidden text boxes on your form, then in the after update event of each of your date pickers (from/to) parse the field to look like your table dates, then put those values in the hidden text boxes, and then put this criteria in the date field in your query:

BETWEEN [Forms]![frmYourForm].[txtHiddenFrom] AND [Forms]![frmYourForm].[txtHiddenTo]

Do you need help on Regular Expressions to parse the date field from one format to another?

HTH


mmcdonal

hi, I have found a way to search it, thought of it last night and have just tried it and it worked. Basically, although access displays the date in dd/mm/yyy format, it must still store it as yyyy-mm-dd.

I formatted the value of the datepicker to be yyyy-mm-dd before searching for it, and it has worked fine.

Thanks for your response though, it is really interesting to see how else this can be achieved.

Patrick

Visit my site: http://www.drybonesuk.com