query

[natalie]

hi, i have another question! i am doing a query which goes through the
Appointments table, to search for a specific property address which the user
types in. however, if i type in the text of address, nothing comes up; but
if i type in the SiteId, then the data is displayed. in the table of the
Appointment, i have
SurveyorID (primary key)
Date (primary key)
Time (primary key)
PropertyAddress
County
SiteID (foriegn key, relating to the table Site)

any ideas on how ican get this query to work by typing in the property line
text?
Many thanks

 

[vbasean]

yes,

here it is step by step

have the propertyaddress as your SECOND field in your query

go to your form, go to the properties of the combobox (I assume you're
using a combo box with this query as it's source)

go under format

the column widths are probably set to auto, you can set each width
individualy by writing them out and separating the values by commas

here's an example
1", 2", 3"

but you want the first column, your SurveyorID to be 0"

set it to 0" and the others as you like

this will force the combo box to receive imput from the address line
and not the surveyorID

 

[Ken Sheridan]

Does the Site table contain the property addresses? If so add the Site table
to the query, joining it on SiteID and add the column with the property
address to the design grid. Then move the parameter, i.e. [Enter Property
Address:] or whatever you've used, to the new text column.

My guess is that when designing the Appointments table you might have used
the 'look up' feature so that the PropertyAddress column shows the value from
the Site table, not the underlying ID value actually stored in the table.
This feature is not thought highly of by most developers. See the following
link for reasons why:


http://www.mvps.org/access/lookupfields.htm


In fact, if the Site table does contain the property addresses, it looks to
me as though the PropertyAddress column in the Appointments table is
redundant and can be deleted.

You could make the process a lot more user friendly if you are prepared to
do a little more work. First design an unbound dialogue form with a combo
box set up as follows:

RowSource: SELECT SiteID, PropertyAddress FROM Site ORDER BY
PropertyAddress;

BoundColum: 1
ColumnCount: 2
ColumnWidths 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

Reference the combo box as the parameter for the SiteID column in the query
in place of the simple [Enter Property Address:] parameter, e.g.

Forms!frmSitesDlg!cboSites

Add a button to the dialogue form to open the query, or better still a form
or report based on it. All the user then has to do is open the dialogue
form, select a site from the drop down list and click the button. There's no
reason why you can't have more than one button, of course, to open a form,
preview a report or print a report.

Ken Sheridan
Stafford, England

 

[Jeff Boyce]

Natalie

We aren't there. We can't see what you're looking at.

Consider posting the SQL statement your query uses.

When you say "type in the ... address", where are you typing this in?

Access is extremely literal -- if there is the slightest difference between
the data in the table and what gets typed in, Access won't match it up.

Have you tried using wildcard characters for your queries? You could use
something like:

Like * & [Enter a partial address] & *

into the Selection Criterion under the Address field in a query's design
grid.

This would prompt you for a string, then look for any row containing that
string anywhere in the field.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[natalie]

it works perfect now! thanku, i did not think of using the site table for
the query.