Expression help in Query

G

Guest

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;
 
T

tina

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 said:
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;
Click to expand...
 
G

Guest

Wow! That works great. I have another question that I didn't think of
before. I added in the additional ranges that I needed and then inserted the
module. My ranges begin at value 11. All numbers 10 and below I would like
it to display the actual record. Since I don't know anything about writing
code I left in the Case Else and the "Unknown Value" entry, so all of my
numbers from 0 to 10 show "Unknown Value". How can I tell it to show 0 - 10
numbers as actual values?
This is what my custom function looks like:

Public Function isRange(ByVal lngNum As Long) As String

Select Case lngNum
Case 33 To 50
isRange = "33-50"
Case 51 To 100
isRange = "51-100"
Case 11 To 12
isRange = "11-12"
Case 13 To 15
isRange = "13-15"
Case 16 To 17
isRange = "16-17"
Case 18 To 21
isRange = "18-21"
Case 22 To 26
isRange = "22-26"
Case 27 To 32
isRange = "27-32"
Case 101 To 250
isRange = "101-250"
Case Is > 250
isRange = "GT 250"
Case 10 To 10
isRange = "10"
Case 9 To 9
isRange = "9"
Case 8 To 8
isRange = "8"
Case 7 To 7
isRange = "7"
Case Else
isRange = "Unknown Value"
End Select

End Function

--
Thanks,
Lucien


tina said:
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 said:
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;
Click to expand...
Click to expand...
 
J

John Vinson

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?
Click to expand...

Just an alternative suggestion to Tina's excellent code. You may want
to use a table-driven approach, both to allow changes in the ranges or
labels without needing to edit code and for efficiency of the query.

YOu can create a table with three fields - Low, High, and Rangename,
with fields like

1 1 1
2 2 2
....
33 37 33-37
38 50 38-50
51 100 51-100
....
250 2000000000 GT 250

Join this table to your table with a "Non Equi Join":

SELECT yourtable.thisfield, yourtable.thatfield, Rangetable.Rangename
FROM yourtable
INNER JOIN rangetable
ON yourtable.numberfield >= rangetable.Low
AND yourtable.numberfield <= rangetable.High;

John W. Vinson[MVP]
 
G

Guest

John,

I created my table ... it is called Invoice Range Buckets with Low, High,
and Rangename fields. Where you wrote:

Join this table to your table with a "Non Equi Join":

SELECT yourtable.thisfield, yourtable.thatfield, Rangetable.Rangename
FROM yourtable
INNER JOIN rangetable
ON yourtable.numberfield >= rangetable.Low
AND yourtable.numberfield <= rangetable.High;

How do I do this, I mean how do I incorporate that into my current SQL?
Here is what I have so far:

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;
 
J

John Spencer

If by Actual Record, you mean the value of lngNum


Public Function isRange(ByVal lngNum As Long) As String

Select Case lngNum
Case 33 To 50
isRange = "33-50"
Case 51 To 100
isRange = "51-100"
Case 11 To 12
isRange = "11-12"
Case 13 To 15
isRange = "13-15"
Case 16 To 17
isRange = "16-17"
Case 18 To 21
isRange = "18-21"
Case 22 To 26
isRange = "22-26"
Case 27 To 32
isRange = "27-32"
Case 101 To 250
isRange = "101-250"
Case Is > 250
isRange = "GT 250"
Case 0 To 10
isRange = Cstr(lngNum)
Case Else
isRange = "Unknown Value"
End Select

End Function
Wow! That works great. I have another question that I didn't think of
before. I added in the additional ranges that I needed and then inserted the
module. My ranges begin at value 11. All numbers 10 and below I would like
it to display the actual record. Since I don't know anything about writing
code I left in the Case Else and the "Unknown Value" entry, so all of my
numbers from 0 to 10 show "Unknown Value". How can I tell it to show 0 - 10
numbers as actual values?
This is what my custom function looks like:

Public Function isRange(ByVal lngNum As Long) As String

Select Case lngNum
Case 33 To 50
isRange = "33-50"
Case 51 To 100
isRange = "51-100"
Case 11 To 12
isRange = "11-12"
Case 13 To 15
isRange = "13-15"
Case 16 To 17
isRange = "16-17"
Case 18 To 21
isRange = "18-21"
Case 22 To 26
isRange = "22-26"
Case 27 To 32
isRange = "27-32"
Case 101 To 250
isRange = "101-250"
Case Is > 250
isRange = "GT 250"
Case 10 To 10
isRange = "10"
Case 9 To 9
isRange = "9"
Case 8 To 8
isRange = "8"
Case 7 To 7
isRange = "7"
Case Else
isRange = "Unknown Value"
End Select

End Function

--
Thanks,
Lucien

tina said:
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 said:
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;
Click to expand...
Click to expand...
Click to expand...
 
J

John Vinson

How do I do this, I mean how do I incorporate that into my current SQL?
Click to expand...

Like this:

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,
[Range Name Buckets].[RangeName]
FROM (MasterAvailabilityData INNER JOIN ItemDescriptions ON
MasterAvailabilityData.ItemID = ItemDescriptions.ItemID)
INNER JOIN [Range Name Buckets]
ON [MasterAvailabilityData].[BranchItemTrans] >= [Range Name
Buckets].[Low]
AND [MasterAvailabilityData].[BranchItemTrans] <= [Range Name
Buckets].[High]
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;

What's the datatype of BranchItemTrans - Text, hence the VAL? If so
that complicates matters considerably; my JOIN assumes it's a Number,
and if you try to use a range of text values you'll find that "2" is
greater than "100" and that "100" is less than "20"!

John W. Vinson[MVP]
 
G

Guest

Yes the BranchItemTrans field is Text. This is such a huge table that Access
will not let me change the format of that field to number. It is obvioulsy
giving me problems. From what you said, your SQL will not work correctly
since that field is Text. I'm not sure what to do about it, though.

--
Thanks,
Lucien


John Vinson said:
How do I do this, I mean how do I incorporate that into my current SQL?
Click to expand...

Like this:

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,
[Range Name Buckets].[RangeName]
FROM (MasterAvailabilityData INNER JOIN ItemDescriptions ON
MasterAvailabilityData.ItemID = ItemDescriptions.ItemID)
INNER JOIN [Range Name Buckets]
ON [MasterAvailabilityData].[BranchItemTrans] >= [Range Name
Buckets].[Low]
AND [MasterAvailabilityData].[BranchItemTrans] <= [Range Name
Buckets].[High]
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;

What's the datatype of BranchItemTrans - Text, hence the VAL? If so
that complicates matters considerably; my JOIN assumes it's a Number,
and if you try to use a range of text values you'll find that "2" is
greater than "100" and that "100" is less than "20"!

John W. Vinson[MVP]
.
Click to expand...
 
G

Guest

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 said:
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 said:
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;
Click to expand...
Click to expand...
 
J

John Vinson

Yes the BranchItemTrans field is Text. This is such a huge table that Access
will not let me change the format of that field to number. It is obvioulsy
giving me problems. From what you said, your SQL will not work correctly
since that field is Text. I'm not sure what to do about it, though.
Click to expand...

There's no limit on the size of table - if you do it right! You can
create a new, empty table by copying and pasting this one, design mode
only; change the datatype in the empty table; then run an Append query
to migrate the data.

To do this with a Text field you'll need to use a (much less
efficient) datatype conversion on the join.

John W. Vinson[MVP]
 
T

tina

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 said:
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 said:
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 said:
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
Click to expand...
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
Click to expand...
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;
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thanks for the clarification!


John Spencer said:
If by Actual Record, you mean the value of lngNum


Public Function isRange(ByVal lngNum As Long) As String

Select Case lngNum
Case 33 To 50
isRange = "33-50"
Case 51 To 100
isRange = "51-100"
Case 11 To 12
isRange = "11-12"
Case 13 To 15
isRange = "13-15"
Case 16 To 17
isRange = "16-17"
Case 18 To 21
isRange = "18-21"
Case 22 To 26
isRange = "22-26"
Case 27 To 32
isRange = "27-32"
Case 101 To 250
isRange = "101-250"
Case Is > 250
isRange = "GT 250"
Case 0 To 10
isRange = Cstr(lngNum)
Case Else
isRange = "Unknown Value"
End Select

End Function
Wow! That works great. I have another question that I didn't think of
before. I added in the additional ranges that I needed and then inserted the
module. My ranges begin at value 11. All numbers 10 and below I would like
it to display the actual record. Since I don't know anything about writing
code I left in the Case Else and the "Unknown Value" entry, so all of my
numbers from 0 to 10 show "Unknown Value". How can I tell it to show 0 - 10
numbers as actual values?
This is what my custom function looks like:

Public Function isRange(ByVal lngNum As Long) As String

Select Case lngNum
Case 33 To 50
isRange = "33-50"
Case 51 To 100
isRange = "51-100"
Case 11 To 12
isRange = "11-12"
Case 13 To 15
isRange = "13-15"
Case 16 To 17
isRange = "16-17"
Case 18 To 21
isRange = "18-21"
Case 22 To 26
isRange = "22-26"
Case 27 To 32
isRange = "27-32"
Case 101 To 250
isRange = "101-250"
Case Is > 250
isRange = "GT 250"
Case 10 To 10
isRange = "10"
Case 9 To 9
isRange = "9"
Case 8 To 8
isRange = "8"
Case 7 To 7
isRange = "7"
Case Else
isRange = "Unknown Value"
End Select

End Function

--
Thanks,
Lucien

tina said:
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


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;
Click to expand...
Click to expand...
.
Click to expand...
 
G

Guest

Ok, I'll try that over the weekend. Thanks!


John Vinson said:
There's no limit on the size of table - if you do it right! You can
create a new, empty table by copying and pasting this one, design mode
only; change the datatype in the empty table; then run an Append query
to migrate the data.

To do this with a Text field you'll need to use a (much less
efficient) datatype conversion on the join.

John W. Vinson[MVP]
Click to expand...
 
Top