Wrox Programmer Forums
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 12th, 2005, 09:11 AM
Authorized User
 
Join Date: Sep 2003
Posts: 34
Thanks: 0
Thanked 0 Times in 0 Posts
Default searching tables

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
 
Old December 12th, 2005, 01:44 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old December 13th, 2005, 05:16 AM
Authorized User
 
Join Date: Sep 2003
Posts: 34
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Provide Searching from more than 60 tables snowbydave1 PHP How-To 1 June 30th, 2008 02:28 PM
Searching multiple tables melkin Classic ASP Databases 0 March 10th, 2008 11:48 AM
creating tables within tables in access??? carswelljr Access 3 August 23rd, 2006 01:21 PM
searching treasacrowe Classic ASP Databases 3 September 3rd, 2004 07:50 AM
Searching programmed C++ Programming 1 June 24th, 2004 01:08 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.