good idea, John <smile>
... taking it another step to only display comma only if the city and state are both specified -- and if no City is specified, the first character is a space -- this, of course is optional
SELECT
CityID
, City & IIF(IsNull(City) or IsNull(State), null,",") & " " & State As CityState
FROM ...
Warm Regards,
Crystal
*
(: have an awesome day :)
*
--- On Sat, 6/4/11, John Viescas wrote:
> 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
> > > >
> > >
> >
Tidak ada komentar:
Posting Komentar