|
Subject:
|
searching tables
|
|
Posted By:
|
ozPATT
|
Post Date:
|
12/12/2005 8:11:45 AM
|
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
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
12/12/2005 12:44:31 PM
|
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
|
|
Reply By:
|
ozPATT
|
Reply Date:
|
12/13/2005 4:16:57 AM
|
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
|
