Search Form..

[cassey14]

Hi everyone..

i hope someone help me on this..

i need to do a search form..but apparently there are seven fields that they need to search..for example they can search a last name or first name...etc..

please help me plss....

any ideas??im having a hard time coding..as well as the trapping..

tanx so much!

cassey

[George Oro]

I assumed your using a bound forms. Bound your form on a query and put the below parameter on the Criteria Grid of each field or column you wish to search. But make sure the criteria are not on the same row. Each criteria should be on different row.

Like "*" & [Forms]![YourFormName]![YourSearchTextbox] & "*"

then put this code under AfterUpdate of YourSearchTextbox
me.requery

or put under click of a button (its your choice)

If youre using unbound forms then let me know. I can give you some idea too.

hth,
George

Hi everyone..

i hope someone help me on this..

i need to do a search form..but apparently there are seven fields that they need to search..for example they can search a last name or first name...etc..

please help me plss....

any ideas??im having a hard time coding..as well as the trapping..

tanx so much!

cassey

[cassey14]

I assumed your using a bound forms. Bound your form on a query and put the below parameter on the Criteria Grid of each field or column you wish to search. But make sure the criteria are not on the same row. Each criteria should be on different row.

Like "*" & [Forms]![YourFormName]![YourSearchTextbox] & "*"

then put this code under AfterUpdate of YourSearchTextbox
me.requery

or put under click of a button (its your choice)

If youre using unbound forms then let me know. I can give you some idea too.

hth,
George

first thank you for the reply..i really appreciate it..

actually i am starting to do a search form but im still having more problems...first i need to query dates with ranges but the data type in my table is datetime..i dont know how will i format the dates.

cassey

[George Oro]

Is this search form used only to filter the date?

Where you want format the date?

first thank you for the reply..i really appreciate it..

actually i am starting to do a search form but im still having more problems...first i need to query dates with ranges but the data type in my table is datetime..i dont know how will i format the dates.

cassey

[cassey14]

Is this search form used only to filter the date?

Where you want format the date?

nop. actually they need to search by date or id number and other combo box..but i really need to filter the date..they need to search by date but the problem is my data type in database is date/time.

thank you so much..

[George Oro]

Date/Time is the field of date and no other.

What I understand is, the value in your table is Date + Time (no pure date) If thats the case, Add one blank column to your query and put this on Field row:
PureDate:Format([YourDateFieldName],"dd/mm/yyyy") then search from this column.

in your search form, format also the date text box field search to ensure users input a valid date:

Properties:
Format: dd/mm/yyyy
Input Mask: 00/00/9999;0;_


you can change this as you like "dd/mm/yyyy"
d=day
m=month
y=year


HTH,
George




hth,
George

nop. actually they need to search by date or id number and other combo box..but i really need to filter the date..they need to search by date but the problem is my data type in database is date/time.

thank you so much..

[NeoPa]

Nice work George.
In truth, there are special ways of dealing with Date/Time fields.
A trick I use to get a pure date, when necessary, is to say CDate(Int([DateField])).
If date literals are required then see (Literal DateTimes and Their Delimiters (#).).
In general, the way to have a multi-field search form is to have a form which is bound to the data source table/query but to have the search items as unbound controls (on the same form).
You would have a general purpose VBA routine to create a filter string. This routine would be called by each of the AfterUpdate event properties for the various search controls. Its job would be to create a string with SQL for all the search controls with valid data in them. The last thing it would include is some code running at the end to requery the form :

Call Me.ReQuery

[George Oro]

How I forgot the CDate function?! silly George! thanks for the tip-trick chief!

Regards,
George

Nice work George.
In truth, there are special ways of dealing with Date/Time fields.
A trick I use to get a pure date, when necessary, is to say CDate(Int([DateField])).
If date literals are required then see (Literal DateTimes and Their Delimiters (#).).
In general, the way to have a multi-field search form is to have a form which is bound to the data source table/query but to have the search items as unbound controls (on the same form).
You would have a general purpose VBA routine to create a filter string. This routine would be called by each of the AfterUpdate event properties for the various search controls. Its job would be to create a string with SQL for all the search controls with valid data in them. The last thing it would include is some code running at the end to requery the form :

Call Me.ReQuery