We help IT Professionals succeed at work.

How do I match two concatenated strings where each string may contain slightly different dates in an Access query?

f19l
f19l asked
on
308 Views
Last Modified: 2012-06-27
Hello, I would like to match two concatenated strings in an Access query where each one contains a date that may be slightly different by + or - 3 days, is this possible?
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Use DateDiff:

  Where Abs(DateDiff("d", [Field1], [Field2]))<=3

/gustav

Author

Commented:
I am not sure how I would actually go about using this? Right now I have a query linking two fields called CONCAT_ID from two different queries, which is where the problem lies as the dates in each field may be slightly different. I am not sure how to use the code above.

Author

Commented:
SELECT [Qry UNION_ID_CONCAT].*, Qry_COST_CENTRE.Cost_Centre

FROM [Qry UNION_ID_CONCAT] LEFT JOIN Qry_COST_CENTRE ON [Qry UNION_ID_CONCAT].CONCAT_ID = Qry_COST_CENTRE.CONCAT_ID

WHERE (((Qry_COST_CENTRE.Cost_Centre)<>""));

Above is the SQL code for the query. As you can see, the "FROM" section is where the join exists between the two concatenated fields.Would this be where I need to add your code?
CERTIFIED EXPERT
Top Expert 2010

Commented:
f19l,

It would be useful to see some sample data and/or a sample file.  EE now allows you to directly upload files to your question.

Please be advised that once you upload a file, it can be publicly accessed, and that it may not be possible to fully and permanently delete it.  The file may also be indexed by the major search engines.

Therefore, be very careful about posting proprietary, confidential, or other sensitive information.  If necessary, use "fake" and/or obfuscated data in your sample.

Please note that at present EE restricts uploads to certain file types.  If your file type does not match those in the list, you can use http://www.ee-stuff.com instead, which is not officially an EE site, but is run by people connected to EE.

Patrick

Author

Commented:
Ok, I have looked at my queries again and identified the points where I start creating the concatenated statements. In the first case, I have taken the SQL code that builds the concatenated statement and added your code above. However, when I try to run it I get a message stating syntax error in query expression and it relates to the code provided. Please see below to see if it makes sense, if  not possible then I wll see about trying to upload the database.
FROM [Qry GPLM] INNER JOIN [Qry Swaps TLS] ON ([Qry GPLM].ABSOLUTE_PRINCIPAL = [Qry Swaps TLS].Absolute_Notional) AND ([Qry GPLM].CURRENCY = [Qry Swaps TLS].CCY) AND ([Qry GPLM].MATURITY_DATE = [Qry Swaps TLS].Maturity_Date)
where abs((datediff("d",[Qry GPLM].MATURITY_DATE, [Qry Swaps TLS].Maturity_Date))<=3;

Open in new window

CERTIFIED EXPERT
Top Expert 2010

Commented:
Bad parentheses :)

where abs(datediff("d",[Qry GPLM].MATURITY_DATE, [Qry Swaps TLS].Maturity_Date))<=3

Open in new window


BTW, using spaces in the names of database objects is a very bad idea.

Author

Commented:
It does not seem to work as the same number of lines appear with/without the extra coding and I am not seeing the data I would expect too see.
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
<I am not seeing the data I would expect too see.>
Since you did not post what you expect to see, we are all guessing here.

How about posting a sample of the DB, so no guesswork is required?
Sound fair?

Sample database notes:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Compile the code. (From the VBA code window, click: Debug-->Compile)
10. Run the compact/Repair utility.
11. Remove any Passwords and/or security.
12. Post explicit steps to replicate the issue.
13. Test the database before posting.

In other words, ...post a database that we can easily open and immediately see the issue, and if applicable, also include a Graphical representation of the Exact results you are expecting based on the sample data.

Thanks

JeffCoachman
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
I have tried to upload the database but I seem to be having problems, I do not know why it is not working. In the meantime I would like to try another approach and see if this helps.

I have created concatenated statements from two queries that are composed as follows:

first part consists of a varying length of characters composed of both numbers and letters.
second part consists of an underscore sign
third part is made up of two numbers ranging from 01 to 31.

Please see the example below

USD15355083712/2011_28

In most cases all three parts should match meaning that joining the concatenated statements is straightforward and not a problem. In other cases however even though the first and second parts match the third parts will be slightly different, by no more that + or - 3 as seen below.

USD15355083712/2011_28 and USD15355083712/2011_27

Instead of me uploading the entire database you could take the two strings mentioned above, put them into two tables, create a query that tries to join them based on the criteria that even though the last two characters may be slightly different, the rest of the strings will match.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
That could be something like this:

AND
(ABS(Val(Right(Replace([Qry GPLM].MATURITY_DATE,"_",""),6))-Val(Right(Replace([Qry Swaps TLS].Maturity_Date,"_",""),6)))<=3);

/gustav
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.