MS Access Query - Val() is returning "short date" rather than int.

[ArlinR]

I'm using Val() in an Access Query and my expression is a field in my database that consists of left aligned numbers in a varchar column, such as 100, 200 etc.
Val is returning 4/9 for 100. In other words April 9 which is the 100th day of the year. What can I do to get integers returned.
Thanks,
Arlin

 

[SkipVought]

Hi,

Try the CInt function.

You are getting a FORMAT of the date, 4/9/1900.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[ArlinR]

Hi Skip,
Like this?
Instead of Expr1: Val([Field_Name])
use
Expr1: CInt([Field_Name])

Thanks,
Arlin

 

[SkipVought]

What happened?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[ArlinR]

Unfortunately I'm on the road at the moment, and won't be able to try it until tomorrow AM.
I'll let you know how it works?
Thanks,
Arlin

 

[ArlinR]

Hi Skip,
I changed the query to Expr2: CInt([Field_Name])
In the report I get Expr2 showing correctly. e.g 100
However, when I try to use Expr2 in the following calculation
=Sum([SOD_RequiredQty]*[Expr2])
I get 36,502 as the result, regardless of the two sides of the equation.
SOD_RequiredQty is a "Standard" number format and equal to 200.
I'm missing something to get the correct answer of
200 X 100, not 36,502.
Thanks for the help,
Arlin

 

[dhookom]

Do you realize that you are Sum()ing your 2 columns from the query? You might have values of 200 and 100 if your query source contains only a single record. Also, this may be the penalty for not changing "[Expr2]" to something that has meaning.

Can you provide the full SQL view of your query?

Duane
Hook'D on Access
MS Access MVP

 

[ArlinR]

In my report, I have SOD_Required Qty visible and I have
Expr2 visible on the line.
However, the calc doesn't work.

Here's the SQL view:

SELECT Shipment.SH_ShipToName, SalesOrder.SOM_SalesOrderID, Shipment.SH_ShipDate, Item.IMA_ItemID, SalesOrderDelivery.SOD_RequiredQty, [sod_requiredqty]*[sod_unitprice] AS [Ext Price], IIf([sh_invoicedate]=[sod_requireddate],"On Time",IIf([sh_invoicedate]<[sod_requireddate],"Early",IIf([sh_invoicedate]>[sod_requireddate],"Late"))) AS Expr1, SalesOrderDelivery.SOD_RequiredDate, Shipment.SH_InvoiceDate, SalesOrder.SOM_SalesOrderType, ItemAttribute.Attribute1_Value, Item.IMA_ProdFam,

CInt([Attribute1_Value]) AS Expr2

FROM ItemAttribute INNER JOIN ((((Shipment INNER JOIN (ShipmentLine INNER JOIN Item ON ShipmentLine.SHL_IMA_RecordID = Item.IMA_RecordID) ON Shipment.SH_RecordID = ShipmentLine.SHL_SH_RecordID) INNER JOIN Customer ON Shipment.SH_CUS_RecordID = Customer.CUS_RecordID) INNER JOIN SalesOrder ON Shipment.SH_SOM_RecordID = SalesOrder.SOM_RecordID) INNER JOIN SalesOrderDelivery ON ShipmentLine.SHL_SOD_RecordID = SalesOrderDelivery.SOD_RecordID) ON ItemAttribute.ItemAttr_IMA_RecordID = ShipmentLine.SHL_IMA_RecordID
WHERE (((SalesOrderDelivery.SOD_RequiredDate) Between [Please Enter Begining Date] And [Please Enter Ending Date]) AND ((SalesOrder.SOM_SalesOrderType)=0) AND ((Item.IMA_ProdFam)="facestock la - 200 & up" Or (Item.IMA_ProdFam)="facestock la - 200 Or below" Or (Item.IMA_ProdFam)="facestock - 200 & up" Or (Item.IMA_ProdFam)="facestock - 200 Or below"));

Thanks,
Arlin

 

[MichaelRed]

casting (CINT) the field does not change the data type, so you could easily still be returning a "date".

The cast to Integer probably should return an error, however the original post suggests the actual database is SQL Server, so it's version could be recent and the range of integer would be larger?

If so, the vagries of the interface might well make differnt 'assumptions' on what the actual value is returned.



MichaelRed

 

[ArlinR]

What's confusing to me is that it returns an integer for printing "Expr2", but when used in a calculation, it seems to be grabbing something else.
I think it must have something to do with pulling in more than a single value for the calculation, since the calculated value is replicated on every line on the report, even though the value being printed for "Expr2" is different for each line.

 

[ArlinR]

I've discovered what the "wierd" number is.
If I have 3 lines of Sales Orders and on each line, I have a SOD_RequiredQty and a Labels Per (Expr2) and I use the expression =SUM([SOD_RequiredQty]*[Expr2])on the line, the value returned on each line is the total of all of the "Sums" calculated. For example:
Line 1 100 X 2 = 200
Line 2 200 X 3 = 600
Line 3 100 X 5 = 500
Each line is showing 1,300 as the result of the SUM for that line.
Strange?
Or is there some way to restrict the SUM to the query result for each line?

 

[dhookom]

Why are you using Sum() if you don't want to Sum() multiple records? If this expression is in the detail section, just use:

=[SOD_RequiredQty]*[Some Alias Better Than Expr2]

Duane
Hook'D on Access
MS Access MVP

 

[ArlinR]

Thanks Duane.
I'm sure that's the problem.
Duh!!
I feel kind of stupid.
I'll put the SUM in for the bottom of the report.
Thanks,
Arlin

 

[ArlinR]

I must have been hung up in Excel land!!!