Re: Expression help in Query

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

well, i don't really know. if you exported the query *with the function in
it* to another db, you also need to export the module that contains the
function. did you do that?

hth


"Lucien" <Lucien@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:6EE0E368-A4C1-4D0E-89C4-D70F16919CB7@xxxxxxxxxxxxxxxx
Tina, when I used your code it worked fine...but when I try to export my
query to another database, it doesn't work because it says something about
Access 2000 and Access 2003. I am using 2003. What could the problem be.
--
Thanks,
Lucien


"tina" wrote:

10 ranges makes one heck of a nested IIf() statement. i'd probably write
a
custom function that uses a Select Case statement, instead, as

Public Function isRange(ByVal lngNum As Long) As String

Select Case lngNum
Case 33 To 37
isRange = "33-37"
Case 51 To 100
isRange = "51-100"
Case Is > 250
isRange = "GT 250"
Case Else
IsRange = "Unknown value"
End Select

End Function

put the code into a standard module. if you don't have one in your
database
already, then from the db window, click on the Modules tab, click New,
write
the code *below* any text the already appears in the VBA window, and
save
the module with the name modPublicProcedures. (the module name is not
important - except that it must be different from the name of the
procedure
you put in it.)

change your SQL to

SELECT MasterAvailabilityData.OrderDate,
MasterAvailabilityData.ActualAvailPlant,
MasterAvailabilityData.AvailFailureCode,
IsRange([MasterAvailabilityData].[BranchItemTrans]) AS BrchItmTrans,
MasterAvailabilityData.ItemID, ItemDescriptions.Desc,
MasterAvailabilityData.RSL, MasterAvailabilityData.SSL,
MasterAvailabilityData.SLOverrideQuantity,
MasterAvailabilityData.SLOverrideType,
MasterAvailabilityData.SLOverReason,
MasterAvailabilityData.SeasonalCode, MasterAvailabilityData.SpinCode,
MasterAvailabilityData.SpecialItemCat
FROM MasterAvailabilityData INNER JOIN ItemDescriptions ON
MasterAvailabilityData.ItemID = ItemDescriptions.ItemID
WHERE (((MasterAvailabilityData.OrderDate) Between #1/1/2006# And
#1/31/2006#) AND ((MasterAvailabilityData.AvailFailureCode)<>0) AND
((Val([MasterAvailabilityData]![BranchItemTrans]))>9) AND
((MasterAvailabilityData.BranchServicesDistrict)="w6"))
ORDER BY MasterAvailabilityData.ActualAvailPlant,
MasterAvailabilityData.AvailFailureCode,
[MasterAvailabilityData]![BranchItemTrans] DESC;

note that i change the name of your field alias from BranchItemTrans to
BrchItmTrans, because i don't think you can alias a field to the same
fieldname. also, note that i didn't "vet" the rest of your SQL
statement -
only dealt with the "range" issue.

hth


"Lucien" <Lucien@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:580BD046-D012-4886-8F76-13F489C119F7@xxxxxxxxxxxxxxxx
I have a basic Access question. I am trying to create a query that
will
display different results than what is in my table. I have a field
that
is:
Val([MasterAvailabilityData]![BranchItemTrans]) AS BranchItemTrans.
This field is full of numbers. I would like the query to do the
following
for this field:
basically create an If .... Then expression.
If record is 34, then display 33-37
If record is 55, then display 51-100
If record is 260, the display GT 250....and so on.
I have 10 different ranges that I would like to display instead of the
actual numerical record. Is this possible?

Here is the SQL view that I have so far, and I am a beginner, so go
easy
on
me:

SELECT MasterAvailabilityData.OrderDate,
MasterAvailabilityData.ActualAvailPlant,
MasterAvailabilityData.AvailFailureCode,
Val([MasterAvailabilityData]![BranchItemTrans]) AS BranchItemTrans,
MasterAvailabilityData.ItemID, ItemDescriptions.Desc,
MasterAvailabilityData.RSL, MasterAvailabilityData.SSL,
MasterAvailabilityData.SLOverrideQuantity,
MasterAvailabilityData.SLOverrideType,
MasterAvailabilityData.SLOverReason,
MasterAvailabilityData.SeasonalCode, MasterAvailabilityData.SpinCode,
MasterAvailabilityData.SpecialItemCat
FROM MasterAvailabilityData INNER JOIN ItemDescriptions ON
MasterAvailabilityData.ItemID = ItemDescriptions.ItemID
WHERE (((MasterAvailabilityData.OrderDate) Between #1/1/2006# And
#1/31/2006#) AND ((MasterAvailabilityData.AvailFailureCode)<>0) AND
((Val([MasterAvailabilityData]![BranchItemTrans]))>9) AND
((MasterAvailabilityData.BranchServicesDistrict)="w6"))
ORDER BY MasterAvailabilityData.ActualAvailPlant,
MasterAvailabilityData.AvailFailureCode,
Val([MasterAvailabilityData]![BranchItemTrans]) DESC;
--
Thanks,
Lucien





.

Relevant Pages

  • Re: Mysql database and OO_base.org: problem with INNER JOINTS on tables
    ... display the main table with placenames in clear, ... list the ones using equality in the query first. ... Whatever line with only one NOT NULL placefield, did not appear at all, ... All this requires learning some SQL and being about to design a query in SQL ...
    (Debian-User)
  • Re: Select DISTINCT...wont select DISTINCT
    ... I thought I was starting to get the hang of SQL, at least a little, but this ... [Advertising Entity Name], ... FROM [Display Entity Mapping]) ... your table structure is going to make your query not work in a usefull ...
    (microsoft.public.access.queries)
  • Re: Combo Box based on Query sort not working - A2K
    ... This is in an app I did not write, ... The combo box is based on a table (and I've put in SQL and a query - ... I tried changing the number of rows to display and the ...
    (comp.databases.ms-access)
  • Re: DB Result Wizard Error
    ... Also noted that one folder, which contains no information can't be deleted, ... >> For instance Equipment Serial Number, Equipment Model Number, Cost Accounting ... >> to display the new equipment information. ... >> is in the table was added to the query. ...
    (microsoft.public.frontpage.programming)
  • RE: Just starting to design a form (or is it a query?)
    ... A Form is used to display the data and allow ... A query can be the record source for a form. ... that match an ID in the Assessment table. ... Now, assuming this is the case, what you need is a form with a sub form. ...
    (microsoft.public.access.gettingstarted)