Crystal & Edgar-
I would do it slightly differently:
Column Count: 2
Column Widths: 0"; 3"
List Width: 3.25"
Row Source:
SELECT CityID, City & ", " & State As CityState
FROM Cities
ORDER BY City, State;
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 Crystal
Sent: Saturday, June 04, 2011 3:47 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Re: Problem with a query, Help!
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
>
>
>
------------------------------------
Yahoo! Groups Links
Tidak ada komentar:
Posting Komentar