Avatar of f19l
 asked on

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

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?
Microsoft Access

Avatar of undefined
Last Comment
Gustav Brock

8/22/2022 - Mon
Gustav Brock

Use DateDiff:

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


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.



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?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Patrick Matthews


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.


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

Patrick Matthews

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.
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.

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 Coachman

<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.


Gustav Brock

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.

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


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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Gustav Brock

That could be something like this:

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