Hi Edgar,
adding on to what John said...
> "The problem with this is that I would have to know the corresponding number of a given city everytime I try to run this query, and that doesn't sound too fun."
another solution: use a form, not a query, to collect the criteria.
make this form:
Name --> frm_ReportMenu
control: combobox
Name --> CityID
ColumnCount --> 3
Columnwidths --> 0; 3" ; 1"
ListWidth --> 4.2"
RowSource -->
SELECT CityID, City, State
FROM Cities
ORDER BY City, State
WHERE
CityID, City, State --> your fieldnames
Cities --> your tablename
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
then, replace the parameter criteria in your report with -->
frms!frm_ReportMenu!CityID
the report menu form has to be open when you run the query, which you can easily do from the report menu by making this command button:
Name --> cmd_qryListCities
OnClick [Event Procedure]
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
if IsNull(me.CityID) then
me.CityID.SetFocus
msgbox "You must specify a City",,"Missing Data"
me.CityID.Dropdown
exit sub
end if
Docmd.OpenQuery "YourQueryName"
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Warm Regards,
Crystal
*
(: have an awesome day :)
*
--- On Fri, 6/3/11, John Viescas wrote:
> Edgar-
>
> Did you download and read "Problem With Lookups.doc" in the
> Files /
> Articles_Excerpts folder on the group web page?
>
> If you don't want artificial keys, then don't use
> AutoNumber, but sometimes you
> can't avoid that. Your City field is a good example
> because city names aren't
> unique. If a user picks "Franklin" - does he or she
> want "Franklin, AL" or
> "Franklin, CT" or one of the 28 other US cities with that
> name? So your city
> lookup table will probably look like:
>
> CityID AutoNumber
> CityName Text
> StateName Text
>
> If you try to get rid of the AutoNumber, you would need to
> make both CityName
> and StateName the Primary Key to ensure uniqueness.
> But a Combo Box can be
> bound to only one field, so you would have to jump through
> hoops to allow the
> user to pick the correct combination and then set two
> fields in the related
> table. AutoNumber solves that by returning one unique
> number.
>
> If you use AutoNumber, you must remember to always include
> the lookup table in
> any query you build. The number 592 means nothing as
> a foreign key value for
> City, but when linked to the lookup table, you can include
> the city and state
> fields from that table to make it easy to sort on search on
> the actual name.
>
> HTH...
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com]
> On Behalf Of
> rivera_vazquez_edgar
> Sent: Saturday, June 04, 2011 12:17 AM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Re: Problem with a query, Help!
>
> Hello Paul, and thanks for your reply.
> I tried changing the "ACTIVO" criteria for the record
> autonumber for that value
> (2) and it worked. The thing is that such field seams
> to be bound to the first
> colum (the autonumber). Is there a way to change this so it
> looks up the actual
> value? I want to do this because I have the same problem
> with the 'City' field.
> In criteria I put [Enter City] and in the pop up i have to
> put the number of the
> first colum in the tblCity instead of the actual city
> name. The problem with
> this is that I would have to know the corresponding number
> of a given city
> everytime I try to run this query, and that doesn't sound
> too fun. What can I
> do?
>
> Edgar
>
> --- In MS_Access_Professionals@yahoogroups.com,
> "Paul" <pbaldy72@...> wrote:
> >
> > Two possibilities come to mind. Most likely is
> that the Status field is
> numeric, and is expecting 1 (or the appropriate number)
> instead of "ACTIVO".
> The other possibility is that a field in the join has
> incompatible data types,
> like IdCli is a number in one table and text in the other.
> >
> > Paul
> >
> >
> > --- In MS_Access_Professionals@yahoogroups.com,
> "rivera_vazquez_edgar"
> <xtintores@> wrote:
> > >
> > > Hello again...
> > > So I've made all my tables, ralationships and
> forms and now I'm off to
> making the querys for my reports. I tried to make my first
> query and when I try
> to run it get a message that says "Data type mismatch in
> criteria expression".
> > >
> > > The SQL is:
> > >
> > > SELECT tblClients.Status, tblClients.Cuenta,
> tblClients.Company,
> tblClients.Tel1, tblClients.Tel2, tblClients.Tel3,
> tblContacts.Title,
> tblContacts.FirstName, tblContacts.MiddleName,
> tblContacts.LastName1,
> tblContacts.LastName2, tblContacts.Nickname,
> tblContacts.JobTitle,
> tblContacts.Tel4, tblContacts.Tel5, tblAddress.AddrType,
> tblAddress.Address,
> tblAddress.City, tblAddress.Tel6, tblAddress.Tel7
> > > FROM (((tblClients INNER JOIN tblContacts ON
> tblClients.IdCli =
> tblContacts.IdCli) INNER JOIN tblAddress ON
> tblClients.IdCli = tblAddress.IdCli)
> INNER JOIN tblClientInventory ON tblClients.IdCli =
> tblClientInventory.IdCli)
> INNER JOIN tblServices ON tblClientInventory.IdInven =
> tblServices.IdInven
> > > WHERE (((tblClients.Status)="ACTIVO"));
> > >
> > > Can someone rescue me out of this one?
> > > Thanks a lot, Edgar
> > >
> >
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
> MS_Access_Professionals-fullfeatured@yahoogroups.com
>
>
>
Tidak ada komentar:
Posting Komentar