Crystal-
Why would either be Null? I prefer to deal with that eventuality this way:
([City] + ", ") & [State]
or
[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 5:14 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Re: Problem with a query, Help!
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
> > > >
> > >
> >
------------------------------------
Yahoo! Groups Links
Tidak ada komentar:
Posting Komentar