UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Number/ String Manipulation Within A Query, Access 2016    
 
   
ITdarkside
post Jan 12 2018, 06:31 PM
Post#1



Posts: 62
Joined: 19-May 16



Hi all, I have a query that is designed to look up and increase the count of a control number.

It looks at a TableValue that has a format of LYYYY-NNN where L is a letter, YYYY is the year and NNN is a sequential number. This value comes from a SharePoint table and the NNN in the table has historically always had a three digit value, so values under 100 have preceeding zeros.

The current query does a Right(TableValue, 3) +1 to find the next biggest number, and it works, but if the preceeding number was H2018.004, the number it comes up with is H2018.5 instead of the desired H2018.005. So right now once the number has been appended into SharePoint, we have to go in and manually change the number within SharePoint itself.

This is the SQL for the existing query

SELECT DISTINCT
qryInvCost.BranchPlant,
tblPlants.DispoDesignator,
DatePart("yyyy",Date()) AS [Year],
[Material Disposition].Site,
(Right(Max([Material Disposition].[Control Number]),3)+1) AS Num,
DispoDesignator & Year & "." & Num AS NewNum
FROM ((qryInvCost INNER JOIN dbo_F0101 ON qryInvCost.BranchPlant = dbo_F0101.ABMCU) INNER JOIN tblPlants ON dbo_F0101.ABAN8 = tblPlants.BranchPlant) INNER JOIN [Material Disposition] ON tblPlants.Site = [Material Disposition].Site
GROUP BY qryInvCost.BranchPlant, tblPlants.DispoDesignator, DatePart("yyyy",Date()), [Material Disposition].Site, dbo_F0101.ABAT1, "DispoDesignator" & "Year" & "." & "Num"
HAVING (((qryInvCost.BranchPlant)=[Forms]![frmInv]![BranchPlant]) AND ((dbo_F0101.ABAT1)="BP"));

I tried the following, but when I run the query I get the error "Unable to execute query. Invalid operation or syntax using multi-value field."

SWITCH(LEN(Right(Max([Material Disposition].[Control Number]),3)+1) < 2, "00" + (Right(Max([Material Disposition].[Control Number]),3)+1), Len(Right([Material Disposition].[Control Number],3)+1) < 3, "0" + (Right(Max([Material Disposition].[Control Number]),3)+1), Len(Right(Max([Material Disposition].[Control Number],3))+1) >=3, (Right(Max([Material Disposition].[Control Number]),3)+1)) as Num,

This would be easy for me to solve in SQL server or VBA, but I'm at a bit of a loss with Access SQL. (I tried replacing the '+' in the aggregating section with '&' and it doesn't work either)

Any suggestions?


Go to the top of the page
 
tina t
post Jan 12 2018, 07:54 PM
Post#2



Posts: 5,518
Joined: 11-November 10
From: SoCal, USA


QUOTE
SELECT DISTINCT
qryInvCost.BranchPlant,
tblPlants.DispoDesignator,
DatePart("yyyy",Date()) AS [Year],
[Material Disposition].Site,
(Right(Max([Material Disposition].[Control Number]),3)+1) AS Num,
DispoDesignator & Year & "." & Num AS NewNum

try the following, as

SELECT DISTINCT
qryInvCost.BranchPlant,
tblPlants.DispoDesignator,
DatePart("yyyy",Date()) AS [Year],
[Material Disposition].Site,
(Right(Max([Material Disposition].[Control Number]),3)+1) AS Num,
DispoDesignator & Year & "." & Format(Num,"000") AS NewNum

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    22nd September 2018 - 09:31 PM