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

Use DateDiff:

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

/gustav

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.

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?

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

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;

Select allOpen in new window

Bad parentheses :)

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

Select allOpen in new window

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

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.

<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

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.

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