Re: Access 2000 Query using "Between Operator"

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Van T. Dinh (VanThien.Dinh_at_discussions.microsoft.com)
Date: 05/04/04 

Date: Tue, 4 May 2004 23:16:10 +1000

Perhaps you should try to follow the naming convention to make it easier to
read and decode. Also, don't use spaces / special characters in names.
They simply add complications when you do coding.

See The Access Web:

http://www.mvps.org/access/general/gen0012.htm

Most Access books (at least the reasonable ones) also have info. on the
Leszynski/Reddick naming conventions. 

-- 
HTH
Van T. Dinh
MVP (Access)
"Donald Regener" <anonymous@discussions.microsoft.com> wrote in message
news:7f6801c431cd$7c25df50$a501280a@phx.gbl...
> Van:
>
> The Second Version Works as desired.
>
> Code posted below.
>
> Appreciate your help.
>
> Don
>
> Query1: 2 Contract: Booth Notification Report
> Query_byBooth#
> SELECT [2 Contract].Booth_Number, [0408wsa1].booth, Val
> ([Booth]) AS
> Sort, [2 Contract].Contract_Number, [ContactFN] & " " &
> [ContactLN]
> AS Contact, [2 Contract].Company, [2 Contract].Address, [2
> Contract].Address2, [2 Contract].City, [2 Contract].State,
> [2
> Contract].Zip, [2 Contract].Country, [2 Contract].Phone,
> [2
> Contract].Fax, [2 Contract].Email, [2 Contract].Venue,
> [BoothSizeX]
> & " X " & [BoothSizeY] AS BoothSize, [2 Contract].S01, [2
> Contract].S02, [2 Contract].S03, [2 Contract].S04
> FROM [2 Contract] INNER JOIN 0408wsa1 ON [2
> Contract].Cust_ID =
> [0408wsa1].cust_id
> WHERE ((Not ([0408wsa1].booth) Is Null) AND (([2
> Contract].Venue)
> Not Like "Collections"));
>
>
> Query2: 2 Contract: Booth Notification Report
> Query_byBooth#2
> SELECT [2 Contract: Booth Notification Report
> Query_byBooth#].Booth_Number, [2 Contract: Booth
> Notification Report
> Query_byBooth#].booth, [2 Contract: Booth Notification
> Report
> Query_byBooth#].Sort, [2 Contract: Booth Notification
> Report
> Query_byBooth#].Contract_Number, [2 Contract: Booth
> Notification
> Report Query_byBooth#].Contact, [2 Contract: Booth
> Notification
> Report Query_byBooth#].Company, [2 Contract: Booth
> Notification
> Report Query_byBooth#].Address, [2 Contract: Booth
> Notification
> Report Query_byBooth#].Address2, [2 Contract: Booth
> Notification
> Report Query_byBooth#].City, [2 Contract: Booth
> Notification Report
> Query_byBooth#].State, [2 Contract: Booth Notification
> Report
> Query_byBooth#].Zip, [2 Contract: Booth Notification
> Report
> Query_byBooth#].Country, [2 Contract: Booth Notification
> Report
> Query_byBooth#].Phone, [2 Contract: Booth Notification
> Report
> Query_byBooth#].Fax, [2 Contract: Booth Notification
> Report
> Query_byBooth#].Email, [2 Contract: Booth Notification
> Report
> Query_byBooth#].Venue, [2 Contract: Booth Notification
> Report
> Query_byBooth#].BoothSize, [2 Contract: Booth Notification
> Report
> Query_byBooth#].S01, [2 Contract: Booth Notification
> Report
> Query_byBooth#].S03, [2 Contract: Booth Notification
> Report
> Query_byBooth#].S04
> FROM [2 Contract: Booth Notification Report Query_byBooth#]
> WHERE (((CLng([2 Contract: Booth Notification Report
> Query_byBooth#].[Sort])) Between CLng([First Booth#]) And
> CLng([Last
> Booth#])))
> ORDER BY [2 Contract: Booth Notification Report
> Query_byBooth#].Sort;
> >-----Original Message-----
> >Post the SQL String of your Query.
> >
> >-- 
> >HTH
> >Van T. Dinh
> >MVP (Access)
> >
> >
> >"Donald Regener" <anonymous@discussions.microsoft.com>
> wrote in message
> >news:6d7301c42f83$7114db30$a401280a@phx.gbl...
> >> Van:
> >>
> >> First, I would like to thank you for your response.
> >>
> >> Second, Let me try and clarify the requirement to see IF
> >> you have answered my question ....
> >>
> >> (1) The "Range" Lower and Upper Limit is in response to
> >> a "Prompt"  (Example: Between [LowerLimit] AND
> >> [UpperLimit] ... where does CLong go in this Code ...
> Edit
> >> SQL View ???
> >> (2) The Actual Values of the field I am working with, is
> >> the Result of a "Val() Function).  It starts as a "Text
> >> Field" and is run thru the Val() Function.
> >>
> >> Is you response based upon these
> requirements/conditions???
> >>
> >> Again, I appreciate your help.
> >>
> >> >-----Original Message-----
> >> >If you work with whole (integral) numbers, try:
> >> >
> >> >WHERE CLng([YourField]) BETWEEN LowerLimit AND
> UpperLimit
> >> >
> >> >If it still doesn't work, try:
> >> >
> >> >WHERE CLng([YourField]
> >> >BETWEEN CLng(LowerLimit) AND CLng(UpperLimit)
> >> >
> >> >--
> >> >HTH
> >> >Van T. Dinh
> >> >MVP (Access)
> >> >
> >> >
> >> >
> >> >"Donald Regener" <anonymous@discussions.microsoft.com>
> >> wrote in message
> >> >news:6ba901c42eed$8166a090$a601280a@phx.gbl...
> >> >> I am developing a Query that requires a "Between
> >> Operator"
> >> >>
> >> >> The field requiring the Operator started as a "Text
> >> >> Field".  The field was converted to a Number Field by
> >> use
> >> >> of the Val() Function.  Unfortunately the Data can
> be a
> >> >> 3, 4, or 5 character field (In the Native Table).  I
> am
> >> >> trying to Use the "Between Operator" with Prompts.
> The
> >> >> Query works as desired with a "Fixed Value" (Number
> >> >> Embedded in Between Statment" vice entered as part of
> >> >> the "Prompt Response").
> >> >>
> >> >> The problem is associated with the 3 and 4 character
> >> >> data.  IF the 3 and 4 character data were excluded
> >> >> (>9999), the query would work as desired.  When the 3
> >> and
> >> >> 4 character data is included, the query results looks
> >> >> like a "Text Search" on the first characters.  The 3
> and
> >> >> 4 Character Data is intermingled with the Correct 5
> >> Digit
> >> >> Data.  (Example Between 10,000 and 10999 entered from
> >> >> Prompts would return: 10130, 1024, 113, 11405, etc).
> I
> >> >> have tried enclosing the Prompt field with a Val()
> >> >> Function (From SQL View) and enclosing with the Trim
> ()
> >> >> Function.  Neither has provided the desired results.
> >> >>
> >> >> I cannot change the 3 & 4 Character data to 5
> >> >> Characters.  I lost that request this morning.  I do
> not
> >> >> know how to fix it, but it appears the Prompt field
> has
> >> >> some characters associate with it beside the ones
> typed
> >> >> as the response to the Prompt.
> >> >>
> >> >> All help Gladly accepted.
> >> >>
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >

Relevant Pages

  • Re: GPS and old-fashioned thinking? - ADS-B position broadcasting
    ... In this, the aircraft automatically transmits, via data links, its ... and ground systems before being able to receive any ADS report. ... made by ground system. ... appropriate occurrence contract. ...
    (rec.aviation.ifr)
  • Re: OT - NBC "Solyndra to Declare Bankruptcy. President Obama touted the facility only a year ag
    ... Under that $2.16 billion contract, called Host Nation Trucking, the US Army has hired eight civilian trucking firms to transport supplies to the web of combat outposts and bases set up through Afghanistan. ... The Nation story, which was reported in conjunction with the Investigative Fund of The Nation Institute, described how US taxpayer dollars being funneled into an elaborate system of extortion in Afghanistan as well-connected security firms made payoffs to warlords allied with insurgents for safe passage of US military goods. ... The report by the Subcommittee on National Security and Foreign Affairs says the resulting system promotes corruption and strengthens warlords—while the US military looks on. ...
    (alt.sports.basketball.nba.la-lakers)
  • Re: Please help. I am being sued through a court. How do I respond?
    ... The report is referred ... |> to in the particulars section of the particulars of claim as "in ... So the particulars of breach are in the expert report. ... contract will follow". ...
    (uk.legal)
  • Iraq: Cheneys Halliburton Rapes US Taxpayers
    ... Iraq: Cheney's Halliburton Rapes US Taxpayers ... Report: Halliburton Unit Exploited Rules ... Halliburton spokeswoman Cathy Mann said that since the current contract ...
    (soc.culture.iraq)
  • Re: Access 2000 Query using "Between Operator"
    ... Query1: 2 Contract: Booth Notification Report ... I would like to thank you for your response. ...
    (microsoft.public.access.queries)