Search form

[Gerdagan]

Hi all,

The question I have is quite specific. I have looked through the forum, and the big www, but I can only find general examples, and I just can't seem to make them fit my needs.

Here is my set-up.

Table name: T-Dates
Fields in table: Date (e.g. 01/01/12), Day (e.g. 01), Month (e.g. January), Year (e.g. 2012).

I also have a form (F-Dates) which uses T-Dates as its record source.

Here is what I want to do.

I have three ubound list boxes on an unbound form:

LB-Day
LB-Month
LB-Year

I can select the day, month and year (e.g. 01 January 2012). What I then want to do is, on the click of the 'search' button I would like to open the relating record in the form, F-Dates.

I want access to use the criteria in the list boxes to find a match in the 'date' field (e.g. 01/01/11), and open the corresponding record.

I hope you can help, knowing me I am over complicating matters.

Thanks again all,
Ged.

 

[John Big Booty]

You could use the DateSerial() function to form the three values in your text boxes into a fully formed date which you can then use as criteria in your query.

 

[Gerdagan]

Thanks John,

But I am still struggling to see how I can apply this to my DB. I have uploaded an example.

What I am trying to do is open the Form 'F-Booking' to the matching record as stated in the Form 'F-Date' when the date is selected using the combo boxes.

I hope someone can help, because this simple little thing is just wracking my brains . All of the data for the lookup is in the Table 'T-Dates'.

Thanks all,
Ged.

 

[John Big Booty]

First up Your table T-Dates is horribly un-normalised. If you are storing the date you do not also need to store it's broken down component as these can be derived, at any time you need them, using DatePart() function.

Rather than your current method of selecting a date you could use a date picker such as the one here.

 

[Gerdagan]

Thanks John, I've looked at the 'date pick' example you gave, but to me that looks more uncomplicated and un-normalised as my T-Dates table.

I've taken a look at the un-normalised info. on Wiki, but it just confuses me. And the DataPart() link leads me to the Wiki also. Thanks a lot for taking the time to look at my example and to reply to me, I really appreciate it.

I have built databases in the past, but for some reasons I just can't get my head around sorting this function. I think I'll just go for the easy route and put a combo box in the header of my form and let the users just select the date they want.

Thanks.