Combo box for query criteria?

[AKK]

Hi,

I've set up a query where the user enters an email address to call up employee information. The way it is now, though, you have to know the person's exact email address, and it's easy to make a typo. Is there a way to set up a combo box so that the user can just choose from a list, and if so, how could I do this?

Thanks!

AKK

[OBP]

Create a Combo with the Toolbox Combo Wizard and when it asks what type you want tick the "Find a Record on my Form that matches my Combo Selection".
It is not the Default choice.

[AKK]

I'm not sure what you mean -- when I'm looking at a query in design view (or any view), I don't have access to the toolbox. I didn't create the input box where the user enters the email address -- it's there because I typed [Enter member email] in the criteria box in one of the query fields in design view. Is this the screen where I need to create the combo box? If so, how?

[OBP]

No, you need to Create a Form and then create a Combo on the form.
Are you just searching for one thing in your query?
If so, then remove the Criteria asking for information and instead create a Form using your Query as it's Record Source.
On that form you can display the Toolbox and Create the type of Combo I mentioned in my previous post.

If you are using your Query for a Report you can still have a Form & Combo to make the selection and replace your Criteria with
Forms![Formname]![Combo]

where Formname is the name of the form and Combo is the name fo your combo on the form.

[CreganTur]

If you are using your Query for a Report you can still have a Form & Combo to make the selection and replace your Criteria with
Forms![Formname]![Combo]

where Formname is the name of the form and Combo is the name fo your combo on the form.

If you do this, then you need to also do the following in Query Design View:

Click on Query->Parameters- enter the "[Forms!][Formname]![Combo]" value into the first column, and then select the correct data type in the second column. Then click Ok to save this.

If you do not do this, then you could end up with any number of errors because SQL is uncertain of the data type that it is receiving.

[mpearce]

edit: Sorry i meant start a new thread as my issue is a little different than this one


I am attempting to add a search feature to a data log app i am creating. I would like the user to be able to choose the table to search and then enter in a start date and an end date and the results would display for the date range they have chosen.

I was thinking of having a combo box and have that contain the names of the tables. Then I would have 2 text boxes one for start date and one for end date. Finally I would have a listbox and have the rowsource get set to the user built query. this would be triggered by a button click

i am thinking something like this:

dim tablename as string
dim startdate as string
dim enddate as string

tablename = cmbtablename.value
startdate = txtstartdate.value
enddate = txtenddate.value

Lstresults.rowsource = "select * from '" & [tablename] & "' where startdate >= '" & [startdate] & "' and enddate <= '" & [enddate] & "';"
this doesnt seem to generate any results in the listbox. I am thinking it is either an issue with the inequalities in the where clause or something with the table name.

What am I missing?

Thanks in advance

[CreganTur]

If StartDate and EndDate are actual Date data types, then you need to change the data qualifier from a single quote to a pound sign (#). Single quotes are only for strings, pound signs are for dates, and no data qualifier means a numeric data type.

[mpearce]

If StartDate and EndDate are actual Date data types, then you need to change the data qualifier from a single quote to a pound sign (#). Single quotes are only for strings, pound signs are for dates, and no data qualifier means a numeric data type.

right i understand that part, but what about the table name? wouldnt that query look for a table called 'tablename' ( for example) instead of tablename?