How to use Trim Function

[Valeriya]

Hi Guys,
Don't know how to use a trim function in the following case.
--Need to run daily an append query, which would check the table on a server (dbo_FCST_TISSUE_RECON.SERIAL_ID) against our local table (Counts)and find the items we've missed to count.

--Everything works perfectly, except for items that are differently formatted. We are using XXXXXX-XXX-XXX format in our databases; however our customers using just numbers without dashes.

Here the code, that I've been using before found this problem:

 [b]INSERT INTO[/b] Counts ( Cust, [Month/Yr], TID, [Update Date] )

[b]SELECT[/b] [0001 Bin Detail].Cust, IIf(IsNull([counts].[month/yr]),DateSerial(Year(Date()),Month(Date()),8)," ") AS [Month/Yr], dbo_FCST_TISSUE_RECON.SERIAL_ID AS TID, Date() AS [Update Date]

[b]FROM[/b] (dbo_FCST_TISSUE_RECON LEFT JOIN [0001 Bin Detail] ON dbo_FCST_TISSUE_RECON.SERIAL_ID = [0001 Bin Detail].TID) LEFT JOIN Counts ON dbo_FCST_TISSUE_RECON.SERIAL_ID = Counts.TID

[b]GROUP BY[/b] [0001 Bin Detail].Cust, IIf(IsNull([counts].[month/yr]),DateSerial(Year(Date()),Month(Date()),8)," "), dbo_FCST_TISSUE_RECON.SERIAL_ID, Date(), Counts.TID

[b]HAVING[/b] ((([0001 Bin Detail].Cust) Is Not Null) AND [COLOR=purple][b]((Counts.TID) Is Null)<---Here where the verification takes palce,append only items that are not in a Counts table[/b][/color]  AND ((Max(dbo_FCST_TISSUE_RECON.SCAN_DT))>DateSerial(Year(Date()),Month(Date()),0)));

Thank you in advance!

Valeriya


Thank You,

 

[Valeriya]

Hello,
Is anybody familiar with a trim function?
Continue to my previous message, I've added TRIM Function to WHERE clouse,and my append query, that I'm using just simply stop working:

[B][I]INSERT INTO[/I][/B] Counts ( Cust, [Month/Yr], TID, [Update Date] )

[B][I]SELECT[/I][/B] [0001 Bin Detail].Cust, IIf(IsNull([counts].[month/yr]),DateSerial(Year(Date()),Month(Date()),8)," ") AS [Month/Yr], dbo_FCST_TISSUE_RECON.SERIAL_ID AS TID, Date() AS [Update Date]

[B][I]FROM[/I][/B] (dbo_FCST_TISSUE_RECON LEFT JOIN [0001 Bin Detail] ON dbo_FCST_TISSUE_RECON.SERIAL_ID = [0001 Bin Detail].TID) LEFT JOIN Counts ON dbo_FCST_TISSUE_RECON.SERIAL_ID = Counts.TID

[COLOR=red yellow][b]WHERE ((Trim([Counts].[TID]))<>Trim([dbo_FCST_TISSUE_RECON].[SERIAL_ID])))[/b][/color]

[B][I]GROUP BY[/I][/B]  [0001 Bin Detail].Cust, IIf(IsNull([counts].[month/yr]),DateSerial(Year(Date()),Month(Date()),8)," "), dbo_FCST_TISSUE_RECON.SERIAL_ID, Date()

[B][I]HAVING[/I][/B] ((([0001 Bin Detail].Cust) Is Not Null) AND ((Max(dbo_FCST_TISSUE_RECON.SCAN_DT))>DateSerial(Year(Date()),Month(Date()),0)));

Please advise,

Thanks,

Valeriya

 

[lespaul]

not sure why you think trim is going to help you, all it does is remove blank spaces from around a string:

SomeField = ' Text '

Trim(SomeField) = Text

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[jjlogan]

Valeriya,

Doesn't the Trim Function just trim off spaces at beginning and end of a field or expression (argument of function)? Seems like you need to rebuild the TID in server table by eliminating the dashes.
Suggestion for consideration: Download the external data source data into local machine. Then run a VBA against the recordset and update a new field (e.g. TID_Modified). Then run your query using the data in the local machine.
Jeff

 

[Golom]

I assume by "simply stop working" you mean "not returning any records". That's as expected. Your FROM clause specifies

dbo_FCST_TISSUE_RECON.SERIAL_ID [COLOR=red]=[/color] Counts.TID

and the WHERE clause specifies

WHERE dbo_FCST_TISSUE_RECON.SERIAL_ID [COLOR=red]<>[/color] Counts.TID

or, in words

Pick the records that have
dbo_FCST_TISSUE_RECON.SERIAL_ID is equal to Counts.TID

and then

Select from that group only those records where
dbo_FCST_TISSUE_RECON.SERIAL_ID is NOT equal to Counts.TID

.... but of course, there aren't any because the two clauses directly contradict each other.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[Valeriya]

Hi Guys,
Unfortunately, I can't change the way it is now.
The company is in a process of conversion to another ERP Software System.
So, I desperately need to figure out to change my query to read those ID's without dashes against our local format XXXXXX-XXX-XXX.

1.

dbo_FCST_TISSUE_RECON.SERIAL_ID = Counts.TID

That is the only way I can link those two tables.
Of course, Golom, I've missed the logic here, thanks.
But cant' think where else I can put my criteria?

2.I've chosen TRIM because honestly that is the only one that I've used for formatting purpose.

3.Does anybody have any thoughts, what function would compare just numbers, without reading dashes or spaces?.

Thanks a lot!

Valeriya.

 

[jjlogan]

Again, it appears you need to use VBA coding and DAO or ADO (DAO and ADO produce recordsets). You can produce a recordset of data from external database. Then using the InStr Access function to recreate the ID number by eliminating the dashes between numbers. The InStr function locates the position within a string of where the dash is and then you can select just the characters you want using the Left, Mid, or Right text functions to create the ID in a new field. (If dashed number is uniform format, then don't need InStr). New Id can be then be in same format as your other matching data field. If you don't do VBA, then you would need to get some assistance within company or from a consultant.
Jeff

 

[Golom]

Have you considered the "Replace" function?

INSERT INTO Counts ( Cust, [Month/Yr], TID, [Update Date] )

SELECT [0001 Bin Detail].Cust, 
       IIf(IsNull([counts].[month/yr]),
           DateSerial(Year(Date()),Month(Date()),8)," ") AS [Month/Yr], 
       dbo_FCST_TISSUE_RECON.SERIAL_ID AS TID, 
       Date() AS [Update Date]

FROM (dbo_FCST_TISSUE_RECON LEFT JOIN [0001 Bin Detail] 
ON dbo_FCST_TISSUE_RECON.SERIAL_ID = [0001 Bin Detail].TID) 
LEFT JOIN Counts ON dbo_FCST_TISSUE_RECON.SERIAL_ID = 
                    [COLOR=red]Replace(Counts.TID,"-","")[/color]

WHERE [0001 Bin Detail].Cust Is Not Null

GROUP BY  [0001 Bin Detail].Cust, 
      IIf(IsNull([counts].[month/yr]),
          DateSerial(Year(Date()),Month(Date()),8),
          " "), 
      dbo_FCST_TISSUE_RECON.SERIAL_ID, 
      Date()

HAVING Max(dbo_FCST_TISSUE_RECON.SCAN_DT) > 
       DateSerial(Year(Date()),Month(Date()),0)

BTW: This

IIf(IsNull([counts].[month/yr]),
          DateSerial(Year(Date()),Month(Date()),8),
          " ")

Looks a bit odd. The resulting field will be a DateTime (for a TRUE Result) and a Text Field (for a FALSE one). You may want to use

IIf(IsNull([counts].[month/yr]),
          DateSerial(Year(Date()),Month(Date()),8),
          [COLOR=red]NULL[/color])

because DateTime fields can hold NULL values but not empty string values.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[lespaul]

how about something like:

Select mid(TheirField, 1, 6) & "-" & mid(TheirField, 7, 3) & "-" & mid(TheirField, 8, 3) From TableName

you can use that expression in joins or where clauses too. Depending on the version of Access:

Select Replace(YourField, "-", "") From TableName

The syntax may not be correct, I don't do much in Access itself.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[Valeriya]

That is a great idea Golom!
Because I'm not that good in VBA to write a recordset code, to be honest.

1.I've implemented the correction to the code you sent me, however the items with XXXXXX-XXX-XXX format are still not shown.

2. The join between dbo_FCST_TISSUE_RECON.SERIAL and Counts table is disappeared.
Basically, everytime I go back and recreate the join, the REPLACE function erased and vice versa.

3. Should I use the REPLACE function in a TID Field Name instead?

Thanks a million for your help!

Valeriya.