Reducing down a string

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi.
I am trying to reduce a string down, which is variable.
EG: 12232$23
or: 12332$1

Is there any way in a query(not SQL) that I can only show the figures after
the $ sign?

Cheers
AJ
 
AJCB said:
Hi.
I am trying to reduce a string down, which is variable.
EG: 12232$23
or: 12332$1

Is there any way in a query(not SQL) that I can only show the figures
after the $ sign?

Cheers
AJ
Click to expand...

Queries *are* SQL. The graphical query designer is just a way to make building
queries easier, but it is writing SQL for you in the background. Since we are
limited to text in these postings then the best way to get your answer is as a
SQL statement. You can paste that into the SQL view of a new query and then
switch to the graphical designer to see what it looks like there.

What you need is a compund expression that uses Mid() to extract text starting
at a certain position and InStr() to determine the position of the "$"
character.

SELECT Mid(YourField, InStr(1, YourField, "$") + 1)
AS NewFieldName
FROM YourTableName
 
I have entered the following string:

SELECT [Latest Record Query].[MaxOfPrimary ID], [Job Static Data].[Pallet
ID] AS [Ind Pallet ID], [Job Static Data].[Customer Code], [Job Static
Data].Reference, [Job Static Data].[Item Number], [Job Static Data].[Pallet
Quantity], [Job Variable Data].Location, [Job Variable Data].[User ID], [Job
Variable Data].[Date/Time], [Job Variable Data].Update, Left([Ind Pallet
ID],5) AS [Pallet No]
FROM [Job Static Data] INNER JOIN ([Latest Record Query] INNER JOIN [Job
Variable Data] ON [Latest Record Query].[MaxOfPrimary ID] = [Job Variable
Data].[Primary ID]) ON [Job Static Data].[Pallet ID] = [Job Variable
Data].[Pallet ID]
WHERE ((([Job Variable Data].Update)=2));
SELECT Mid([Job Static Data].[Pallet ID], InStr(1, [Job Static Data].[Pallet
ID], "$") + 1)
AS [Pallet Number]
FROM [Job Static Data]

From the 2nd 'SELECT' is where I have added the string that you gave me.

It comes up with an error that says 'Characters found after end of SQL
statement'.

I am not confident a SQL query, so apologies if this is simple stuff....
 
You have TWO SQL SELECT statements, not one. You want to be treating the
Mid() and Instr() result as a new "field" within the first SQL SELECT
statement.

You might find this much easier to do if you did it in the query design view
grid... and then, after it was working correctly, switched to SQL view to
see (?copy) how Access did it.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
 
Thanks Jeff.
I have managed to do this the way you said.
I think that if I concentrate on the design view, then look at it in SQL, I
might be able to understand it better.

Cheers again.

AJ
 
Back
Top